ratnaprasadd
asked on
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.
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.
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;
/
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You Your solution was excellent .
Thank you, Datamonkey, for an excellent debugging tool!
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