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

Solution 1:

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

Solution 2:

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

Solution 3:

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.

Solution 4:

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;