?
Solved

Problem when spooling DBMS_OUTPUT.PUT_LINE result

Posted on 2004-09-13
12
Medium Priority
?
3,921 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 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