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:
SQLexec 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:
SQLselect count(*) from cca_autonomous_ commit_test_9i;
COUNT(*)
----------
1
SQLselect 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:
SQLselect 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
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:
SQLexec 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:
SQLselect count(*) from cca_autonomous_ commit_test_9i;
COUNT(*)
----------
1
SQLselect 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:
SQLselect 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
Comment