Oracle Exception Handing WHEN OTHERS SQLCODE SQLERRM PRAGMA INIT


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Exception Handling

Version 11.1

General
NOTE: How Oracle Does Implicit Rollbacks Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back. If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram. At the level of the SQLPlus prompt, every update/insert/delete has one implicit savepoint, and also the invocation of any unnamed block. Below that, the unnamed block itself has 'sub' savepoints - one for each insert/update/delete statement in it, and one for each subprogram unit. And so on down the line. If an error occurs, and that error is handled at any level by the time we're back at the SQLPlus prompt, we only rollback to the immediate savepoint at the start of the update/insert/delete that errors. Otherwise we rollback to the top-level 'virtual' savepoint currently in existence, which is my offending unnamed block. That is, a handled error is handled and so can be dealt with without rolling back all the way to the top. It is handled and the transaction proceeds. Commits define the end of a transaction (and start of a new one) - rollbacks only define the end of a transaction if they rollback to the last commit, rather than savepoint (whether explicit or implicit). I came to my 'version' from the following by no means exhaustive tests: CASE 1: I created a table a with one column, a1 number, and at the sqlplus prompt inserted a row with a1 = 1. I then ran that unnamed block I referred in an earlier post that, without an exception handler, does the following: INSERT INTO a VALUES (2); INSERT INTO a VALUES (3); INSERT INTO a VALUES ('a'); As expected I get an unhandled error on the last line. When I do a select for everything in the table a, I get the first row I inserted 'manually', the one with a1 = 1. So there seems to have been an invisible savepoint set just before the unnamed block ran. CASE 2: Then I modified the unnamed block so it did two good inserts and then called a stored procedure that did two good inserts and ended with one 'bad' - inserting a character into a number column. The stored procedure also had no error trap. When I run this one, as expected, error message. When I select everything from the table, it gets that single row with a1 = 1. Again, the unnamed block seems to set an invisible savepoint. And everything in the stored procedure got rolled back. CASE 3: Then I reran everything, except the unnamed block had a generic when others then null; error trap, and the stored procedure had a generic when others the null; error trap. In this case as expected, no error message was generated, and when I selected * from the table, it had inserted all the rows that were valid and only failed to insert the 'bad' rows. CASE 4: Then I deleted everything from the table a except the a1 = 1 and did a commit. Then I reran everything just as in case3, except that: the stored procedure had NO error trap but the unnamed block that calls it DOES. The result was exactly the same as in case3 - everything was stored except 'bad' rows. CASE 5: Then I deleted everything from the table 1 except the a1 = 1 and did a commit. Then I reran everything just as in case4, except that the stored procedure was the one with the error trap and unnamed block the one without an error trap. The results were that everything was stored in the table except the 'bad' lines. CASE 6: Finally ran case where my unnamed block did some ok inserts, I called a proc that did some more ok updates, then I called a proc that did some ok inserts and a bad insert; and there were no error traps in any proc or block. Everything got rolled back. Usenet source: Ken Quirici (c.d.o.server - 29-Oct-2004)

Basic Exception Handling
With Error Basic Block Structure Handling	CREATE OR REPLACE PROCEDURE <procedure_name> IS BEGIN NULL; EXCEPTION WHEN <named_exception> THEN -- handle identified exception WHEN <named_exception> THEN -- handle identified exception WHEN OTHERS THEN -- handle any exceptions not previously handled END; /
	CREATE OR REPLACE PROCEDURE myproc IS BEGIN NULL; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN ZERO_DIVIDE THEN NULL; WHEN OTHERS THEN NULL; END; /

