Re: Help, please. Unneeded variable replacement verbiage spooled to output file.

On Apr 30, 3:07 pm, Greg <esab..._at_yahoo.com> wrote:
> I'm very much a newbie at SQL*Plus. I've been working on something
> for the last couple of days that would probably be easy for most of
> you, but I would certainly appreciate any help someone will give me.
>
> I have created a query (.sql) file that I run from a batch that calls
> SQL*Plus. Rather than explain it, I have the query below:
>
> -----------------------------------------------
> connect gsabens/sample_at_pmed
> set heading off
> set trimspool on
> set newpage none
> set termout off
> set feedback off
> set pagesize 0
> set linesize 500
>
> column filename_column new_value filename
> column today_column new_value today
> column backday_column new_value backday
>
> SELECT
> to_char(sysdate,'yymmdd') || '.txt' filename_column,
> to_char(sysdate-1,'dd-MON-yy') today_column,
> case to_char(sysdate,'DY') when 'MON' then to_char(sysdate-3,'dd-MON-
> yy') else to_char(sysdate-1,'dd-MON-yy') end backday_column
> FROM dual;
> spool &filename
>
> SELECT
> x.city,
> x.state
> FROM Patientmedicalrecords x
> WHERE
> x.apptdate between '&backday' and '&today';
> spool off
> exit
> -----------------------------------------------
>
> I know that it doesn't seem useful, but I cut out 'stuff' that isn't
> pertinant to the question.
>
> Anyway, this works great, except that the file that is output contains
> two lines at the beginning that show how the variables, backday and
> today, have been replaced. I'd really rather not have that showing in
> the export file. Following is an example of the lines I'm seeing that
> I don't need in the file (starting with 'old 37:' and 'new 37:'):
>
> ----------
> old 37: and x.apptdate between '&backday' and '&today'
> new 37: and x.apptdate between '27-APR-07' and '29-APR-07'
> WEST LAFAYETTE,IN
> LAFAYETTE,IN
> LAFAYETTE,IN
> ...
> ...
> ...
> ... and so on ...
> ----------
>
> Is there an SQL*Plus setting that I can change so that such verbiage
> isn't spooled? Or, are their other options that I haven't learned yet
> that would make setting the dates in the second query's WHERE clause
> easier?
>
> Again, thank you in advance to anyone who helps!
>
> Greg

set verify off

David Fitzjarrell Received on Mon Apr 30 2007 - 15:27:38 CDT