Solved

SQL Reports - spool command

Posted on 2001-06-25
4
667 Views
Last Modified: 2012-06-27
Hi there !!!

I am using the reporting feature of SQL. I am giving the following commands for printing my report :

SET LINESIZE 47
TTITLE CENTER "Employee Report"
COLUMN empno FORMAT 9999 HEADING "Empno"
COLUMN ename FORMAT A15 HEADING "Emp. Name"
SPOOL emp_rep
SELECT empno, ename FROM emp WHERE deptno = 20;
SPOOL OFF

Now when I print the file EMP_REP.LST from DOS prompt or Notepad, the following select command is printed in the beginning of report :

"SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 20"

and the following line is printed at the end of the report :

"51 ROWS SELECTED"

I dont want these lines at the beginning and the end of my output file. So please tell me what should I do to avoid these lines in the output file.

Thanking you,

Manish.
0
Comment
Question by:manish_saraswat
4 Comments
 
LVL 1

Expert Comment

by:hemlatha_rk
ID: 6223884
Try 'set feedback off'.

Best wishes.
0
 

Expert Comment

by:sampathi
ID: 6223894
The last line which gives the count can be avoided by

SET FEEDBACK OFF before the spool.
0
 

Author Comment

by:manish_saraswat
ID: 6224012
I have tried that option but by giving SET FEEDBACK OFF only the line "51 ROWS SELECTED" is removed. Still the line
"SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 20"
is there at the beginning of the output file and

"SPOOL OFF"

at then end of the output file remains. What should I do to remove them ?

Please give a complete answer.

Manish.
0
 

Accepted Solution

by:
nikhilnk earned 60 total points
ID: 6224130
Dear Manish,

To create report file from sql+ you need to first create a text file like below. Save this file as file1.txt or file1.sql.
From SQLPrompt run this script, if required you need to give full path of script file. ie.
SQL >@file1.txt  OR
SQL >@c:\reports\file1.sql

SET LINESIZE 47
set feedback off
set echo off
set termout off
TTITLE CENTER "Employee Report"
COLUMN empno FORMAT 9999 HEADING "Empno"
COLUMN ename FORMAT A15 HEADING "Emp. Name"
SPOOL emp_rep
SELECT empno, ename FROM emp WHERE deptno = 20;
SPOOL OFF
set feedback on
set echo on
set termout on

It will give output like below :

                Employee Report                
Empno Emp. Name                                
----- ---------------                          
 7369 SMITH                                    
 7566 JONES                                    
 7788 SCOTT                                    
 7876 ADAMS                                    
 7902 FORD                                    

Ok,
bye,
NK
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now