Re: Autonomous Transactions

devjnr_at_gmail.com wrote:
>
> 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.

remove the 'commit'

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------