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

<ford_desperado (AT) yahoo (DOT) com> a écrit dans le message de
news:1103212508.962538.182440 (AT) z14g2000cwz (DOT) googlegroups.com...
Put your insert statements in an autonmous transaction procedure.

http://download-west.oracle.com/docs..._ora.htm#27440

Regards
Michel Cadot

I do use autonomous transactions to log error messages:

CREATE OR REPLACE PROCEDURE SD_LOG_SQL_ERROR(SQLCODE IN NUMBER,
SQLERRM IN VARCHAR2, BLOCK_NAME IN VARCHAR2, PARAM_VALUES IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SD_SQL_ERROR_LOG(ERROR_TIME, SQLCODE, SQLERRM, BLOCK_NAME,
PARAM_VALUES)
VALUES(SYSDATE, SQLCODE, SQLERRM, BLOCK_NAME, PARAM_VALUES);
COMMIT;
END
;

....

EXCEPTION
WHEN OTHERS THEN
SD_LOG_SQL_ERROR(SQLCODE, SQLERRM, 'My_SP_Name',
TRIM(My_SP_parameters));
ROLLBACK;
RAISE;

It works all right and it is not my concern.
It is the DML in the body of the procedure, not in the exception
handler:

INSERT INTO AAT VALUES(P_N);

that is a part of ongoing transaction and somehow gets rolled back.
This DML does not belong to an autonomous transaction.

In article <1103212508.962538.182440 (AT) z14g2000cwz (DOT) googlegroups.com>,
ford_desperado (AT) yahoo (DOT) com says...
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?



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

Thanks Tom

I have an SP issuing several DML commands. in the procedure that called
the failed one, I wanted to figure out which DML failed, and I noticed
that changes made by a failed SP are rolled back. I did not know why,
and I was very curious. I guessed there is an implicit savepoint. Thank
you for confirming that.

Recording which DML has failed is very easy:

V_STEP VARCHAR2(20);
....
V_STEP := 'INSERT INTO TABLE1';
INSERT INTO TABLE1 ...

V_STEP := 'INSERT INTO TABLE2';
INSERT INTO TABLE2 ...

....

EXCEPTION
WHEN OTHERS THEN
SD_LOG_SQL_ERROR(SQLCODE, SQLERRM, 'My_SP_Name '||
V_STEP,
TRIM(My_SP_parameters));
ROLLBACK;
RAISE;

I've described SD_LOG_SQL_ERROR in my previous post.

In article <1103219054.656660.115830 (AT) c13g2000cwb (DOT) googlegroups.com>,
ford_desperado (AT) yahoo (DOT) com says...

this would be an appropriate use of an autonomous transaction.

create procedure SD_LOG_SQL_ERROR( .... )
as
pragma autonomous_transaction;
...
begin
insert into log_table;
....
commit;
end;



it will operate as an independent subtransaction - it will only see its work, it
will only commit its work.


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

Thanks Tom,

the only difference between your procedure and mine that I noticed is
that yours is in lowercase while mine is in uppercase. Are you
suggesting that uppercase SQL is bad programming style?

In article <1103236627.680720.19790 (AT) c13g2000cwb (DOT) googlegroups.com>,
ford_desperado (AT) yahoo (DOT) com says...

No, the big difference is

a) pragma autonomous_transaction;
b) commit;

(but no, I am personally not a fan of uppercase in code, I find it miserable
hard to read for some reason... but that is a matter of opinion)


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

"Thomas Kyte" <tkyte (AT) oracle (DOT) com> wrote

bit ott, but whats public sector- different that Government health and
education?

In article <OFqwd.1486$Ar5.200 (AT) text (DOT) news.blueyonder.co.uk>, Dave says...
Public Sector is the Government bit of GEH. The E and H got "reorged". We
handle Federal and Civilian agencies as well as State and Local Government.

I'm back where I began in 1993 -- Oracle Federal


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