problem with exception handling

From: <ford_desperado_at_yahoo.com>
Date: 16 Dec 2004 07:55:08 -0800
Message-ID: <1103212508.962538.182440@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 Received on Thu Dec 16 2004 - 09:55:08 CST