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 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 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 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:
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:
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.
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.
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.
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.
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.
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)
/* 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.
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.
BEGIN IF p_Top THEN
v_Handled := FALSE; ELSIFNOT v_Handled THEN
v_Handled := TRUE;
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK; ENDIF; END HandleAll;
--=================================================== PROCEDURE PrintStacks(
p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE)
-- 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); ENDLOOP;
-- 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) || ': ');
--=================================================== PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
-- Insert first part of header info. into the errors table INSERTINTO 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
v_Index := 1;
Loop through the string, finding each 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.
-- 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 INSERTINTO call_stacks
(module, seq_number, call_order, object_handle, line_num,
(p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum,
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.