WHEN OTHERS THEN with SQLCODE and SQLERRM
Note: If not the only exception handler ... must be the last exception handler
No Error Condition	DECLARE ecode NUMBER; emesg VARCHAR2(200); BEGIN NULL; ecode := SQLCODE; emesg := SQLERRM; dbms_output.put_line(TO_CHAR(ecode) \|\| '-' \|\| emesg); END; /
A Procedure That Does Nothing	CREATE OR REPLACE PROCEDURE no_error IS BEGIN NULL; END no_error; / exec no_error
Modified To Force An Error	CREATE OR REPLACE PROCEDURE force_error IS BEGIN NULL; RAISE too_many_rows; END force_error; / exec force_error
Trap And Hide The Error	CREATE OR REPLACE PROCEDURE trap_error IS BEGIN NULL; RAISE too_many_rows; EXCEPTION WHEN OTHERS THEN NULL; END trap_error; / exec trap_error
Display Error With SQLCODE	CREATE OR REPLACE PROCEDURE trap_errcode IS ecode NUMBER(38); thisproc CONSTANT VARCHAR2(50) := 'trap_errmesg'; BEGIN NULL; RAISE too_many_rows; EXCEPTION WHEN OTHERS THEN ecode := SQLCODE; dbms_output.put_line(thisproc \|\| ' - ' \|\| ecode); END trap_errcode; / set serveroutput on exec trap_errcode
Display Error With SQLERRM	CREATE OR REPLACE PROCEDURE trap_errmesg IS emesg VARCHAR2(250); BEGIN NULL; RAISE too_many_rows; EXCEPTION WHEN OTHERS THEN emesg := SQLERRM; dbms_output.put_line(emesg); END trap_errmesg; / set serveroutput on exec trap_errmesg

WHEN <name exception> THEN & Named Exceptions
Note: A table of the named exceptions is at the bottom of this web page.
When Invalid Cursor Exception Demo	CREATE OR REPLACE PROCEDURE invcur_exception IS CURSOR x_cur is SELECT * FROM all_tables; x_rec x_cur%rowtype; BEGIN LOOP -- note the cursor was not opened before the FETCH FETCH x_cur INTO x_rec; EXIT WHEN x_cur%notfound; NULL; END LOOP; EXCEPTION WHEN INVALID_CURSOR THEN dbms_output.put_line('Whoops!'); WHEN OTHERS THEN dbms_output.put_line('Some Other Problem'); END invcur_exception; / set serveroutput on exec invcur_exception
Two Many Rows Exception Demo	CREATE OR REPLACE PROCEDURE tmr_exception IS x all_tables.table_name%TYPE; BEGIN -- note the statement will try to fetch many values SELECT table_name -- try to SELECT many things into 1 var INTO x FROM all_tables; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line('Too Many Rows'); WHEN OTHERS THEN dbms_output.put_line('Some Other Problem'); END tmr_exception; / set serveroutput on exec tmr_exception
Division By Zero Error Trapping Demo	CREATE OR REPLACE PROCEDURE dbz_exception (numin NUMBER) IS z NUMBER := 0; x NUMBER; BEGIN x := numin / z; EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('Division By Zero'); WHEN OTHERS THEN dbms_output.put_line('Some Other Problem'); END dbz_exception; / set serveroutput on exec dbz_exception(6)
Divide By Zero Error Pass In The Zero	CREATE OR REPLACE PROCEDURE zero_div (numin NUMBER) IS z NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')); x NUMBER; BEGIN x := z / numin; dbms_output.put_line('Division By ' \|\| TO_CHAR(numin)); EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('Division By Zero'); WHEN OTHERS THEN dbms_output.put_line('Some Other Problem'); END zero_div; / set serveroutput on exec zero_div(2) exec zero_div(0) exec zero_div(7)

User Defined Exceptions
Named Exception In a Function Demo	CREATE OR REPLACE FUNCTION is_ssn (string_in VARCHAR2) RETURN VARCHAR2 IS -- validating ###-##-#### format delim VARCHAR2(1); part1 NUMBER(3,0); part2 NUMBER(2,0); part3 NUMBER(4,0); too_long EXCEPTION; too_short EXCEPTION; delimiter EXCEPTION; BEGIN IF LENGTH(string_in) > 11 THEN RAISE too_long; ELSIF LENGTH(string_in) < 11 THEN RAISE too_short; END IF; part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999'); delim := SUBSTR(string_in,4,1); IF delim <> '-' THEN RAISE delimiter; END IF; part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99'); delim := SUBSTR(string_in,7,1); IF delim <> '-' THEN RAISE delimiter; END IF; part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999'); RETURN 'TRUE'; EXCEPTION WHEN too_long THEN dbms_output.put_line('More Than 11 Characters'); RETURN 'FALSE'; WHEN too_short THEN dbms_output.put_line('Less Than 11 Characters'); RETURN 'FALSE'; WHEN delimiter THEN dbms_output.put_line('Incorrect Delimiter'); RETURN 'FALSE'; WHEN OTHERS THEN dbms_output.put_line('Some Other Issue'); RETURN 'FALSE'; END is_ssn; / set serveroutput on SELECT is_ssn('123-45-6789') FROM DUAL; SELECT is_ssn('123-45-67890') FROM DUAL; SELECT is_ssn('123-45-678') FROM DUAL; SELECT is_ssn('123-45=67890') FROM DUAL;

