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