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

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.

Answer 2

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.

Answer 3

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;

Answer 4

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

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

Answer 5

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.

Answer 6

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.