Re: Commit

A copy of this was sent to "Ivica Dimjasevic" <smokesoft_at_email.hinet.hr> (if that email address didn't require changing) On Fri, 1 Jun 2001 00:04:14 +0200, you wrote:

>Let I have two procedures:
>
>create procedure proc1 is
>begin
> ...
> proc2;
> ...
>end;
>
>create procedure proc2 is
>begin
> ...
> commit;
>end;
>
>How to get proc2 to commit only changes made within proc2 and not to commit
>changes within proc1.
>
>
>Thanks,
>Ivica

<sigh> no versions....

If you are using Oracle8i, you have access to a feature called an autonomous transaction. Using this feature, you could:

create or replace procedure proc2
as

   pragma autonomous_transaction;
begin

    <whatever work you want to do>
    commit;
end;

Then, if someone executes:

begin

   proc1;
   proc2;
end;
/

the work of proc1 is yet to be committed whilst the work of proc2 is commited.

See
http://osi.oracle.com/~tkyte/autonomous/index.html

for more examples.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp