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

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?

An old thread, but there is another alternative.

Since 9i you can use pipelined table function.

First, create a type as a table of varchar:

CREATE TYPE t_string_max IS TABLE OF VARCHAR2(32767);

Second, wrap your code in a pipelined function declaration:

CREATE FUNCTION fn_foo (bar VARCHAR2) -- your params
  RETURN t_string_max PIPELINED IS 
  -- your vars
BEGIN
  -- your code
END;
/

Replace all DBMS_OUTPUT.PUT_LINE for PIPE ROW.

Finally, call it like this:

SELECT * FROM TABLE(fn_foo('param'));

Hope it helps.

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

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

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.

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;

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

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.

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

Try This:

SELECT systimestamp INTO time_db FROM dual ;

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