Autonomous Transactions

From: <devjnr_at_gmail.com>
Date: 28 Aug 2006 04:00:24 -0700
Message-ID: <1156762824.101850.176090@b28g2000cwb.googlegroups.com>

I created a proc that do something like this:

---

create proc myproc
as
pragma autonomous_transaction;
begin

update T set field = UPPER(field) where id = 1; commit;

end;

---

and inserted in an anonymous block this way:

---

declare

       l_res1 varchar(100);
       l_res2 varchar(100);

begin

  select field into l_res1 from T where id = 1;   dbms_output.put_line('l_res1 = ' || l_res1);

  update temp set temp1 = l_res1;

• AT myproc;

  select field into l_res2 from T where id = 1;   dbms_output.put_line('l_res2 = ' || l_res2);

  update aaa_temp set temp2 = l_res2;

  rollback;

end;

---

As I expected last rollback didn't prevent myproc to commit.

But If I delete "pragma autonomous_transaction;" from proc body and run anonym block, I expect to have all work rolled back but it doesn't work.

1. update temp set temp1 = l_res1; is executed
2. myproc; is executed.

What is the behavior???

Thx to all. Received on Mon Aug 28 2006 - 06:00:24 CDT