How to redirect the output of DBMS_OUTPUT.PUT_LINE

Question

I need to debug in pl/sql to figure times of procedures, I want to use:

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

but I don't understand where the output goes to and how can I redirect it to a log file that will contain all the data I want to collect?

Answer 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

Answer 2

Try This:

SELECT systimestamp INTO time_db FROM dual ;

DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db);

Answer 3

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

Answer 4

Its possible write a file directly to the DB server that hosts your database, and that will change all along with the execution of your PL/SQL program.

This uses the Oracle directory TMP_DIR; you have to declare it, and create the below procedure:

CREATE OR REPLACE PROCEDURE write_log(p_log varchar2)
  -- file mode; thisrequires
--- CREATE OR REPLACE DIRECTORY TMP_DIR as '/directory/where/oracle/can/write/on/DB_server/';
AS
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen('TMP_DIR', 'my_output.log', 'A');
  utl_file.put_line(l_file, p_log);
  utl_file.fflush(l_file);
  utl_file.fclose(l_file);
END write_log;
/

Here is how to use it:

1) Launch this from your SQL*PLUS client:

BEGIN
  write_log('this is a test');
  for i in 1..100 loop
    DBMS_LOCK.sleep(1);
    write_log('iter=' || i);
  end loop;
  write_log('test complete');
END;
/

2) on the database server, open a shell and

    tail -f -n500 /directory/where/oracle/can/write/on/DB_server/my_output.log