Solved

Problem when spooling DBMS_OUTPUT.PUT_LINE result

Posted on 2004-09-13
12
3,894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 150 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

690 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