?
Solved

SQL Reports - spool command

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 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