[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Reports - spool command

Posted on 2001-06-25
4
Medium Priority
?
686 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 240 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

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.
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month19 days, 4 hours left to enroll

834 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