Thanks for the question, Rahul.

Asked: June 15, 2016 - 5:05 am UTC

Last updated: June 16, 2016 - 6:44 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hello,

Thanks for taking up this question.

My question is regarding usage of PRAGMA AUTONOMOUS_TRANSACTION. In the below example I have used AUTONOMOUS_TRANSACTION but I thin its effects can also be achieved without using this feature.

--Create table to hold logging information
CREATE TABLE logtab
( code INTEGER, text VARCHAR2(4000)
);

--Test table for inserting test data
CREATE TABLE test
(id NUMBER,name VARCHAR2(100)
);

--log_error utility package
CREATE OR REPLACE PACKAGE log_error
IS
PROCEDURE putline
(
code_in IN INTEGER,
text_in IN VARCHAR2
)
;
PROCEDURE saveline
(
code_in IN INTEGER,
text_in IN VARCHAR2
)
;
END;
/

CREATE OR REPLACE PACKAGE BODY log_error
IS
PROCEDURE putline
(
code_in IN INTEGER,
text_in IN VARCHAR2
)
IS
BEGIN
INSERT INTO logtab VALUES
( code_in, text_in
);
--COMMIT;------ Commenting out this line, also referenced in my question at the bottom.
END;

PROCEDURE saveline
(
code_in IN INTEGER,
text_in IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
putline (code_in, text_in);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
END;
/

--Inserting a record in test table and intentionally causing an error
DECLARE
num1 NUMBER;
BEGIN
INSERT INTO TEST VALUES
(1,'aa'
);
num1:=1/0; -- Intentionally causing an error here
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
log_error.saveline (SQLCODE, SQLERRM);
--log_error.putline (SQLCODE, SQLERRM); ---Commenting out this line, also referenced in my question at the bottom.
raise;
END;
/

This code does the following
1. Rollbacks the insert in TEST table
2. Insert a record in log table LOGTAB

My question is - The same can also be achieved without using PRAGMA AUTONOMOUS_TRANSACTION
1. Uncommenting COMMIT in procedure log_error.putline
2. Calling procedure log_error.putline instead of log_error.saveline in the exception section

and Connor said...

You are making a fundamental assumption here - that there is no outstanding transactions in play.

Consider the following code:

line1: delete from my_app;
line2: your anonymous block;

If you issue "rollback" in your code, you will rollback not just your changes, but anything that came before it as well.

That's where the autonmous transaction can be useful. If your code was just:

begin
  "do something"
exception
  when others then
     log_error;   <=== this does the autonomous stuff
     raise;
end;

Then your error will be logged, and the "raise" will rollback any changes *your* routine did, without impact the state of any existing transaction that may be active.

Hope this helps.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rahul Gupta, June 15, 2016 - 6:09 am UTC

Thanks for your quick response.

Suppose I do not want to re-raise the exception (I know its a BAD practice though), then any DML changes in my pl/sql block or prior will automatically be committed.

In this case, do I really need to use autonomous pragma for error logging?

Would you agree that I might as well use a regular procedure (non-autonomous) to insert a record in my log table and do COMMIT in that procedure?
(Refer log_error.putline procedure from my original question)

begin
"do something"
exception
when others then
log_error; <=== non-autonomous
--raise; -- not re-raising the exception
end;

June 16, 2016 - 2:36 am UTC

Not re-raising ? O....K.... :-)

I wonder what "log_error" will do/should do/etc when the error is: "ORA-01578: ORACLE data block corrupted"

Anyway... I think we all know my thoughts on not re-raising, so I wont labour the point.

I agree with your statement about the autonomous transaction. I would actually go further and say, the number of commits in a PLSQL program should never exceed that definition of the logical end to a transaction.

Which often for a PLSQL program (or entire suite of them) might be zero, because they are most commonly called from some other layer in the application stack.

Dont forget you can also get more granular control of the transaction processing in plsql using savepoint / rollback to savepoint, should the need arise.

Rahul Gupta, June 16, 2016 - 4:17 am UTC

Thanks for your inputs.

I used the example that you mentioned and tested the results.
line1: delete from my_app;
line2: your anonymous block;

You mentioned that - "If you issue "rollback" in your code, you will rollback not just your changes, but anything that came before it as well."

I found that both ROLLBACK and RAISE (re-raise) have the same affect. Both of them impact transactions in my pl/sql block (a procedure) as well as any existing transactions that may be active.

create or replace procedure test_proc as
num1 NUMBER;
BEGIN
INSERT INTO TEST_1 VALUES
(1,'aa'
);

num1:=1/0;
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
log_error.saveline (SQLCODE, SQLERRM);
RAISE;
END;
/

begin
insert into test values(1,'aaaa');
test_proc; -- procedure which calls ROLLBACK/RAISE
end;
/

I tested the program twice. Once with rollback and another with raise. In both occurrences, the DML changes did not persist in database.

June 16, 2016 - 6:44 am UTC

SQL> create table t (x int );

Table created.

SQL>
SQL> create or replace
  2  procedure my_proc is
  3    x int;
  4  begin
  5    insert into t values (2);
  6
  7    x := 1/0;
  8  exception
  9    when others then raise;
 10  end;
 11  /

Procedure created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL> exec my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MCDONAC.MY_PROC", line 8
ORA-06512: at line 1


SQL>
SQL> select * from t;

         X
----------
         1

1 row selected.

See how the failure of the procedure (with the raise) un-did the transaction work that the *procedure* had done, but left the "prior" transaction as it was before the procedure started.

Now let us put a rollback in there

SQL> create table t (x int );

Table created.

SQL>
SQL> create or replace
  2  procedure my_proc is
  3    x int;
  4  begin
  5    insert into t values (2);
  6
  7    x := 1/0;
  8  exception
  9    when others then rollback; raise;
 10  end;
 11  /

Procedure created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL> exec my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MCDONAC.MY_PROC", line 8
ORA-06512: at line 1


SQL>
SQL> select * from t;

no rows selected

That's a bad procedure because it changes things that it was not "entitled" to do.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs