• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3333
  • Last Modified:

How to redirect the output from PL/SQL program

Can any one help in solving my problem
 How to direct the output from PL/SQL program to a text file . Using DBMS_OUT.PUT_LINE() i am getting an errors Buffer overflow . Is there any another way to direct my output to file.

0
ratnaprasadd
Asked:
ratnaprasadd
1 Solution
 
lmedfoCommented:
Buffer overflow:
solution
ie:
set arraysize 1

Actually, you could set the arraysize to anything you want, it just tells the system how many record sets you want returned at a time.  Generally, has to be set when you are returning lots of data.

You could just spool the output to a txt file.
ie:
spool $home/myfiles/output.txt
@program.sql
spool off
0
 
andrewstCommented:
First, have you set the buffer size to the maximum 1 million bytes?

SQL> SET SERVEROUTPUT ON SIZE 1000000

If that still doesn't give you enough, you will have to use the UTL_FILE package.  This writes to a file on the SERVER.
In your PL/SQL you will open the file, output lines to it and then close it like this:

DECLARE
  f UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN( 'directory', 'filename', 'W' );
  UTL_FILE.PUT_LINE( f, 'Hello world' );
  UTL_FILE.FCLOSE( f );
END;
/
0
 
DatamonkeyCommented:
If you create a wrapper procedure for dbms_output you can circumvent the buffer overflow errors quite easily.
There are numerous examples out there on how to do that, this is one that comes from an oracle article:

CREATE OR REPLACE PROCEDURE Show_Message(pmv_Msg_in IN VARCHAR2)
IS
BEGIN
  IF LENGTH(pmv_Msg_in)  > 80 THEN
     DBMS_OUTPUT.Put_Line(SUBSTR(pmv_Msg_in,1,79));
     Show_Message(SUBSTR(pmv_Msg_in,80,LENGTH(pmv_Msg_in)));
  ELSE
     DBMS_OUTPUT.Put_Line(pmv_Msg_in);
  END IF;
EXCEPTION
  WHEN Others THEN
       DBMS_OUTPUT.Disable;
       DBMS_OUTPUT.Enable(1000000);
       Show_Message(SUBSTR(pmv_Msg_in,80,LENGTH(pmv_Msg_in)));
END Show_Message;

Now you simply call show_message instead of dbms_output and you won't get the buffer overflow errors.
0
 
ratnaprasaddAuthor Commented:
Thank You Your solution was excellent .
0
 
etudeCommented:
Thank you, Datamonkey, for an excellent debugging tool!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now