Home » SQL & PL/SQL » SQL & PL/SQL » Preventing sql statement echo at end of spool file
Preventing sql statement echo at end of spool file [message #232177] Thu, 19 April 2007 13:48 Go to next message
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.
Re: Preventing sql statement echo at end of spool file [message #232179 is a reply to message #232177] Thu, 19 April 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is 2 ";" at the end of statement.
The second one displays the last statement.
Remove it.

Regards
Michel
Re: Preventing sql statement echo at end of spool file [message #232184 is a reply to message #232177] Thu, 19 April 2007 14:15 Go to previous messageGo to next message
Gast
Messages: 2
Registered: April 2007
Junior Member
Heh, that was it. Thanks.
Re: Preventing sql statement echo at end of spool file [message #232194 is a reply to message #232184] Thu, 19 April 2007 14:48 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
PS probably you want to give this one:
to_date(reportdatetime)

a date format with it...
Previous Topic: Formatting pl/sql output
Next Topic: regular expressions
Goto Forum:
  


Current Time: Wed Dec 07 05:16:58 CST 2016

Total time taken to generate the page: 0.15663 seconds