Autonomous procedure falsely committing?

[Dan Loomis]

Hi guys - I have a weird condition here I'm trying to get resolution
on, and unfortunately I'm not getting anywhere with my friends at
Oracle. I'm hoping it's something simple, or at the very least it's a
documented bug that I can simply reference and move on.

The following code and session output (I think) documents a condition
where a procedure marked for an automomous commit is commiting an
INSERT over a database link that is outside of its transactional
boundry. In a nutshell, my INSERT is committing when it shouldn't,
*only* in situations where I have an autonomous transaction, *and* I
have an error handler that doesn't gracefully rollback the
transaction. My test case:

--setup only - the actual test run is further down
create table cca_autonomous_ commit_test_9i (
id number,
creation_date date
)
/

create or replace procedure autonomous_inse rt is

PRAGMA AUTONOMOUS_TRAN SACTION;

begin

--insert into local database table
insert into cca_autonomous_ commit_test_9i values ( 1 ,sysdate );
commit;

end;
/

create or replace procedure dloomis_test

is

begin

--reset environment
delete from cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;
delete from cca_autonomous_ commit_test_9i;

commit;

--insert over database link.
insert into cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s values (
1, sysdate );

--call my autonomous procedure, which should *NOT* commit the record
above
autonomous_inse rt;

--raise error, for testing purposes only
raise NO_DATA_FOUND;

end;
/

--the test output
Session 1:

--Execute dloomis_test, which should throw no_data_found by design:

SQL> exec dloomis_test
BEGIN dloomis_test; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.DLOOMIS_T EST", line 20
ORA-06512: at line 1

Session 2 - everything looks ok:

SQL> select count(*) from cca_autonomous_ commit_test_9i;

COUNT(*)
----------
1

SQL> select count(*) from
cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;

COUNT(*)
----------
0
Now, if I Control-C out of session 1, the 8i record is commited
somehow:

Session 2:

SQL> select count(*) from
cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;

COUNT(*)
----------
1

This is not always reproducable, but if you run the test several times
over you will get the results above. My question is - how is the 8i
record being commited, if the only commit being issued is in the
autonomous procedure? I realize an explicit ROLLBACK command in the
WHEN OTHERS block will fix the issue, but I'd still like an answer on
why this happens under the above condition.

Thanks a bunch -

Dan Loomis
IT Engineer, Cisco Systems

[Mark D Powell]

dl*****@gmail.c om (Dan Loomis) wrote in message news:<e2******* *************** ****@posting.go ogle.com>...

Hi guys - I have a weird condition here I'm trying to get resolution
on, and unfortunately I'm not getting anywhere with my friends at
Oracle. I'm hoping it's something simple, or at the very least it's a
documented bug that I can simply reference and move on.

The following code and session output (I think) documents a condition
where a procedure marked for an automomous commit is commiting an
INSERT over a database link that is outside of its transactional
boundry. In a nutshell, my INSERT is committing when it shouldn't,
*only* in situations where I have an autonomous transaction, *and* I
have an error handler that doesn't gracefully rollback the
transaction. My test case:

--setup only - the actual test run is further down
create table cca_autonomous_ commit_test_9i (
id number,
creation_date date
)
/

create or replace procedure autonomous_inse rt is

PRAGMA AUTONOMOUS_TRAN SACTION;

begin

--insert into local database table
insert into cca_autonomous_ commit_test_9i values ( 1 ,sysdate );
commit;

end;
/

create or replace procedure dloomis_test

is

begin

--reset environment
delete from cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;
delete from cca_autonomous_ commit_test_9i;

commit;

--insert over database link.
insert into cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s values (
1, sysdate );

--call my autonomous procedure, which should *NOT* commit the record
above
autonomous_inse rt;

--raise error, for testing purposes only
raise NO_DATA_FOUND;

end;
/

--the test output
Session 1:

--Execute dloomis_test, which should throw no_data_found by design:

SQL> exec dloomis_test
BEGIN dloomis_test; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.DLOOMIS_T EST", line 20
ORA-06512: at line 1

Session 2 - everything looks ok:

SQL> select count(*) from cca_autonomous_ commit_test_9i;

COUNT(*)
----------
1

SQL> select count(*) from
cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;

COUNT(*)
----------
0
Now, if I Control-C out of session 1, the 8i record is commited
somehow:

Session 2:

SQL> select count(*) from
cca_autonomous_ commit_test_8i@ xxcts_sjoe_ccai s;

COUNT(*)
----------
1

This is not always reproducable, but if you run the test several times
over you will get the results above. My question is - how is the 8i
record being commited, if the only commit being issued is in the
autonomous procedure? I realize an explicit ROLLBACK command in the
WHEN OTHERS block will fix the issue, but I'd still like an answer on
why this happens under the above condition.

Thanks a bunch -

Dan Loomis
IT Engineer, Cisco Systems

Dan, the execution of an anonymous transaction as part of a
distributed transaction in 8i is unsupported. This is stated in the
8i manuals and normally results in an error being raised. Version 9+
does support performing an anonymous transaction as part of a
distributed transaction, but I would expect this to work only when
connecting to another version 9+ database.

It would appear each database is following the rules for its version.

IMHO -- Mark D Powell --