SERVERERROR examples

Let's look at some examples of using the SERVERERROR functions. We'll start with a very simple example of a SERVERERROR trigger that echoes the fact that an error occurred.

CREATE OR REPLACE TRIGGER error_echoAFTER SERVERERRORON SCHEMABEGIN DBMS_OUTPUT.PUT_LINE ('You experienced an error');END;

Whenever an Oracle error occurs (assuming that SERVEROUTPUT is ON), the coded message above will display:

SQL> SET SERVEROUTPUT ONSQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));You experienced an errorBEGIN DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A')); END; *ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character to number conversion errorORA-06512: at line 1

Note that the Oracle error message was delivered after the trigger message. This allows the Oracle error to be accessed and logged prior to the actual failure, as shown in the next example.

SERVERERROR triggers are automatically isolated in their own autonomous transaction (autonomous transactions were covered in Chapter 13). This means that you can, for example, write error information out to a log table and save those changes with a COMMIT, while not affecting the session transaction in which the error occurred.

The error_echo trigger guarantees that information about all but a handful of errors listed earlier will be automatically logged regardless of the application, user, or program in which the error was raised.

/* File on web: error_log.sql */CREATE OR REPLACE TRIGGER error_echoAFTER SERVERERRORON SCHEMADECLARE v_errnum NUMBER; -- the Oracle error # v_now DATE := SYSDATE; -- current time v_counter NUMBER := 1; -- stack counterBEGIN -- for every error in the error stack... LOOP -- get the error number off the stack at this position v_errnum := ORA_SERVER_ERROR (v_counter); -- if the error # is zero then we are done EXIT WHEN v_errnum = 0; -- Write the error out to the log table. INSERT INTO error_log(username, error_number, sequence, timestamp) VALUES(USER, v_errnum, v_counter, v_now); -- increment the counter and try again v_counter := v_counter + 1; END LOOP; -- every error on the stackEND;

Remember that all these new rows in the error_log have been committed by the time the END statement is reached, because the trigger is executed within an autonomous transaction. The following lines demonstrate this trigger in action:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character to number conversion error SQL> SELECT * FROM error_log; USERNAME ERROR_NUMBER SEQUENCE TIMESTAMP------------------------------ ------------ ---------- ---------BOOK 6502 1 04-JAN-02BOOK 6512 2 04-JAN-02

Why do two errors appear in the table when only one error was raised? The actual error stack generated by Oracle contains both ORA-06502 and ORA-06512, so they are both logged and denoted by their sequence of occurrence.

If you want to quickly determine if a certain error number is located in the stack without parsing it manually, use the companion function,ORA_IS_SERVERERROR. This function is very useful for monitoring specific errors that may require extra handling, such as user-defined exceptions. This is the kind of code you might write:

-- Special handling of user defined errors-- 20000 through 20010 raised by calls to-- RAISE_APPLICATION_ERROR FOR errnum IN 20000 .. 20010 LOOP IF ORA_IS_SERVERERROR (errnum) THEN log_user_defined_error (errnum); END IF;END LOOP;

All Oracle error numbers are negative, except for 1 (user-defined exception) and 100 (synonymous with -1403, NO_DATA_FOUND). When you specify an error number in the call to ORA_IS_SERVERERROR, however, you must supply a positive number, as shown in the above example.