Oracle Exception Handling Version 11.1

illpvzltdp

NOTE:How Oracle Does Implicit Rollbacks Before executing anINSERT,UPDATE, orDELETEstatement, Oracle marks an implicit savepoint (unavailableto 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 rolledback. 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 SQL*Plus 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 foreach insert/update/delete statement in it, and onefor 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 SQL*Plus 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' savepointcurrently 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: INSERTINTOaVALUES(2); INSERTINTOaVALUES(3); INSERTINTOaVALUES('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 CREATEORREPLACEPROCEDURE<procedure_name>IS BEGIN NULL; EXCEPTION WHEN<named_exception>THEN -- handle identified exception WHEN<named_exception>THEN -- handle identified exception WHENOTHERSTHEN -- handle any exceptions not previously handled END; / CREATEORREPLACEPROCEDUREmyprocIS BEGIN NULL; EXCEPTION WHENNO_DATA_FOUNDTHEN NULL; WHENZERO_DIVIDETHEN NULL; WHENOTHERSTHEN NULL; END; / WHENOTHERSTHENwithSQLCODEandSQLERRM Note: If not the only exception handler ... must be the last exception handler No Error Condition DECLARE ecodeNUMBER; emesgVARCHAR2(200); BEGIN NULL; ecode := SQLCODE; emesg := SQLERRM; dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg); END; / A Procedure That Does Nothing CREATEORREPLACEPROCEDUREno_errorIS BEGIN NULL; ENDno_error; / exec no_error Modified To Force An Error CREATEORREPLACEPROCEDUREforce_errorIS BEGIN NULL; RAISEtoo_many_rows; ENDforce_error; / exec force_error Trap And Hide The Error CREATEORREPLACEPROCEDUREtrap_errorIS BEGIN NULL; RAISEtoo_many_rows; EXCEPTION WHENOTHERSTHEN NULL; ENDtrap_error; / exec trap_error Display Error With SQLCODE CREATEORREPLACEPROCEDUREtrap_errcodeIS ecodeNUMBER(38); thisprocCONSTANTVARCHAR2(50) := 'trap_errmesg'; BEGIN NULL; RAISEtoo_many_rows; EXCEPTION WHENOTHERSTHEN ecode := SQLCODE; dbms_output.put_line(thisproc || ' - ' || ecode); ENDtrap_errcode; / set serveroutput on exec trap_errcode Display Error With SQLERRM CREATEORREPLACEPROCEDUREtrap_errmesgIS emesgVARCHAR2(250); BEGIN NULL; RAISEtoo_many_rows; EXCEPTION WHENOTHERSTHEN emesg := SQLERRM; dbms_output.put_line(emesg); ENDtrap_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 CREATEORREPLACEPROCEDUREinvcur_exceptionIS CURSORx_cur is SELECT* FROMall_tables; x_rec x_cur%rowtype; BEGIN LOOP -- note the cursor was not opened before the FETCH FETCHx_curINTOx_rec; EXITWHENx_cur%notfound; NULL; ENDLOOP; EXCEPTION WHENINVALID_CURSORTHEN dbms_output.put_line('Whoops!'); WHENOTHERSTHEN dbms_output.put_line('Some Other Problem'); ENDinvcur_exception; / set serveroutput on exec invcur_exception Two Many Rows Exception Demo CREATEORREPLACEPROCEDUREtmr_exceptionIS x all_tables.table_name%TYPE; BEGIN -- note the statement will try to fetch many values SELECTtable_name -- try toSELECTmany things into 1 var INTOx FROMall_tables; EXCEPTION WHENTOO_MANY_ROWSTHEN dbms_output.put_line('Too Many Rows'); WHENOTHERSTHEN dbms_output.put_line('Some Other Problem'); ENDtmr_exception; / set serveroutput on exec tmr_exception Division By Zero Error Trapping Demo CREATEORREPLACEPROCEDUREdbz_exception(numinNUMBER) IS zNUMBER:= 0; xNUMBER; BEGIN x := numin / z; EXCEPTION WHENZERO_DIVIDETHEN dbms_output.put_line('Division By Zero'); WHENOTHERSTHEN dbms_output.put_line('Some Other Problem'); ENDdbz_exception; / set serveroutput on exec dbz_exception(6) Divide By Zero Error Pass In The Zero CREATEORREPLACEPROCEDUREzero_div(numinNUMBER) IS zNUMBER:=TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')); xNUMBER; BEGIN x := z / numin; dbms_output.put_line('Division By ' ||TO_CHAR(numin)); EXCEPTION WHENZERO_DIVIDETHEN dbms_output.put_line('Division By Zero'); WHENOTHERSTHEN dbms_output.put_line('Some Other Problem'); ENDzero_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 CREATEORREPLACEFUNCTIONis_ssn(string_inVARCHAR2) RETURNVARCHAR2IS -- validating ###-##-#### format delimVARCHAR2(1); part1NUMBER(3,0); part2NUMBER(2,0); part3NUMBER(4,0); too_longEXCEPTION; too_shortEXCEPTION; delimiterEXCEPTION; BEGIN IFLENGTH(string_in) > 11THEN RAISEtoo_long; ELSIFLENGTH(string_in) < 11THEN RAISEtoo_short; ENDIF; part1 :=TO_NUMBER(SUBSTR(string_in,1,3),'999'); delim :=SUBSTR(string_in,4,1); IFdelim <> '-'THEN RAISEdelimiter; ENDIF; part2 :=TO_NUMBER(SUBSTR(string_in,5,2),'99'); delim :=SUBSTR(string_in,7,1); IFdelim <> '-'THEN RAISEdelimiter; ENDIF; part3 :=TO_NUMBER(SUBSTR(string_in,8,4),'9999'); RETURN'TRUE'; EXCEPTION WHENtoo_longTHEN dbms_output.put_line('More Than 11 Characters'); RETURN'FALSE'; WHENtoo_shortTHEN dbms_output.put_line('Less Than 11 Characters'); RETURN'FALSE'; WHENdelimiterTHEN dbms_output.put_line('Incorrect Delimiter'); RETURN'FALSE'; WHENOTHERSTHEN dbms_output.put_line('Some Other Issue'); RETURN'FALSE'; ENDis_ssn; / set serveroutput on SELECTis_ssn('123-45-6789')FROMDUAL; SELECTis_ssn('123-45-67890')FROMDUAL; SELECTis_ssn('123-45-678')FROMDUAL; SELECTis_ssn('123-45=67890')FROMDUAL; Pragma Exception_Init PRAGMA EXCEPTION_INIT Demo CREATETABLEresults ( sourcenoNUMBER(10)NOTNULL, testnoNUMBER(3)NOTNULL, locationidNUMBER(10)NOTNULL); -- the basic procedure CREATEORREPLACEPROCEDUREPragmaExcInit IS BEGIN INSERTINTOresults (sourceno) VALUES ('1'); COMMIT; ENDPragmaExcInit; / exec pragmaexcinit -- the same procedure with exception trapping CREATEORREPLACEPROCEDUREPragmaExcInit IS FieldsLeftNullEXCEPTION; PRAGMA EXCEPTION_INIT(FieldsLeftNull, -01400); BEGIN INSERTINTOresults (sourceno) VALUES ('1'); COMMIT; EXCEPTION WHENFieldsLeftNullTHEN dbms_output.put_line('ERROR: Trapped Fields Left Null'); WHENOTHERSTHEN dbms_output.put_line(SQLERRM); ENDPragmaExcInit; / set serveroutput on exec pragmaexcinit RAISE Demo Procedure With User Defined Exceptions AndRAISE CREATEORREPLACEPROCEDUREraise_demo(invalNUMBER) IS evennoEXCEPTION; oddnoEXCEPTION; BEGIN IFMOD(inval, 2) = 1THEN RAISEoddno; ELSE RAISEevenno; ENDIF; EXCEPTION WHENevennoTHEN dbms_output.put_line(TO_CHAR(inval) || ' is even'); WHENoddnoTHEN dbms_output.put_line(TO_CHAR(inval) || ' is odd'); ENDraise_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_messageVARCHAR2(2048) TRUE add to error stack FALSE replace error stack (the default) CREATEORREPLACEPROCEDUREraise_app_error(invalNUMBER) IS evennoEXCEPTION; oddnoEXCEPTION; BEGIN IFMOD(inval, 2) = 1THEN RAISEoddno; ELSE RAISEevenno; ENDIF; EXCEPTION WHENevennoTHEN RAISE_APPLICATION_ERROR(-20001, 'Even Number Entered'); WHENoddnoTHEN RAISE_APPLICATION_ERROR(-20999, 'Odd Number Entered'); ENDraise_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 stepVARCHAR2(2); iNUMBER(1) := 5; nNUMBER(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 WHENZERO_DIVIDETHEN dbms_output.put_line('Failure at: ' ||step); END; / Declaration Exceptions Declaration Exceptions Declaration exceptions can not be trapped with an error handler DECLARE iNUMBER(3) :=1000; BEGIN NULL; EXCEPTION WHENOTHERSTHEN NULL; END; / CREATEORREPLACEPROCEDUREdemo(somevalINNUMBER)IS iNUMBER(3) := someval; BEGIN i := i+0; EXCEPTION WHENOTHERSTHEN NULL; END; / exec demo(999); exec demo(1000); Exception Handling Demo Incomplete Handling CREATETABLEtest ( col INT); ALTERTABLEtest ADDCONSTRAINTpk_test PRIMARY KEY (col) USING INDEX; CREATEORREPLACEPROCEDUREp IS BEGIN INSERTINTOtestVALUES(1); ENDp; / BEGIN p; p; END; / -- no records inserted as expected SELECT*FROMtest; BEGIN p; p; EXCEPTION WHENOTHERSTHEN NULL; END; / -- one record inserted SELECT*FROMtest; System-Defined Event Trapping Error Stack Trapping with System Events Declaration exceptions can not be trapped with an error handler set serveroutput on CREATEORREPLACETRIGGERe_trigger BEFORE delete ON t DECLARE l_textora_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 ); FORiIN1 .. nvl(l_text.count,0) LOOP dbms_output.put_line(l_text(i) ); ENDLOOP; dbms_output.put_line( 'error text: ' ); FORiIN1 ..ora_server_error_depth LOOP dbms_output.put_line(ora_server_error_msg(i) ); ENDLOOP; dbms_output.put_line( '--------------------' ); ENDe_trigger; / Simple Error Handling Procedure Function To Identify The User Logged Onto Oracle CREATEORREPLACEFUNCTIONgetosuserRETURNVARCHAR2IS vOSUseruser_users.username%TYPE; BEGIN SELECTosuser INTOvOSUser FROMsys.v_$session WHEREsid = ( SELECTsid FROMsys.v_$mystat WHERErownum = 1); RETURNvOSUser; EXCEPTION WHENOTHERSTHEN RETURN'UNK'; ENDgetosuser; / SELECTgetosuserFROMDUAL; The Table Holding The Output Of The Error Logging Procedure CREATETABLEerrorlog ( procnameVARCHAR2(61), loadfilenameVARCHAR2(40), runtime DATEDEFAULTSYSDATE, osuserVARCHAR2(30), mesgtextVARCHAR2(250)); The Error Logging Procedure CREATEORREPLACEPROCEDURElog_error( pProcNameVARCHAR2, pLoadFileVARCHAR2, pMesgTextVARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERTINTOerrorlog (procname, loadfilename, osuser, mesgtext) VALUES (pProcName, pLoadFile,getOSUser, pMesgText); COMMIT; -- No exception handler intentionally. Why? ENDlog_error; / To Test The Error Logging Procedure exec log_error('Test', 'None', 'Did it work?'); SELECT*FROMerrorlog; Database-Wide Exception Handling Using AFTER SERVERERROR CREATETABLEerror_log ( error_timestamp TIMESTAMP(9), database_name VARCHAR(50), instance_numberNUMBER, error_numberNUMBER, error_messageVARCHAR2(255), logged_on_asVARCHAR2(30), client_hostVARCHAR2(50), service_nameVARCHAR2(30)); CREATEORREPLACEPROCEDUREerror_trap IS odbnameVARCHAR2(50); -- Oracle database name oinstNUMBER; -- Oracle instance number enumNUMBER; -- Error Message number emsgVARCHAR2(250); -- Error text curschemaVARCHAR2(30); clihostVARCHAR2(50); serv_nameVARCHAR2(30); -- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN enum := sqlcode; emsg := sqlerrm; odbname := ora_database_name; oinst := ora_instance_num; SELECTsys_context('USERENV', 'CURRENT_SCHEMA') INTOcurschema FROMDUAL; SELECTsys_context('USERENV', 'HOST') INTOclihost FROMDUAL; SELECTsys_context('USERENV', 'SERVICE_NAME') INTOserv_name FROMDUAL; INSERTINTOerror_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; ENDerror_trap; / CREATEORREPLACETRIGGERerror_trig AFTER SERVERERROR ON DATABASE CALL error_trap / BEGIN RAISEzero_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 SELECTerror_timestamp, database_name, instance_number FROMerror_log; SELECTerror_timestamp, error_number, error_message FROMerror_log; SELECTlogged_on_as, client_host, service_name FROMerror_log; Robust Error Handling Procedure Formatting Error Stack Tables And Sequence CREATETABLEerrors ( moduleVARCHAR2(50), seq_numberNUMBER, error_numberNUMBER, error_mesgVARCHAR2(100), error_stackVARCHAR2(2000), call_stackVARCHAR2(2000), timestamp DATE); ALTERTABLEerrors ADDCONSTRAINTpk_errors PRIMARY KEY (module, seq_number) USING INDEX TABLESPACE indx_sml; CREATETABLEcall_stacks ( moduleVARCHAR2(50), seq_numberNUMBER, call_orderNUMBER, object_handleVARCHAR2(10), line_numNUMBER, object_nameVARCHAR2(80)); ALTERTABLEcall_stacks ADDCONSTRAINTpk_call_stacks PRIMARY KEY (module, seq_number, call_order) USING INDEX TABLESPACE indx_sml; ALTERTABLEcall_stacks ADDCONSTRAINTfk_cs_errors FOREIGN KEY (module, seq_number) REFERENCES errors (module, seq_number) ON DELETE CASCADE; CREATETABLEerror_stacks ( moduleVARCHAR2(50), seq_numberNUMBER, error_orderNUMBER, facilityCHAR(3), error_numberNUMBER(5), error_mesgVARCHAR2(100)); ALTERTABLEerror_stacks ADDCONSTRAINTpk_error_stacks PRIMARY KEY (module, seq_number, error_order) USING INDEX TABLESPACE indx_sml; ALTERTABLEerror_stacks ADDCONSTRAINTfk_es_errors FOREIGN KEY (module, seq_number) REFERENCES errors (module, seq_number) ON DELETE CASCADE; CREATESEQUENCEerror_seq START WITH 1 INCREMENT BY 1; Error Handling Package Header CREATEORREPLACEPACKAGEErrorPkgAS /* 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 informationin 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. */ PROCEDUREHandleAll(p_TopBOOLEAN); /* Prints the error and call stacks (using DBMS_OUTPUT) for the given module and sequence number. */ PROCEDUREPrintStacks(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. */ PROCEDUREStoreStacks(p_Module IN errors.module%TYPE, p_SeqNum OUT errors.seq_number%TYPE, p_CommitFlagBOOLEANDEFAULTFALSE); ENDErrorPkg; / Error Handling Package Body CREATEORREPLACEPACKAGEBODYErrorPkgIS v_NewLineCONSTANTCHAR(1) :=CHR(10); v_HandledBOOLEAN:=FALSE; v_ErrorStackVARCHAR2(2000); v_CallStackVARCHAR2(2000); PROCEDUREHandleAll(p_TopBOOLEAN) IS BEGIN IFp_TopTHEN v_Handled :=FALSE; ELSIFNOTv_HandledTHEN v_Handled :=TRUE; v_ErrorStack :=DBMS_UTILITY.FORMAT_ERROR_STACK; v_CallStack :=DBMS_UTILITY.FORMAT_CALL_STACK; ENDIF; ENDHandleAll; --=================================================== PROCEDUREPrintStacks( 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; CURSORc_CallCur IS SELECTobject_handle, line_num, object_name FROMcall_stacks WHEREmodule = p_Module ANDseq_number = p_SeqNum ORDER BYcall_order; CURSORc_ErrorCur IS SELECTfacility, error_number, error_mesg FROMerror_stacks WHEREmodule = p_Module ANDseq_number = p_SeqNum ORDER BYerror_order; BEGIN SELECTtimestamp, error_mesg INTOv_TimeStamp, v_ErrorMsg FROMerrors WHEREmodule = p_Module ANDseq_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(' ------------- ----------- -----------'); FORv_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); ENDLOOP; -- Output the error stack. dbms_output.put_line('Complete Error Stack:'); FORv_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); ENDLOOP; ENDPrintStacks; --=================================================== PROCEDUREStoreStacks(p_Module IN errors.module%TYPE, p_SeqNum OUT errors.seq_number%TYPE, p_CommitFlagBOOLEANDEFAULTFALSE) IS v_SeqNumNUMBER; v_IndexNUMBER; v_LengthNUMBER; v_EndNUMBER; v_CallVARCHAR2(100); v_CallOrderNUMBER:= 1; v_ErrorVARCHAR2(120); v_ErrorOrderNUMBER:= 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. SELECTerror_seq.nextval INTOv_SeqNum FROMDUAL; p_SeqNum := v_SeqNum; -- Insert first part of header info. into the errors table INSERTINTOerrors (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 */ INSERTINTOerror_stacks (module, seq_number, error_order, facility, error_number, error_mesg) VALUES (p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg); IFv_ErrorOrder = 1THEN v_FirstErrNum := v_ErrNum; v_FirstErrMsg := v_Facility || '-' ||TO_NUMBER(v_ErrNum) || ': ' || v_ErrMsg; ENDIF; v_ErrorOrder := v_ErrorOrder + 1; ENDLOOP; -- Update the errors table with the message and code UPDATEerrors SET error_number = v_FirstErrNum, error_mesg = v_FirstErrMsg WHEREmodule = p_Module ANDseq_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 IFv_CallOrder > 1THEN INSERTINTOcall_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); ENDIF; v_Callorder := v_CallOrder + 1; ENDLOOP; IFp_CommitFlagTHEN COMMIT; ENDIF; ENDStoreStacks; ENDErrorPkg; / Format Error Stack Demo Table And Trigger CREATETABLEttt (f1 number); CREATEORREPLACETRIGGERttt_insert BEFOREINSERTON ttt BEGIN RAISEZERO_DIVIDE; ENDttt_insert; / Error Producing Procedures (A, B, And C) CREATEORREPLACEPROCEDURECAS BEGIN INSERTINTOtttVALUES(7); EXCEPTION WHENOTHERSTHEN ErrorPkg.HandleAll(FALSE); RAISE; ENDC; / --=================================================== CREATEORREPLACEPROCEDUREB AS BEGIN C; EXCEPTION WHENOTHERSTHEN ErrorPkg.HandleAll(FALSE); RAISE; ENDB; / --=================================================== CREATEORREPLACEPROCEDUREA AS v_ErrorSeqNUMBER; BEGIN B; EXCEPTION WHENOTHERSTHEN ErrorPkg.HandleAll(TRUE); ErrorPkg.StoreStacks('Scott', v_ErrorSeq,TRUE); ErrorPkg.PrintStacks('Scott', v_ErrorSeq); ENDA; / 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 theOracle 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 theWHENclauses of a CASE statement is selected and there is noELSEclause. COLLECTION_IS_NULL ORA-06531 Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table orVARRAY. 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 TheSELECTstatement returned no rows or referenced a deleted element in a nested table or referenced an initialized element in an Index-Bytable. 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 passedto 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 SQLINTOstatement 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 aVARCHAR2(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 Anonymous Block DBMS_UTILITY DDL Triggers Instead-Of Triggers Errors Function Procedure System Events System Triggers Table Triggers UTL_LMS Warnings