Re: problem with exception handling

In article <1103212508.962538.182440_at_z14g2000cwz.googlegroups.com>, ford_desperado_at_yahoo.com says...
>
>just run into a problem with Oracle 9i running on Linux
>
>If my exceptin handlers don't reraise exceptions, all works as
>expected:

all statements are atomic.

sql> exec foo;

that either 100% succeeds or 100% fails. (yes, there is an implicit savepoint in there to make everything atomic)

I don't see how you could make heads or tails of the current database state if you have a procedure that does N statements and somewhere in the middle of "N" statements it fails (so it has done maybe 3 of 5 statements, or 2 of 5 or 1 of 5 or none of 5 or maybe 4 of 5 -- you don't know).

Can you describe the business flow, the logic you are trying to achieve?

are you actually just trying to log errors to an exception table? if so, perhaps this is a legitimate use of an autonomous transaction -- but if not, I cannot see how it would make sense?

>
>CREATE TABLE AAT(N NUMBER NOT NULL PRIMARY KEY)
>Table created
>
>CREATE TABLE AAT11(errMessage VARCHAR2(30), N NUMBER)
>Table created
>
>/
>CREATE OR REPLACE PROCEDURE AAT_TEST(P_N IN NUMBER)
>AS
>PK_violation EXCEPTION;
>PRAGMA EXCEPTION_INIT(PK_violation, -1);
>BEGIN
>INSERT INTO AAT VALUES(P_N);
>IF(P_N = 2)
>THEN
>RAISE_APPLICATION_ERROR(-20001, 'My custom exception');
>END IF;
>EXCEPTION
>WHEN PK_violation
>THEN
>INSERT INTO AAT11 VALUES('PK_violation', P_N);
>WHEN OTHERS
>THEN
>INSERT INTO AAT11 VALUES('OTHERS', P_N);
>-- RAISE;
>END;
>Procedure created
>--------- should succeed -
>CALL AAT_TEST(1)
>Method called
>-------- should be PK violation
>CALL AAT_TEST(1)
>Method called
>-------- shiould be my custom exception
>CALL AAT_TEST(2)
>Method called
>
>SELECT * FROM AAT
>N
>----------
>1
>2
>2 rows selected
>
>
>SELECT * FROM AAT11
>ERRMESSAGE N
>------------------------------ ----------
>PK_violation 1
>OTHERS 2
>2 rows selected
>
>
>Hopwever, if I uncomment RAISE, the results are a little bit
>surprising:
>
>CREATE OR REPLACE PROCEDURE AAT_TEST(P_N IN NUMBER)
>AS
>PK_violation EXCEPTION;
>PRAGMA EXCEPTION_INIT(PK_violation, -1);
>BEGIN
>INSERT INTO AAT VALUES(P_N);
>IF(P_N = 2)
>THEN
>RAISE_APPLICATION_ERROR(-20001, 'My custom exception');
>END IF;
>EXCEPTION
>WHEN PK_violation
>THEN
>INSERT INTO AAT11 VALUES('PK_violation', P_N);
>WHEN OTHERS
>THEN
>INSERT INTO AAT11 VALUES('OTHERS', P_N);
>RAISE;
>END;
>Procedure created
>
>CALL AAT_TEST(1)
>Method called
>
>CALL AAT_TEST(1)
>Method called
>
>CALL AAT_TEST(2)
>ORA-20001: My custom exception
>ORA-06512: at "********.AAT_TEST", line 18
>SELECT * FROM AAT
>N
>----------
>1
>1 row selected
>
>
>SELECT * FROM AAT11
>ERRMESSAGE N
>------------------------------ ----------
>PK_violation 1
>1 row selected
>
>ROLLBACK
>Rollback complete
>
>SELECT * FROM AAT
>0 rows selected
>
>
>SELECT * FROM AAT11
>0 rows selected
>
>it looks as if Oracle implicitly added a savepoint before
>CALL AAT_TEST(2)
>and issued a ROLLBACK to that savepoint when the SP call failed.
>I don't want DML performed by the SP to be rolled back.
>Are there any ways to accomplish it?
>I'm not a student, this is a problem in production
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation