NOTE:How Oracle Does Implicit Rollbacks
Before executing anINSERT,UPDATE, orDELETEstatement, 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 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 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 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' 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:
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;
/ | CREATEORREPLACEPROCEDUREmyproc IS
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_error IS
BEGIN
NULL;
ENDno_error;
/
exec no_error |
Modified To Force An Error | CREATEORREPLACEPROCEDUREforce_error IS
BEGIN
NULL;
RAISEtoo_many_rows;
ENDforce_error;
/
exec force_error |
Trap And Hide The Error | CREATEORREPLACEPROCEDUREtrap_error IS
BEGIN
NULL;
RAISEtoo_many_rows;
EXCEPTION
WHENOTHERSTHEN
NULL;
ENDtrap_error;
/
exec trap_error |
Display Error With SQLCODE | CREATEORREPLACEPROCEDUREtrap_errcode IS
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_errmesg IS
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_exception IS
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_exception IS
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 | CREATEORREPLACEPACKAGEErrorPkg 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.
*/
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 | CREATEORREPLACEPACKAGEBODYErrorPkg IS
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) | CREATEORREPLACEPROCEDUREC AS
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 |
|
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
