Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: inner without outer transaction committing
DaLoverhino wrote:
> Hello.
>
> I'd like to be able to do something like the following:
>
> begin transaction
>
> -- Do stuff
> exec myproc;
> -- Do stuff
> exec myproc;
>
> commit; --OUTER BLOCK COMMIT
>
>
> Now, myproc looks like this:
>
> create procedure myproc as
> begin
> insert into MyTable ....;
> commit; --PROC COMMIT
> end;
>
>
> I want to make sure that the insert into MyTable happens all the time,
> when the proc gets called from the outer block, regardless of whether
> the outer block commits or not. Furthermore, I don't want the PROC
> COMMIT to commit any DML's issued in the outer block prior to the proc
> commit.
>
> How do I go about doing this?
>
> thanks.
>
Make it an autonomous transaction.
See SQL Reference Manual and Programmer's manual for examples, etc.
-- Regards, Frank van BortelReceived on Fri Apr 22 2005 - 03:42:30 CDT