Problem when spooling DBMS_OUTPUT.PUT_LINE result

Hi,

I am trying to spool the output of DBMS_OUTPUT.PUT_LINE using the following code:

ORACLE_SID=DB1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/redo1/DB1/arch/testhotbk/
export BACKUP_DIR
. oraenv

sqlplus "/ as sysdba" <<!
spool /u03/oracle/local/output
BEGIN
for f1 in (select tablespace_name tn from dba_tablespaces)
loop
dbms_output.put_line(f1.tn);
end loop;
END;
/
spool off;
EXIT
!

But the whole thing gets spooled:
(from output.lst)

BEGIN
for f1 in (select tablespace_name tn from dba_tablespaces)
loop
dbms_output.put_line(f1.tn);
end loop;
END;
/

Can anyone tell me what is wrong and how can I just spool the content (f1.fn) put by dbms_output.

Thanks,


ebi168Asked:
Who is Participating?
 
morphmanConnect With a Mentor Commented:
You need to use UTL_FILE to write files competently from plsql.

You will need to read up a bit to use it, but its fairly straight forward. You open a file, write to it, then close the file. Its handy for this exact situation where you need to write to files from plsql rather than from sqlplus.

Here is a link with some basics.

http://www.adp-gmbh.ch/ora/plsql/utl_file.html

Cheers
Dan
0
 
seazodiacCommented:
try this:

ORACLE_SID=DB1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/redo1/DB1/arch/testhotbk/
export BACKUP_DIR
. oraenv

sqlplus "/ as sysdba" <<!
set serverout on size 1000000
spool /u03/oracle/local/output
BEGIN
for f1 in (select tablespace_name tn from dba_tablespaces)
loop
dbms_output.put_line(f1.tn);
end loop;
END;
/
spool off;
EXIT
!

0
 
seazodiacCommented:
or modify your script like this:'

ORACLE_SID=DB1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/redo1/DB1/arch/testhotbk/
export BACKUP_DIR
. oraenv

sqlplus "/ as sysdba" <<!
spool /u03/oracle/local/output
select tablespace_name tn from dba_tablespaces;
spool off;
EXIT
!

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
"Spool" and "DBMS_OUTPUT.PUT_LINE" are two different ways to write text to an ASCII file.  Usually "spool" is used in *.SQL files that are run from SQL*Plus that do not include PL\SQL stored procedures.  You can use "spool" for example in a SQL*Plus session on your Windows client to write the output from a "select..." statement to a local file on your PC from a database somewhere on a server (that may be using Unix or another O/S).

"DBMS_OUTPUT.PUT_LINE" is the way to have PL/SQL write text to an ASCII file **on the server**.  Note that before you can use DBMS_OUTPUT.PUT_LINE, your database must be configureed to support this (with either "utl_file_dir" entries in the initialization file, or with "directory" objects that have been created by a DBA).  Also note that the resulting file(s) will be on the server (or at least on drives accessible from the server's O/S) not usually on a client machine, and depending on your server's security, a client machine may or may not have access to the resulting file(s).
0
 
ebi168Author Commented:
set serveroutput on won't solve the problem and I can't change the for loop because there will be an inner loop later on. Any more clues? Thanks
0
 
ebi168Author Commented:
Ok, then how could I find the result file under utl_file_dir. Thanks
0
 
ebi168Author Commented:
Sounds good. I'll give it a try and let you know.
0
 
MikeOM_DBACommented:

Why not just do this:

ORACLE_SID=DB1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/redo1/DB1/arch/testhotbk/
export BACKUP_DIR

sqlplus -s "/ as sysdba" <<!
set ver off feed off echo off pages 0 lin 80 trims on term off
spool output
select tablespace_name tn from dba_tablespaces;
spool off;
EXIT
!
0
 
seazodiacCommented:
ebi:

I mentioned earlier on the second , alternative solution , I think it's simpler and easier.
Mike also gave out the same solution
0
 
MikeOM_DBACommented:

Sorry seazodiak, didn't notice you had the same solution.
Also, he needs the set ... options to create a 'clean' spool file with only the file names.

0
 
ebi168Author Commented:
The trial has been successful using utl_file package. Sorry I can give up the for loop as I mentioned earlier. Thanks all
0
 
ebi168Author Commented:
I mean I can't give up the for loop.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.