How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?

Question

DBMS_OUTPUT is not the best tool to debug, since most environments don't use it natively. If you want to capture the output of DBMS_OUTPUT however, you would simply use the DBMS_OUTPUT.get_line procedure.

Here is a small example:

SQL> create directory tmp as '/tmp/';

Directory created

SQL> CREATE OR REPLACE PROCEDURE write_log AS
  2     l_line VARCHAR2(255);
  3     l_done NUMBER;
  4     l_file utl_file.file_type;
  5  BEGIN
  6     l_file := utl_file.fopen('TMP', 'foo.log', 'A');
  7     LOOP
  8        EXIT WHEN l_done = 1;
  9        dbms_output.get_line(l_line, l_done);
 10        utl_file.put_line(l_file, l_line);
 11     END LOOP;
 12     utl_file.fflush(l_file);
 13     utl_file.fclose(l_file);
 14  END write_log;
 15  /

Procedure created

SQL> BEGIN
  2     dbms_output.enable(100000);
  3     -- write something to DBMS_OUTPUT
  4     dbms_output.put_line('this is a test');
  5     -- write the content of the buffer to a file
  6     write_log;
  7  END;
  8  /

PL/SQL procedure successfully completed

SQL> host cat /tmp/foo.log

this is a test

As an alternative to writing to a file, how about writing to a table? Instead of calling DBMS_OUTPUT.PUT_LINE you could call your own DEBUG.OUTPUT procedure something like:

procedure output (p_text varchar2) is
   pragma autonomous_transaction;
begin
   if g_debugging then
      insert into debug_messages (username, datetime, text)
      values (user, sysdate, p_text);
      commit;
   end if;
end;

The use of an autonomous transaction allows you to retain debug messages produced from transactions that get rolled back (e.g. after an exception is raised), as would happen if you were using a file.

The g_debugging boolean variable is a package variable that can be defaulted to false and set to true when debug output is required.

Of course, you need to manage that table so that it doesn't grow forever! One way would be a job that runs nightly/weekly and deletes any debug messages that are "old".

If you are just testing your PL/SQL in SQL Plus you can direct it to a file like this:

spool output.txt
set serveroutput on

begin
  SELECT systimestamp FROM dual INTO time_db;
  DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db);
end;
/

spool off

IDEs like Toad and SQL Developer can capture the output in other ways, but I'm not familiar with how.

use set serveroutput on;

for example:

set serveroutput on;

DECLARE
x NUMBER;
BEGIN
x := 72600;
dbms_output.put_line('The variable X = '); dbms_output.put_line(x);
END;

In addition to Tony's answer, if you are looking to find out where your PL/SQL program is spending it's time, it is also worth checking out this part of the Oracle PL/SQL documentation.

Using UTL_FILE instead of DBMS_OUTPUT will redirect output to a file:

http://oreilly.com/catalog/oraclebip/chapter/ch06.html

As a side note, remember that all this output is generated in the server side.

Using DBMS_OUTPUT, the text is generated in the server while it executes your query and stored in a buffer. It is then redirected to your client app when the server finishes the query data retrieval. That is, you only get this info when the query ends.

With UTL_FILE all the information logged will be stored in a file in the server. When the execution finishes you will have to navigate to this file to get the information.

Hope this helps.