Oracle PL/SQL - tips for immediate output / console printing
Solution 1
You can have a procedure that writes messages to a table using an autonomous transaction something like:
procedure log (p_message)
is
pragma autonomous_transaction;
begin
insert into message_log (user, datetime, message)
values (user, sysdate, p_message);
commit;
end;
Then monitor the table from another Oracle session.
Solution 2
we have a small trick for this.
you can use DBMS_APPLICATION_INFO.set_client_info(" some information here"); creating some variables and replace the string inside " ".
and use select client_info from v$session to monitor the progress.
Solution 3
I've been using dbms_pipe for this purpose. Send messages to a named pipe and read them from another session. This method may not work in a RAC environment when the writing and reading processes may connect to a different node.
Alternatively you can insert messages into a table using a procedure that runs in its own session using "pragma autonomous_transaction". You can the query these messages from another session
Edit: I see that my second option has already been mentioned.
Solution 4
An alternative is to use a pipelined function that returns your logging information. See here for an example: http://berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html When you use a pipelined function you don't have to use another SQLPLUS/Toad/sql developer etc... session.
Solution 5
It may depend on your client tool. I haven't used SQL*Plus in a while, but when I'm debugging procedures in PL/SQL Developer, I open a command window and issue a SET SERVEROUTPUT ON
command. Then when I execute the procedure, anything printed by DBMS_OUTPUT.PUT_LINE
shows up right away.
Edit: you're right, I guess I was only seeing that with larger amounts of output or something. Anyhow I did some searching online and came across this log4plsql - may be useful.
Comments
-
FrustratedWithFormsDesigner almost 2 years
I have a number of pl/sql procedures that can take several minutes to run. While developing them, I've added a few print statements to help debug and also provide some feedback and progress indicators. Initially, I ran these on small test sets and output was almost instantaneous. Now that I'm testing with larger test sets that take several minutes to run, I find that printing to the console is no longer suitable, because nothing gets printed until the procedure ends. I'm used to working in environments that do not buffer their output and print it immediately and adding simple print-statements for simple debugging and diagnostic is common.
Is it possible in pl/sql to print output immediately (not buffered)? If not, what alternatives do people recommend to get a similar result?
-
FrustratedWithFormsDesigner over 14 yearsI am usign PL/SQL Developer, that does not work. All of the output is printed at once when the script has finished executing.
-
FrustratedWithFormsDesigner over 14 yearsI have access to a v$session_longops and v$session_connect_info and neither of them has a client_info field. Would v$session_longops be just as good?
-
Henry Gao over 14 yearsno, you need to use v$session view. ask you DBA if you do not have access.
-
berlebutch over 14 yearsMore information on pragma autonomous_transaction stackoverflow.com/questions/1335331/autonomoustransaction
-
FrustratedWithFormsDesigner over 14 yearsThis is interesting, but wouldn't I have to modify my existing function to be pipelined, or possibly wrap it in a pipeline function?
-
FrustratedWithFormsDesigner over 14 yearsThis looks good, but I get an error: "identifier 'DBMS_PIPE' must be declared", I think I need to talk to the DBA about this. Probably a permissions issue.
-
FrustratedWithFormsDesigner over 14 yearsThis looks good, I'll probably be trying it out today :) How does it compare to the other suggestion of using pipes? Which will be easier to view and monitor?
-
FrustratedWithFormsDesigner over 14 yearsBoth of these look like interesting possibilities, although I get an error (probably permissions-related) when I try to use teh dbms_pipe package. What are the pros and cons of each approach? I don't know much about Oracle pipes so I'm not sure how to evaluate that, but it looks closer to what I want than a table-driven solution.
-
Tony Andrews over 14 yearsI haven't used the pipe method, so can't comment on that. One difference with the table method is that the messages are persistent. This can be an advantage, but it also means you have to manage them so that the table doesn't grow infinitely!
-
tuinstoel over 14 yearsOf course you have to change your code. Just wrapping it inside a pipelined function doesn't help you because you want immediate logging and not only logging when something is done.
-
FrustratedWithFormsDesigner over 14 yearsI'm going to accept this answer as persistence could be a serious issue (in the sense that we will need it!) further down the road, but the pipes idea and even the pipelined function idea were interesting. If I get some downtime, I will have to try them all!