The Oracle (tm) Users' Co-Operative FAQ

Can Oracle Spool Output from a procedure?


Author's name: Keith_Jamieson

Author's Email: Keih_Jamieson@hotmail.com

Date written: 22 Sep 2003

Oracle version(s): 9.2.0.1.0

This is a question which appears frequently in newsgroups mainly by people who are relatively new to oracle. Typically, they want to turn on spooling during testing to verify that the procedure is doing what they expect it to. We can’t do this directly, but we can work around the problem.

Back to index of questions


Here is some very simple PL/SQL to illustrate the problem.

 

create or replace

procedure do_some_work

as

begin

    dbms_output.put_line('started');

    exception

    when others

    then

        dbms_output.put_line('Exception occurred');

end do_some_work;

 

If you set server out on and call this procedure you will see that you get no output.

 

SQL> exec do_some_work

PL/SQL procedure successfully completed.

Solution

Probably the easiest method to work around this problem is to store the data in a table using an autonomous procedure.  You must have Oracle 8i or above

Firstly we create a table to hold our text.

create table output_table(time date,

                           text varchar2(4000));

Then we create an autonomous procedure which logs records into the table.

create or replace procedure log_output(p_text in varchar2)

as

pragma autonomous_transaction;

v_text varchar2(4000) := p_text;

begin

insert

  into output_table

      ( time,

        text)

values( sysdate,

        v_text);

commit;

end log_output;

 

Now when we execute the procedure we still don’t see any output on the screen.

SQL> exec do_some_work

PL/SQL procedure successfully completed.

However, we can issue a SQL statement to show us exactly what went on.

select to_char(time,'DD-MON-YYYY HH24:MI:SS'), text from output_table order by time desc


TO_CHAR(TIME,'DD-MON TEXT

-------------------- ---------

22-SEP-2003 13:13:58 started

In addition, because this is an autonomous transaction the commit for the message data is treated as a completely independent session, so it won’t affect any of your existing transactions.  Naturally, theres a whole lot more that you can do here such to enhance the procedure, eg logging the user_id, and the procedure name, deciding whether you want to clear out the procedure at the beginning of a run.  Also, you won’t get stuck with the 255 Character limit of DBMS_OUTPUT.

Alternative Solutions: In  8i and above, you can use Java Stored Procedures to call a Java Class that will write to the OS. If you are limited to Oracle 8, you can call a C external procedure to log any output.  The other alternative is to use the  UTL_FILE  package.  These other methods require more configuration, and you must have OS access to the Database server.   


Further reading: Autonomous Transactions , Java Stored Procedures, External Procedures


Back to top

Back to index of questions