Pragma Exception_Init
PRAGMA EXCEPTION_INIT Demo	CREATE TABLE results ( sourceno NUMBER(10) NOT NULL, testno NUMBER(3) NOT NULL, locationid NUMBER(10) NOT NULL); -- the basic procedure CREATE OR REPLACE PROCEDURE PragmaExcInit IS BEGIN INSERT INTO results (sourceno) VALUES ('1'); COMMIT; END PragmaExcInit; / exec pragmaexcinit -- the same procedure with exception trapping CREATE OR REPLACE PROCEDURE PragmaExcInit IS FieldsLeftNull EXCEPTION; PRAGMA EXCEPTION_INIT(FieldsLeftNull, -01400); BEGIN INSERT INTO results (sourceno) VALUES ('1'); COMMIT; EXCEPTION WHEN FieldsLeftNull THEN dbms_output.put_line('ERROR: Trapped Fields Left Null'); WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END PragmaExcInit; / set serveroutput on exec pragmaexcinit

RAISE
Demo Procedure With User Defined Exceptions And RAISE	CREATE OR REPLACE PROCEDURE raise_demo (inval NUMBER) IS evenno EXCEPTION; oddno EXCEPTION; BEGIN IF MOD(inval, 2) = 1 THEN RAISE oddno; ELSE RAISE evenno; END IF; EXCEPTION WHEN evenno THEN dbms_output.put_line(TO_CHAR(inval) \|\| ' is even'); WHEN oddno THEN dbms_output.put_line(TO_CHAR(inval) \|\| ' is odd'); END raise_demo; / set serveroutput on exec raise_demo

RAISE_APPLICATION_ERROR
Returning a User Defined Exception to the application	RAISE_APPLICATION_ERROR(<error_number>, <error_message>, <TRUE \| FALSE>); error_number -20000 to -20999 error_message VARCHAR2(2048) TRUE add to error stack FALSE replace error stack (the default)
	CREATE OR REPLACE PROCEDURE raise_app_error (inval NUMBER) IS evenno EXCEPTION; oddno EXCEPTION; BEGIN IF MOD(inval, 2) = 1 THEN RAISE oddno; ELSE RAISE evenno; END IF; EXCEPTION WHEN evenno THEN RAISE_APPLICATION_ERROR(-20001, 'Even Number Entered'); WHEN oddno THEN RAISE_APPLICATION_ERROR(-20999, 'Odd Number Entered'); END raise_app_error; / exec raise_app_error;

Locator Variables
Locating Errors With Locator Variables	The use of variables to identify the location with a code block where the error was raised
	set serveroutput on DECLARE step VARCHAR2(2); i NUMBER(1) := 5; n NUMBER(2) := 10; BEGIN step := 'A'; n := n/i; i := i-1; step := 'B'; n := n/i; i := i-2; step := 'C'; n := n/i; i := i-2; step := 'D'; n := n/i; i := i-2; step := 'E'; n := n/i; i := i-1; EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('Failure at: ' \|\| step); END; /

Declaration Exceptions
Declaration Exceptions	Declaration exceptions can not be trapped with an error handler
	DECLARE i NUMBER(3) := 1000; BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END; / CREATE OR REPLACE PROCEDURE demo(someval IN NUMBER) IS i NUMBER(3) := someval; BEGIN i := i+0; EXCEPTION WHEN OTHERS THEN NULL; END; / exec demo(999); exec demo(1000);

Exception Handling Demo
Incomplete Handling	CREATE TABLE test ( col INT); ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (col) USING INDEX; CREATE OR REPLACE PROCEDURE p IS BEGIN INSERT INTO test VALUES (1); END p; / BEGIN p; p; END; / -- no records inserted as expected SELECT * FROM test; BEGIN p; p; EXCEPTION WHEN OTHERS THEN NULL; END; / -- one record inserted SELECT * FROM test;

