FRM-40735错误求助：When-Mouse-Click触发器触发未处理ORA-06502异常

Got it, let's figure out why you're hitting this ORA-06502 error alongside FRM-40735, and how to fix it step by step:

Root Cause Breakdown #

The ORA-06502 (VALUE_ERROR) is triggering because of a type conversion failure or value size mismatch, and the FRM-40735 pops up because the exception isn't being handled properly in the Oracle Forms trigger context. Here's the specific issue in your code:

• You’re using substitution variables (&book_no, &no_of_books) which aren’t the right way to capture user input in Oracle Forms. These variables resolve to strings, and when assigning &no_of_books to d number(4), if the input is non-numeric or exceeds 4 digits, the conversion fails immediately during variable declaration—before your exception block even runs. That’s why your catch doesn’t work, and Forms throws the unhandled exception error.

Fix 1: Use Form Items Correctly #

In Oracle Forms, you should reference form-level input items directly using the colon (:) syntax instead of substitution variables. Let’s assume you have text items on your form named BOOK_NO, BOOK_NAME, PRICE, and NO_OF_BOOKS where users enter data. Rewrite your code like this:

declare
  a varchar2(20) := :BOOK_NO;
  b varchar2(20) := :BOOK_NAME;
  c varchar2(20) := :PRICE;
  d number(4); -- Don't assign immediately; handle conversion in the block
begin
  -- Convert and validate the numeric input explicitly
  d := to_number(:NO_OF_BOOKS);
  
  insert into book values (a, b, c, d);
  
  -- Optional: Add a success message if needed
  message('Book added successfully!');
  message(''); -- Clear message stack
  
EXCEPTION
  when VALUE_ERROR then
    dbms_output.put_line('VALUE_ERROR: Invalid number or value too large for book count');
    -- Show user-friendly message in Forms
    message('Error: Number of books must be a valid number (max 4 digits)');
    raise form_trigger_failure; -- Tells Forms to stop processing the trigger
  when others then
    dbms_output.put_line('Unexpected error: ' || sqlerrm);
    message('An unexpected error occurred. Please try again.');
    raise form_trigger_failure;
end;

Fix 2: Add Pre-Validation to Avoid Errors Entirely #

To prevent the error from happening in the first place, add validation checks before converting the input to a number. This gives you more control over user feedback:

declare
  a varchar2(20) := :BOOK_NO;
  b varchar2(20) := :BOOK_NAME;
  c varchar2(20) := :PRICE;
  d number(4);
begin
  -- Check if the input is a valid number first
  if not is_number(:NO_OF_BOOKS) then
    message('Error: Please enter a numeric value for "Number of Books"');
    raise form_trigger_failure;
  end if;
  
  -- Check if the number is within the 4-digit limit
  if to_number(:NO_OF_BOOKS) > 9999 then
    message('Error: Number of books cannot exceed 9999 (max 4 digits)');
    raise form_trigger_failure;
  end if;
  
  d := to_number(:NO_OF_BOOKS);
  insert into book values (a, b, c, d);
  
  message('Book added successfully!');
  message('');
EXCEPTION
  when others then
    dbms_output.put_line('Error: ' || sqlerrm);
    message('Failed to add book. Please check your input.');
    raise form_trigger_failure;
end;

Note: is_number() is a built-in Oracle Forms function to check if a string is a valid number. If you’re using an older Forms version, you can write a simple helper function to perform this check.

Why This Fixes FRM-40735 #

By moving the conversion and validation into the BEGIN block (instead of declaring variables with substitution variables), you ensure that any errors are caught by your EXCEPTION handler. Adding raise form_trigger_failure; tells Oracle Forms to halt the trigger execution and display your custom message instead of the generic unhandled exception error.

内容的提问来源于stack exchange，提问作者Chitra Thever