?
Solved

Problem when spooling DBMS_OUTPUT.PUT_LINE result

Posted on 2004-09-13
12
Medium Priority
?
3,960 Views
Last Modified: 2008-01-09
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,


0
Comment
Question by:ebi168
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12046587
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12046603
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 12046775
"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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:ebi168
ID: 12046776
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
 

Author Comment

by:ebi168
ID: 12047105
Ok, then how could I find the result file under utl_file_dir. Thanks
0
 
LVL 6

Accepted Solution

by:
morphman earned 600 total points
ID: 12047172
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
 

Author Comment

by:ebi168
ID: 12047363
Sounds good. I'll give it a try and let you know.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12047682

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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12047809
ebi:

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

Expert Comment

by:MikeOM_DBA
ID: 12048004

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
 

Author Comment

by:ebi168
ID: 12049016
The trial has been successful using utl_file package. Sorry I can give up the for loop as I mentioned earlier. Thanks all
0
 

Author Comment

by:ebi168
ID: 12049038
I mean I can't give up the for loop.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 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