General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Simon Qu
Frequent Advisor

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

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.
9 REPLIES
Brian Crabtree
Honored Contributor

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

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
Honored Contributor

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

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

Yogeeraj_1
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Simon Qu
Frequent Advisor

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

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
Frequent Advisor

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

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

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor
Solution

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

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Yogeeraj_1
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Simon Qu
Frequent Advisor

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

It works fine. Thank you all for help.