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.
