?
Solved

How to redirect the output from PL/SQL program

Posted on 2003-03-08
5
Medium Priority
?
3,220 Views
Last Modified: 2007-12-19
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
Comment
Question by:ratnaprasadd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Expert Comment

by:lmedfo
ID: 8094014
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
 
LVL 15

Expert Comment

by:andrewst
ID: 8094139
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
 
LVL 2

Accepted Solution

by:
Datamonkey earned 100 total points
ID: 8097104
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
 

Author Comment

by:ratnaprasadd
ID: 8102504
Thank You Your solution was excellent .
0
 

Expert Comment

by:etude
ID: 12155789
Thank you, Datamonkey, for an excellent debugging tool!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question