Autonomous transaction scope and error collection

Question

I doubt a little. Suppose these are batch procedures:

PROCEDURE ERR_MANAGER(I_ERRM IN VARCHAR2) IS BEGIN ROLLBACK; --DO SOME STUFF END ERR_MANAGER; PROCEDURE test IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN test2; COMMIT; EXCEPTION WHEN OTHERS THEN ERR_MANAGER(SQLERRM); END test; PROCEDURE test2 IS BEGIN --DO SOME TRANSACTIONNAL DML RAISE_APPLICATION_ERROR(-20001, 'ERR'); --for the test purpose, in reality this could be any actual error END test2; 

So, as you can see there is an error in test2() , which will go up to test() , and then be processed in the err_manager() method.

I have 2 questions:

• What is the size of err_manager ()? Is it still in an offline transaction? I think so, since this is only a function call, but I would like to be sure.
• What happens if you exit a standalone transaction brutally due to an increase in error to the upper levels without taking any commit or rollback actions?

Many thanks. S.

Answer 1

• The transaction volume of the err_manager procedure is an autonomous call transaction, you are right.

  Procedures and functions inherit their calling transactions if they themselves are not autonomous transactions.

• When an offline transaction causes an unhandled error, it rolls back its changes and the error propagates to the calling application. Here's the test:

   SQL> CREATE TABLE t (id number); Table created. SQL> DECLARE 2 l NUMBER; 3 PROCEDURE p IS 4 pragma autonomous_transaction; 5 BEGIN 6 insert into t values (1); 7 raise_application_error(-20001, 'rollback?'); 8 END; 9 BEGIN 10 p; 11 EXCEPTION 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.put_line('error catched:' || sqlcode); 14 SELECT COUNT(*) INTO l FROM t; 15 DBMS_OUTPUT.put_line('lines in t: ' || l); 16 END; 17 / error catched:-20001 lines in t: 0 PL/SQL procedure successfully completed.