OraFAQ Forum: SQL & PL/SQL » Preventing sql statement echo at end of spool file

Home » SQL & PL/SQL » SQL & PL/SQL » Preventing sql statement echo at end of spool file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Preventing sql statement echo at end of spool file [message #232177]

Thu, 19 April 2007 13:48

Gast
Messages: 2
Registered: April 2007

Junior Member

I'm trying to script an SQL query to collect stats to be imported into excel for easy management quality graphs. I've got everything I need but I can't figure out how to prevent the sql statement from showing up at the end of the spooled file.

Here's what a tail of my file looks like. I want the last 9 lines to not show up.

20070419144908|SA    | Information I want here
  1  select
  2     INSERTTIME
  3     ,REPORTTYPECODE
  4     ,REPORTDATA
  5   from
  6     report_archives
  7   where
  8     dupCriteria = 'FIRST_IN'
  9*    and (to_date(reportdatetime) > (sysdate - 1))

Here's my SQL script

alter session set nls_date_format = 'YYYYMMDDHH24MISS';

set colsep '|'
set echo off
set feedback off
set heading off
set linesize 300
set long 32767
set newpage none
set pagesize 0
set termout off
set trimspool on
set Verify off
set wrap off

col INSERTTIME format 99999999999999
col REPORTDATETIME format 99999999999999
col reportTypeCode format A6 heading 'RType'

col spooldest new_value v_spooldest noprint 
select 'afwed_'||to_char(sysdate,'YYYYMMDDHH24') spooldest from dual ; 
spool &&v_spooldest 

select 
	INSERTTIME
	,REPORTTYPECODE
	,REPORTDATA
 from 
	report_archives
 where 
	dupCriteria = 'FIRST_IN'
	and (to_date(reportdatetime) > (sysdate - 1)) ;
;

spool off
exit

Thanks for your help.

Report message to a moderator