How to save the contents from PL/SQL into a text file

[Simon Qu]

We can use DBMS_OUTPUT.PUT_LINE to display the contents from PL/SQL on the screen, but How to save the contents from PL/SQL into a text file ? Thanks.

[Brian Crabtree]

You have a few ways.

1. If you are using dbms_output.put_line, then using the 'spool' command to output to a file might be the easiest.

2. Take a look at the UTL_FILE package. This allows you to open, read, write, and append to a file on the OS level. This might be an easier solution which would let you output the information directly to a file. It should be noted that this file will then be owned by the OS user that is running the database (most likely 'oracle' and not the user running the plsql code.

Thanks,

Brian

[T G Manikandan]

SQL>spool a.txt
SQL>select * from emp;
...
..
SQL>spool off

[Yogeeraj_1]

hi,

If you are running your PL/SQL using sqlplus you can simply use: spool

Else, use UTL_FILE package inside your PL/SQL block

You need to check your database setting for that: your utl_file_dir in init.ora
or using SQLPLUS/SVRMGRL
show parameter utl_file_dir

Note that in 9i Release 2, UTL_FILE uses a "directory" object instead of the utl_file_dir init.ora parameter and these directory objects are created when needed, without touching the parameter files, without restarting.

If you need a small example, please let us know...

regards
Yogeeraj

[Simon Qu]

Here is my specific question:

set serveroutput on
declare
max_amnt float(8);
begin
select max(amnt) into max_amnt
from sales_history
where partno = '12345';

DBMS_OUTPUT.PUT_LINE ('The max amount is ' || amnt);

end;

The above pl/sql statement works fine and display 'The max amount is xxxx ' on screen.

If I try to use spool to put the output into a file, where should I put the spool and spool off ? or how can I use another way to do that ?

Thank you all.

[Simon Qu]

oops... it should be:
DBMS_OUTPUT.PUT_LINE ('The max amount is ' || max_amnt);

[Yogeeraj_1]

hi,

the spool command is a SQLPLUS command! so you should put it outside the PLSQL block of statements.

thus,

spool myfile.txt;
set serveroutput on
declare
max_amnt float(8);
begin
select max(amnt) into max_amnt
from sales_history
where partno = '12345';

DBMS_OUTPUT.PUT_LINE ('The max amount is ' || max_amnt);

end;
spool off;


cheers
Yogeeraj

[Graham Cameron_1 · Accepted solution]

Aboce from Yogeeraj is not quite right, as SQLPlus will not execute your pl/sql block until you enter a slash, and you don't need semicolons after the spool statments.

Instead, try:

set serveroutput on size 1000000 format wrap
spool myfile
declare
max_amnt float(8);
begin
select max(amnt) into max_amnt
from sales_history
where partno = '12345';

DBMS_OUTPUT.PUT_LINE ('The max amount is ' || max_amnt);

end;
/
spool off

This will create myfile.lst in the current directory. If it already existed it will have been overwritten.

-- Graham

[Yogeeraj_1]

thank you Graham for the correction.

You do need the / to execute the PL/SQL block.

As for your sqlplus environment, you may consider to default it (login.sql) as follows:
==========================================
column global_name new_value gname
set termout off
select lower(user)||'@'||global_name global_name from global_name;
set termout on
set sqlprompt '&gname> '
set timing on
set long 4000
set termout on
set underline '_'
set verify off
set feedback 5
set pagesize 500
set linesize 1000
set arraysize 11
set heading on
set serveroutput on size 1000000

regards
Yogeeraj

[Simon Qu]

It works fine. Thank you all for help.