Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_OUTPUT on UTL_FILE
DBMS_OUTPUT on UTL_FILE [message #411627] Mon, 06 July 2009 02:32 Go to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Hi,

I have Package and wich is having many procedure and function with dbms_output debug messages to execute.
I want to read this messages in UTL file without changing my package(dbms_output). How can i achive this??

thanks & regatds
Manju
Re: DBMS_OUTPUT on UTL_FILE [message #411634 is a reply to message #411627] Mon, 06 July 2009 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Spool the result into a file.

Regards
Michel
Re: DBMS_OUTPUT on UTL_FILE [message #411743 is a reply to message #411627] Mon, 06 July 2009 09:12 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
manns wrote on Mon, 06 July 2009 09:32
Hi,

I have Package and wich is having many procedure and function with dbms_output debug messages to execute.


Make sure you only actually execute these dbms_output commands if a debug switch is set. Otherwise you might run into an overflow error in production.
Re: DBMS_OUTPUT on UTL_FILE [message #411770 is a reply to message #411743] Mon, 06 July 2009 11:52 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I don't understand the question. Do you mean you want to get the content of DBMS_OUTPUT buffer and write it to file?

SQL> set serveroutput on
SQL> DECLARE
  2      v_file     UTL_FILE.file_type;
  3      v_line     VARCHAR2 (200);
  4      v_status   NUMBER;
  5      n          NUMBER         := 1;
  6  BEGIN
  7      DBMS_OUTPUT.ENABLE (1000000);
  8      DBMS_OUTPUT.PUT_LINE('this is first test line');
  9      DBMS_OUTPUT.PUT_LINE('this is last test line');
 10      v_file    := UTL_FILE.fopen ('MY_DIR', 'test.txt', 'w');
 11     LOOP
 12        DBMS_OUTPUT.get_line (v_line, v_status);
 13        IF v_status = 0
 14        THEN
 15           UTL_FILE.put_line (v_file, n||'-->'||v_line);
 16           n    := n + 1;
 17        ELSE
 18           EXIT;
 19        END IF;
 20     END LOOP;
 21     UTL_FILE.fclose (v_file);
 22
 23     DBMS_OUTPUT.PUT_LINE('this final line is left in buffer');
 24  END;
 25  /
this final line is left in buffer

PL/SQL procedure successfully completed.

SQL>

Now, test.txt contains:

1-->this is first test line
2-->this is last test line



Re: DBMS_OUTPUT on UTL_FILE [message #411984 is a reply to message #411770] Tue, 07 July 2009 06:45 Go to previous message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Thanks Smile
Previous Topic: Materialized View
Next Topic: update query failing
Goto Forum:
  


Current Time: Thu Feb 13 17:14:06 CST 2025