Question

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?

Answer 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.

Answer 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.

Answer 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.

Answer 4

There are generally two options:

Send the output to an Oracle table (or temporary table)
Write to the (database host) filesystem with UTL_FILE

If you don t have OS access to the database host, you can still write to the dbhost filesystem and bind an Oracle externally-defined table to the file so it can be queried with a SELECT.

Answer 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.

Answer 6

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.

Answer 7

You can use DBMS Pipe and the Pipe Viewer in PL/SQL Developer to asynchronously catch all infos as they are put into the pipe.

Be careful to only put things into a pipe when there s someone to read it. Otherwise, your call will fail when the pipe is full.

There s also the possibility of using events, PL/SQL Developer has an event monitor as well. And the docs should provide an example of how to do it.

Answer 8

Another option is to have your PL/SQL call a procedure to send an email with the log message in it. This requires that your database has email sending capability, which can be added using UTL_SMTP.

友情链接