How to redirect the output of DBMS OUTPUT PUT LINE to a file

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

User · Answer

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

User · Answer

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 gt  create directory tmp as   tmp     Directory created  SQL gt  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 gt  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 gt  host cat  tmp foo log  this is a test

User · Answer

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

User · Answer

Using UTL FILE instead of DBMS OUTPUT will redirect output to a file   http   oreilly com catalog oraclebip chapter ch06 html

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try This   SELECT systimestamp INTO time db FROM dual    DBMS OUTPUT PUT LINE  time before procedure      time db

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

Examples related to oracle

Examples related to plsql

Examples related to oracle-sqldeveloper

Examples related to dbms-output