Home » SQL & PL/SQL » SQL & PL/SQL » autonomous transaction
autonomous transaction [message #246741] Thu, 21 June 2007 13:54 Go to next message
Shanavas. Y
Messages: 5
Registered: November 2004
Junior Member
Dear sir,

I have created three procedures in a package and all are autonomous procedures, and I am filling a table by executing the three procedures. But I am calling these procedure thru the package, not executing all together, ie. first it will execute procedure1 then procedure 2 and so on.
What is the problem behind that.(format attached). Please advice me

PACKAGE BODY EXEC is
procedure pro1 is
pragma autonomous_transaction;
begin
for i in 1..100000 loop
insert into t1 values(i,'a');
commit;
end loop;
end;
procedure pro2 is
pragma autonomous_transaction;
begin
for i in 1..100000 loop
insert into t1 values(i,'b');
commit;
end loop;
end;
procedure pro3 is
pragma autonomous_transaction;
begin
for i in 1..100000 loop
insert into t1 values(i,'c');
commit;
end loop;
end;
END;

call method
execute exec.pr01;
execute exec.pr02;
execute exec.pr03;

Regards,
Shan
Re: autonomous transaction [message #246745 is a reply to message #246741] Thu, 21 June 2007 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the posting guidelines as enumerated in the STICKY post at the top of this forum.
Problem? What problem. I don't see any problem?
Re: autonomous transaction [message #246765 is a reply to message #246741] Thu, 21 June 2007 16:34 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
committing within a loop? seriously bad idea. How about you tell us what you are actually trying to do.
Re: autonomous transaction [message #246783 is a reply to message #246765] Thu, 21 June 2007 23:46 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Commit should follow the end loop. Committing inside a loop is a very poor standard of coding.
Re: autonomous transaction [message #246831 is a reply to message #246783] Fri, 22 June 2007 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, you can speed the inserts up by rewriting this:
for i in 1..100000 loop
insert into t1 values(i,'a');
end loop;
like this:
insert into t1
select lvl,'a' from (select level lvl from dual connect by level <= 100000);


You mention a problem in your OP.
Care to share with us what problem you're having?
Re: autonomous transaction [message #246835 is a reply to message #246831] Fri, 22 June 2007 03:01 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Care to share with us what problem you're having?
Then where would the fun be?
Previous Topic: running Oracle Procedures as a DOS Batch file
Next Topic: small logical query doubt
Goto Forum:
  


Current Time: Sun Dec 11 00:30:56 CST 2016

Total time taken to generate the page: 0.03924 seconds