Home » SQL & PL/SQL » SQL & PL/SQL » Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION
Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400901] Thu, 30 April 2009 05:47 Go to next message
davidius
Messages: 2
Registered: April 2009
Location: UK
Junior Member
Hello we have a bit code that is ran manually via Oracle SQL Developer. It does a few inserts and loops and such. But instead of just reporting "anonymous block completed" we would like to get it to send the counts the script output pane (preferably as they are counted rather than when it concludes).

Any suggestions? Shocked



DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
tmpCount1_ NUMBER;
tmpCount2_ NUMBER;
tmpCount3_ NUMBER;

BEGIN
Select count(*) into tmpCount1_ from tbldata_1;
Select count(*) into tmpCount2_ from tbldata_2;
Select count(*) into tmpCount3_ from tbldata_3;
END;
Re: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400904 is a reply to message #400901] Thu, 30 April 2009 05:51 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're running this as script from SQL Developer then I don't see any need for PRAGMA AUTONOMOUS_TRANSACTION;

To get output in SQL Developer dbms_output is the simplest way but there's no way to get the results before the script finishes.
Re: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400909 is a reply to message #400901] Thu, 30 April 2009 06:00 Go to previous messageGo to next message
davidius
Messages: 2
Registered: April 2009
Location: UK
Junior Member
I forgot to leave in the commit at the bottom, but the script does other things like insers and deletes.
The reason it's a AUTONOMOUS_TRANSACTION is when we take that bit out it stops working and a manual commit is required.

I looked into DBMS_OUTPUT.PUT_LINE
for example;
DBMS_OUTPUT.PUT_LINE(tmpCount1_);

But I had trouble with the syntax and it didn't seem to fail and continued to report "anonymous block completed".
Re: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400910 is a reply to message #400901] Thu, 30 April 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why in the hell are you using "PRAGMA AUTONOMOUS_TRANSACTION;" Question

Why are you using a PL/SQL block?
Use dbms_output.

Regards
Michel
Re: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400913 is a reply to message #400909] Thu, 30 April 2009 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
davidius wrote on Thu, 30 April 2009 12:00

The reason it's a AUTONOMOUS_TRANSACTION is when we take that bit out it stops working and a manual commit is required.



Then you're doing something wrong.

Quote:

I looked into DBMS_OUTPUT.PUT_LINE
for example;
DBMS_OUTPUT.PUT_LINE(tmpCount1_);

But I had trouble with the syntax and it didn't seem to fail and continued to report "anonymous block completed".


What trouble? That's what I'd be putting.
Re: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION [message #400914 is a reply to message #400909] Thu, 30 April 2009 06:11 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
"set serveroutput on" or whatever is the equivalent in SQL Developer.

Regards
Michel
Previous Topic: search by like operator
Next Topic: Group Function with Analytic function
Goto Forum:
  


Current Time: Sat Feb 08 16:55:59 CST 2025