Re: problem with exception handling

<ford_desperado_at_yahoo.com> a écrit dans le message de news:1103212508.962538.182440_at_z14g2000cwz.googlegroups.com...
| just run into a problem with Oracle 9i running on Linux
|
| If my exceptin handlers don't reraise exceptions, all works as
| expected:
|
| 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
|

Put your insert statements in an autonmous transaction procedure.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#27440

Regards
Michel Cadot Received on Thu Dec 16 2004 - 10:19:48 CST