System-Defined Event Trapping
Error Stack Trapping with System Events	Declaration exceptions can not be trapped with an error handler
	set serveroutput on CREATE OR REPLACE TRIGGER e_trigger BEFORE delete ON t DECLARE l_text ora_name_list_t; l_n number; BEGIN dbms_output.put_line( '--------------------' ); dbms_output.put_line('statment causing error: ' ); l_n := ora_sql_txt( l_text ); FOR i IN 1 .. nvl(l_text.count,0) LOOP dbms_output.put_line(l_text(i) ); END LOOP; dbms_output.put_line( 'error text: ' ); FOR i IN 1 .. ora_server_error_depth LOOP dbms_output.put_line(ora_server_error_msg(i) ); END LOOP; dbms_output.put_line( '--------------------' ); END e_trigger; /

Simple Error Handling Procedure
Function To Identify The User Logged Onto Oracle	CREATE OR REPLACE FUNCTION getosuser RETURN VARCHAR2 IS vOSUser user_users.username%TYPE; BEGIN SELECT osuser INTO vOSUser FROM sys.v_$session WHERE sid = ( SELECT sid FROM sys.v_$mystat WHERE rownum = 1); RETURN vOSUser; EXCEPTION WHEN OTHERS THEN RETURN 'UNK'; END getosuser; / SELECT getosuser FROM DUAL;
The Table Holding The Output Of The Error Logging Procedure	CREATE TABLE errorlog ( procname VARCHAR2(61), loadfilename VARCHAR2(40), runtime DATE DEFAULT SYSDATE, osuser VARCHAR2(30), mesgtext VARCHAR2(250));
The Error Logging Procedure	CREATE OR REPLACE PROCEDURE log_error ( pProcName VARCHAR2, pLoadFile VARCHAR2, pMesgText VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO errorlog (procname, loadfilename, osuser, mesgtext) VALUES (pProcName, pLoadFile, getOSUser, pMesgText); COMMIT; -- No exception handler intentionally. Why? END log_error; /
To Test The Error Logging Procedure	exec log_error('Test', 'None', 'Did it work?'); SELECT * FROM errorlog;

Database-Wide Exception Handling
Using AFTER SERVERERROR	CREATE TABLE error_log ( error_timestamp TIMESTAMP(9), database_name VARCHAR(50), instance_number NUMBER, error_number NUMBER, error_message VARCHAR2(255), logged_on_as VARCHAR2(30), client_host VARCHAR2(50), service_name VARCHAR2(30)); CREATE OR REPLACE PROCEDURE error_trap IS odbname VARCHAR2(50); -- Oracle database name oinst NUMBER; -- Oracle instance number enum NUMBER; -- Error Message number emsg VARCHAR2(250); -- Error text curschema VARCHAR2(30); clihost VARCHAR2(50); serv_name VARCHAR2(30); -- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN enum := sqlcode; emsg := sqlerrm; odbname := ora_database_name; oinst := ora_instance_num; SELECT sys_context('USERENV', 'CURRENT_SCHEMA') INTO curschema FROM DUAL; SELECT sys_context('USERENV', 'HOST') INTO clihost FROM DUAL; SELECT sys_context('USERENV', 'SERVICE_NAME') INTO serv_name FROM DUAL; INSERT INTO error_log (error_timestamp, database_name, instance_number, error_number, error_message, logged_on_as, client_host, service_name) VALUES (SYSTIMESTAMP, odbname, oinst, enum, emsg, curschema, clihost, serv_name); COMMIT; END error_trap; / CREATE OR REPLACE TRIGGER error_trig AFTER SERVERERROR ON DATABASE CALL error_trap / BEGIN RAISE zero_divide; END; / set linesize 141 col error_timestamp format a31 col database_name format a40 col error_message format a40 col logged_on_as format a20 col client_host format a20 col service_name format a20 SELECT error_timestamp, database_name, instance_number FROM error_log; SELECT error_timestamp, error_number, error_message FROM error_log; SELECT logged_on_as, client_host, service_name FROM error_log;

Robust Error Handling Procedure
Formatting Error Stack Tables And Sequence	CREATE TABLE errors ( module VARCHAR2(50), seq_number NUMBER, error_number NUMBER, error_mesg VARCHAR2(100), error_stack VARCHAR2(2000), call_stack VARCHAR2(2000), timestamp DATE); ALTER TABLE errors ADD CONSTRAINT pk_errors PRIMARY KEY (module, seq_number) USING INDEX TABLESPACE indx_sml; CREATE TABLE call_stacks ( module VARCHAR2(50), seq_number NUMBER, call_order NUMBER, object_handle VARCHAR2(10), line_num NUMBER, object_name VARCHAR2(80)); ALTER TABLE call_stacks ADD CONSTRAINT pk_call_stacks PRIMARY KEY (module, seq_number, call_order) USING INDEX TABLESPACE indx_sml; ALTER TABLE call_stacks ADD CONSTRAINT fk_cs_errors FOREIGN KEY (module, seq_number) REFERENCES errors (module, seq_number) ON DELETE CASCADE; CREATE TABLE error_stacks ( module VARCHAR2(50), seq_number NUMBER, error_order NUMBER, facility CHAR(3), error_number NUMBER(5), error_mesg VARCHAR2(100)); ALTER TABLE error_stacks ADD CONSTRAINT pk_error_stacks PRIMARY KEY (module, seq_number, error_order) USING INDEX TABLESPACE indx_sml; ALTER TABLE error_stacks ADD CONSTRAINT fk_es_errors FOREIGN KEY (module, seq_number) REFERENCES errors (module, seq_number) ON DELETE CASCADE; CREATE SEQUENCE error_seq START WITH 1 INCREMENT BY 1;
Error Handling Package Header	CREATE OR REPLACE PACKAGE ErrorPkg AS /* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_CALL_STACK. This package stores general error information in the errors table, with detailed call stack and error stack information in the call_stacks and error_stacks tables, respectively. Entry point for handling errors. HandleAll should be called from all exception handlers where you want the error to be logged. p_Top should be TRUE only at the topmost level of procedure nesting. It should be FALSE at other levels. / PROCEDURE HandleAll(p_Top BOOLEAN); / Prints the error and call stacks (using DBMS_OUTPUT) for the given module and sequence number. / PROCEDURE PrintStacks(p_Module IN errors.module%TYPE, p_SeqNum IN errors.seq_number%TYPE); / Unwinds the call and error stacks, and stores them in the errors and call stacks tables. Returns the sequence number under which the error is stored. If p_CommitFlag is TRUE, then the inserts are committed. In order to use StoreStacks, an error must have been handled. Thus HandleAll should have been called with p_Top = TRUE. */ PROCEDURE StoreStacks(p_Module IN errors.module%TYPE, p_SeqNum OUT errors.seq_number%TYPE, p_CommitFlag BOOLEAN DEFAULT FALSE); END ErrorPkg; /
Error Handling Package Body	CREATE OR REPLACE PACKAGE BODY ErrorPkg IS v_NewLine CONSTANT CHAR(1) := CHR(10); v_Handled BOOLEAN := FALSE; v_ErrorStack VARCHAR2(2000); v_CallStack VARCHAR2(2000); PROCEDURE HandleAll(p_Top BOOLEAN) IS BEGIN IF p_Top THEN v_Handled := FALSE; ELSIF NOT v_Handled THEN v_Handled := TRUE; v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK; v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK; END IF; END HandleAll; --=================================================== PROCEDURE PrintStacks( p_Module IN errors.module%TYPE, p_SeqNum IN errors.seq_number%TYPE) IS v_TimeStamp errors.timestamp%TYPE; v_ErrorMsg errors.error_mesg%TYPE; CURSOR c_CallCur IS SELECT object_handle, line_num, object_name FROM call_stacks WHERE module = p_Module AND seq_number = p_SeqNum ORDER BY call_order; CURSOR c_ErrorCur IS SELECT facility, error_number, error_mesg FROM error_stacks WHERE module = p_Module AND seq_number = p_SeqNum ORDER BY error_order; BEGIN SELECT timestamp, error_mesg INTO v_TimeStamp, v_ErrorMsg FROM errors WHERE module = p_Module AND seq_number = p_SeqNum; -- Output general error information. dbms_output.put_line(TO_CHAR(v_TimeStamp, 'DD-MON-YY HH24:MI:SS')); dbms_output.put(' Module: ' \|\| p_Module); dbms_output.put(' Error #' \|\| p_SeqNum \|\| ': '); dbms_output.put_line(v_ErrorMsg); -- Output the call stack. dbms_output.put('Complete Call Stack:'); dbms_output.put(' Object Handle Line Number Object Name'); dbms_output.put_line(' ------------- ----------- -----------'); FOR v_CallRec in c_CallCur LOOP dbms_output.put(RPAD(' ' \|\| v_CallRec.object_handle, 15)); dbms_output.put(RPAD(' ' \|\| TO_CHAR(v_CallRec.line_num), 13)); dbms_output.put_line(' ' \|\| v_CallRec.object_name); END LOOP; -- Output the error stack. dbms_output.put_line('Complete Error Stack:'); FOR v_ErrorRec in c_ErrorCur LOOP dbms_output.put(' ' \|\| v_ErrorRec.facility \|\| '-'); dbms_output.put(TO_CHAR(v_ErrorRec.error_number) \|\| ': '); dbms_output.put_line(v_ErrorRec.error_mesg); END LOOP; END PrintStacks; --=================================================== PROCEDURE StoreStacks(p_Module IN errors.module%TYPE, p_SeqNum OUT errors.seq_number%TYPE, p_CommitFlag BOOLEAN DEFAULT FALSE) IS v_SeqNum NUMBER; v_Index NUMBER; v_Length NUMBER; v_End NUMBER; v_Call VARCHAR2(100); v_CallOrder NUMBER := 1; v_Error VARCHAR2(120); v_ErrorOrder NUMBER := 1; v_Handle call_stacks.object_handle%TYPE; v_LineNum call_stacks.line_num%TYPE; v_ObjectName call_stacks.object_name%TYPE; v_Facility error_stacks.facility%TYPE; v_ErrNum error_stacks.error_number%TYPE; v_ErrMsg error_stacks.error_mesg%TYPE; v_FirstErrNum errors.error_number%TYPE; v_FirstErrMsg errors.error_mesg%TYPE; BEGIN -- Get the error sequence number. SELECT error_seq.nextval INTO v_SeqNum FROM DUAL; p_SeqNum := v_SeqNum; -- Insert first part of header info. into the errors table INSERT INTO errors (module, seq_number, error_stack, call_stack, timestamp) VALUES (p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE); /* Unwind the error stack to get each error out by scanning the error stack string. Start with the index at the beginning of the string ; v_Index := 1; / Loop through the string, finding each newline A newline ends each error on the stack / WHILE v_Index < LENGTH(v_ErrorStack) LOOP -- v_End is the position of the newline. v_End := INSTR(v_ErrorStack, v_NewLine, v_Index); -- The error is between the current index and the newline v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index); -- Skip over the current error, for the next iteration v_Index := v_Index + LENGTH(v_Error) + 1; / An error looks like 'facility-number: mesg'. Get each piece out for insertion. The facility is the first 3 characters of the error. / v_Facility := SUBSTR(v_Error, 1, 3); -- Remove the facility and the dash (always 4 characters) v_Error := SUBSTR(v_Error, 5); -- Next get the error number v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error, ':') - 1)); -- Remove the error number, colon & space (always 7 chars) v_Error := SUBSTR(v_Error, 8); -- What's left is the error message v_ErrMsg := v_Error; / Insert the errors, and grab the first error number and message while we're at it / INSERT INTO error_stacks (module, seq_number, error_order, facility, error_number, error_mesg) VALUES (p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg); IF v_ErrorOrder = 1 THEN v_FirstErrNum := v_ErrNum; v_FirstErrMsg := v_Facility \|\| '-' \|\| TO_NUMBER(v_ErrNum) \|\| ': ' \|\| v_ErrMsg; END IF; v_ErrorOrder := v_ErrorOrder + 1; END LOOP; -- Update the errors table with the message and code UPDATE errors SET error_number = v_FirstErrNum, error_mesg = v_FirstErrMsg WHERE module = p_Module AND seq_number = v_SeqNum; / Unwind the call stack to get each call out by scanning the call stack string. Start with the index after the first call on the stack. This will be after the first occurrence of 'name' and the newline. / v_Index := INSTR(v_CallStack, 'name') + 5; / Loop through the string, finding each newline. A newline ends each call on the stack. / WHILE v_Index < LENGTH(v_CallStack) LOOP -- v_End is the position of the newline v_End := INSTR(v_CallStack, v_NewLine, v_Index); -- The call is between the current index and the newline v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index); -- Skip over the current call, for the next iteration v_Index := v_Index + LENGTH(v_Call) + 1; / Within a call, we have the object handle, then the line number, then the object name, separated by spaces. Separate them out for insertion. -- Trim white space from the call first. */ v_Call := TRIM(v_Call); -- First get the object handle v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' ')); -- Remove the object handle,then the white space v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1); v_Call := TRIM(v_Call); -- Get the line number v_LineNum := TO_NUMBER(SUBSTR(v_Call,1,INSTR(v_Call,' '))); -- Remove the line number, and white space v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1); v_Call := TRIM(v_Call); -- What is left is the object name v_ObjectName := v_Call; -- Insert all calls except the call for ErrorPkg IF v_CallOrder > 1 THEN INSERT INTO call_stacks (module, seq_number, call_order, object_handle, line_num, object_name) VALUES (p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum, v_ObjectName); END IF; v_Callorder := v_CallOrder + 1; END LOOP; IF p_CommitFlag THEN COMMIT; END IF; END StoreStacks; END ErrorPkg; /
Format Error Stack Demo Table And Trigger	CREATE TABLE ttt (f1 number); CREATE OR REPLACE TRIGGER ttt_insert BEFORE INSERT ON ttt BEGIN RAISE ZERO_DIVIDE; END ttt_insert; /
Error Producing Procedures (A, B, And C)	CREATE OR REPLACE PROCEDURE C AS BEGIN INSERT INTO ttt VALUES (7); EXCEPTION WHEN OTHERS THEN ErrorPkg.HandleAll(FALSE); RAISE; END C; / --=================================================== CREATE OR REPLACE PROCEDURE B AS BEGIN C; EXCEPTION WHEN OTHERS THEN ErrorPkg.HandleAll(FALSE); RAISE; END B; / --=================================================== CREATE OR REPLACE PROCEDURE A AS v_ErrorSeq NUMBER; BEGIN B; EXCEPTION WHEN OTHERS THEN ErrorPkg.HandleAll(TRUE); ErrorPkg.StoreStacks('Scott', v_ErrorSeq, TRUE); ErrorPkg.PrintStacks('Scott', v_ErrorSeq); END A; /
Run Format Error Stack Demo	exec a; -- Examine the tables errors, call_stack, and error_stack

Predefined (Named) PL/SQL Exceptions
For a full list of all 18,000 PL/SQL Errors, visit the Oracle Error Code Library
Exception Name	Error	Description
ACCESS_INTO_NULL	ORA-06530	Attempted to assign values to the attributes of an uninitialized (NULL) object.
CASE_NOT_FOUND	ORA-06592	None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause.
COLLECTION_IS_NULL	ORA-06531	Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table or VARRAY.
CURSOR_ALREADY_OPEN	ORA-06511	Exactly what it seems to be. Tried to open a cursor that was already open
DUP_VAL_ON_INDEX	ORA-00001	An attempt to insert or update a record in violation of a primary key or unique constraint
INVALID_CURSOR	ORA-01001	The cursor is not open, or not valid in the context in which it is being called.
INVALID_NUMBER	ORA-01722	It isn't a number, even though you are treating it like one to trying to turn it into one.
LOGIN_DENIED	ORA-01017	Invalid name and/or password for the instance.
NO_DATA_FOUND	ORA-01403	The SELECT statement returned no rows or referenced a deleted element in a nested table or referenced an initialized element in an Index-By table.
NOT_LOGGED_ON	ORA-01012	Database connection lost.
PROGRAM_ERROR	ORA-06501	Internal PL/SQL error.
ROWTYPE_MISMATCH	ORA-06504	The rowtype does not match the values being fetched or assigned to it.
SELF_IS_fs	ORA-30625	Program attempted to call a MEMBER method, but the instance of the object type has not been intialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
STORAGE_ERROR	ORA-06500	A hardware problem: Either RAM or disk drive.
SUBSCRIPT_BEYOND_COUNT	ORA-06533	Reference to a nested table or varray index higher than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	Reference to a nested table or varray index outside the declared range (such as -1).
SYS_INVALID_ROWID	ORA-01410	The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	ORA-00051	The activity took too long and timed out.
TOO_MANY_ROWS	ORA-01422	The SQL INTO statement brought back more than one value or row (only one is allowed).
USERENV_COMMITSCN_ERROR	ORA-01725	Added for USERENV enhancement, bug 1622213.
VALUE_ERROR	ORA-06502	An arithmetic, conversion, truncation, or size-constraint error. Usually raised by trying to cram a 6 character string into a VARCHAR2(5) variable
ZERO_DIVIDE	ORA-01476	Not only would your math teacher not let you do it, computers won't either. Who said you didn't learn anything useful in primary school?

Related Topics

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us