To test how the Orders application works with database triggers, you add to the Orders table the
following database trigger that fires before the update of Customer_Id:
BEGIN
If :old.customer_id != : new.customer_id then
RAISE_APPLICATION_ERROR (-20101, ‘Database trigger says no!’);
end if;
END;
You run the Orders form, change the customer ID, and click Save. You receive the error message
“FRM-40509: Oracle error: unable to UPDATE record.” You select Help > Display Error, and the
Database Error dialog box that is shown in the Exhibit appears.
Which code would you put in your Form-level On-Error trigger to display the ORA- error message
instead of the FRM- error message?
A.
IF ERROR_CODE = 40509 THEN
MESSSAGE (DBMS_ERROR_TEXT);
END IF
B.
IF ERROR_CODE = 40509 THEN
MESSSAGE (SQLERRM);
END IF;
C.
IF ERROR_CODE = 06512 THEN
MESSSAGE(DBMS_ERROR_TEXT);
END IF;
D.
IF ERROR_CODE = 06512 THEN
MESSSAGE (SQLERRM);
END IF;
E.
IF ERROR-CODE = 20101 THEN
MESSSAGE (DBMS_ERROR_TEXT);
END IF;
F.
IF ERROR_CODE = 20101 THEN
MESSSAGE(SQLERRM);
END IF;
Explanation:
The error code raised by your code is 20101.
The SQLERRM function returns the error message associated with the most recently raised error
exception.
Note: The RAISE_APPLICATION_ERROR procedure lets you issue user-defined ORA-n error
messages from stored subprograms. That way, you can report errors to your application and avoid
returning unhandled exceptions.
To invoke RAISE_APPLICATION_ERROR, use the following syntax:
raise_application_error(
error_number, message[, {TRUE | FALSE}]);
where error_number is a negative integer in the range -20000..-20999 and message is a character
string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the
stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous
errors.
A is correct. ERROR_CODE constains FRM number error. SQLERRM is relevant for exception section. In case of F returns ORA-0000. DBMS_ERROR_TEXT works fine in ON-ERROR trigger.
A is Correct Answer