10 June 2010

Response to questions about 9 June 2010 quiz: Autonomous transactions(363)

A number of players got in touch with us regarding this quiz question for 9 June 2010: "Which of the following choices state valid rules for defining a program as an autonomous transaction?" We said that these choices are correct: 1. If within that program a change is made to at least one table, you must perform a commit or rollback before terminating the program. Otherwise, an exception will be raised by Oracle and a rollback performed in your session. 2. The AUTONOMOUS_TRANSACTION pragma must appear in the declaration section of the program. And we claimed these are incorrect: 3. The AUTONOMOUS_TRANSACTION pragma must appear in the first line of the declaration section. 4. If any change is made to a table in the program, you must commit that change before returning control to the main transaction. 5. You can execute only static SQL statements inside an autonomous transaction. We respond below to the feedback we received (referencing choices by the numbers shown above), but the bottom line is this: We agree that choice (1) is in fact incorrect. Anyone who marked this as wrong will receive credit and ranking will be adjusted.

Choice 1 is Actually Incorrect

According to the Oracle 10.2 documentation http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1537 section Avoiding Errors with Autonomous Transactions, we find this: "If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, the transaction is rolled back." Consequently, the last sentence of the choice should be (correction in italics): "Otherwise, an exception will be raised by Oracle and a rollback performed on any changes made in the autonomous transaction program unit" not "a rollback performed in your session."

Choice 4: Change made to a Table

A player wrote that "change" "to a table" doesn't mean only table data, raising questions regarding the impact of DDL changes to the table as in:
declare
 /*create table a (d date, v varchar2 (8)); */
 pragma autonomous_transaction;
begin
 insert into a values (sysdate,'o');
 execute immediate 'truncate table a';
 execute immediate 'alter table a add (b varchar2(8))';
 execute immediate 'alter table a drop column b';
end;
You wouldn't have to do a commit explicilty in this case, because Oracle will do so implicitly. Regardless, this choice is incorrect, because you do not have to commit at all with an autonomous transaction. You could decide to rollback instead. So there is a possible ambiguity in the choice, but the ambiguity does not affect the correctness of the answer.

Choice 4: Rolling Back Table changes

A player wrote: "This option of the quiz doesn't indicate that a rollback is not possible." Well, the point of this choice is to make sure you understand that a commit is not required; either commit or rollback is required to terminate the autonomous transaction program unit without an exception being raised by Oracle.

Error and Exception?

A player wrote: "Regarding the first answer of the quiz for Wednesday 9 June 2010: shouldn't a distinction be made between an error and an exception? If a commit or rollback is omitted in the autonomous transaction an error occurs. This could result in an exception being raised, but first you would have to declare that exception and associate it with this particular error both of which has not necessarily been done yet." We do not think there is any problem with this. An exception is raised by Oracle. You don't need to declare any exception. You may be confusing Oracle error numbers with exceptions. In PL/SQL, an error condition is called an exception.

5 comments:

  1. You said:

    "Otherwise, an exception will be raised by Oracle and a rollback performed on any changes made in the autonomous transaction program unit" not "a rollback performed in your session."

    My comments:

    Do you mean if COMMIT or ROLLBACK is not used in autonomous transaction then only the statements in autonomous transaction are rolled back? If you meant that then please check the code below:

    -----------------------------------
    SQL> select * from a;

    COL1 COL2
    ---------- ----------
    1 ONE
    2 TWO
    3 THREE
    4 FOUR
    5 FIVE

    SQL> create or replace procedure my_proc as
    2 pragma autonomous_transaction;
    3 begin
    4 insert into a values (20, 'TWENTY');
    5 end;
    6 /

    Procedure created.

    SQL> begin
    2 insert into a values (10, 'TEN');
    3 my_proc;
    4 end;
    5 /
    begin
    *
    ERROR at line 1:
    ORA-06519: active autonomous transaction detected and rolled back
    ORA-06512: at "SCOTT.MY_PROC", line 5
    ORA-06512: at line 3


    SQL> select * from a;

    COL1 COL2
    ---------- ----------
    1 ONE
    2 TWO
    3 THREE
    4 FOUR
    5 FIVE
    -----------------------------------

    If you meant as I said above then the table should have a record inserted for values (10, 'TEN') because this change is made in parent transaction and not in autonomous transaction.

    If you didn't mean that then could you please explain the difference between "a rollback performed on any changes made in the autonomous transaction program unit" and "a rollback performed in your session"?

    I am a bit confused here.

    Thanks,
    Ramesh

    ReplyDelete
  2. Sorry but the code that you presented in about choice 4 also applies to choice 1 (and was with that I mind that I send that)

    declare
    /*create table a (d date, v varchar2 (8)); */
    pragma autonomous_transaction;
    begin
    insert into a values (sysdate,'o'); --A
    execute immediate 'truncate table a'; --B
    execute immediate 'alter table a add (b varchar2(8))'; --C
    execute immediate 'alter table a drop column b'; --D
    end;

    So only choice 2 was valid!
    1- if you use line C or D you made a change in the table and you didn't need to commit/roolback etc...
    2- choice 1 says "you need" soo implicit commit was not considered (you can check using lines A + B (lines c and C not necessary))

    so only

    ReplyDelete
  3. Ramesh, the insert of (10,’TEN’) was not rolled back, it never occurred because the call to my_proc raised an exception that was not handled.

    Filipe, choice 1 does not say that a COMMIT or ROLLBACK statement is required; it says that a commit or rollback must be performed. Executing DDL implicitly performs a commit.

    ReplyDelete
  4. @Filipe Silva

    Your point is correct that when we use DDLs inside a block that is marked as autonomous_transaction we don't explicitly need to commit/rollback as an implicit commit is issued by Oracle.

    But I think the question here is "whether we need commit/rollback in an autonomous_transaction", may it be implicit or explicit. And the answer is "Yes, commit/rollback is required in autonomous_transaction".

    Also, I think when you talk about transaction you talk about changes made to data in the tables using insert/delete/update and the DDLs do not count as a part of the transaction. So if you use DDLs in a block, the implicit commit they issue is just an side-effect. The statement "changes made to table" refers to "changes made to table data" and not "changes made to table structure".

    With these assumptions, I am in total agreement with Finn's justification.

    Could you please comment on this Finn and also please comment on the query that I raised in my earlier post. I am confused with "a rollback performed on any changes made in the autonomous transaction program unit" and "a rollback performed in your session".

    Thanks,
    Ramesh

    ReplyDelete
  5. jhall62: Yes, you are right. I missed to note that. Thanks for pointing it out.

    Here is the code I used to test.

    ===========================================

    SQL> select * from a;

    COL1 COL2
    ---------- --------------------
    1 one
    2 two
    3 three

    SQL> create or replace procedure my_proc as
    2 pragma autonomous_transaction;
    3 begin
    4 insert into a values (20, 'TWENTY');
    5 end;
    6 /

    Procedure created.

    SQL> begin
    2 insert into a values (10, 'TEN');
    3 my_proc;
    4 exception
    5 when others then
    6 null;
    7 end;
    8 /

    PL/SQL procedure successfully completed.

    SQL> select * from a;

    COL1 COL2
    ---------- --------------------
    1 one
    2 two
    3 three
    10 TEN

    ===========================================

    ReplyDelete