[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem when spooling DBMS_OUTPUT.PUT_LINE result

Posted on 2004-09-13
12
Medium Priority
?
3,938 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

650 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