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
Solved

SQL Reports - spool command

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

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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