Solved

SQL Reports - spool command

Posted on 2001-06-25
4
673 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net - CSV to Oracle table 4 67
Concat multi row values of a field in oracle 6 63
dates - loop 12 65
error in my cursor 5 27
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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, 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.

776 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