Re: sqlplus question

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Wed, 17 Nov 1999 21:54:37 GMT
Message-ID: <01bf3155$d1c82800$a504fa80@mndnet>

Hi,

Here is a script that you can modify to suit your needs:

REM

REM     Author     : Suresh N. Bhat
REM     DATE       : 10/03/94
REM     File Name  : sql_dated_spool_name.sql
REM     Usage      : On sqlplus prompt enter:
REM                     @sql_dated_spool_name
REM     Description: Creates a variable file name from Oracle date
functions
REM                  to which the output from SQL*Plus script is spooled.

REM
set heading off feedback off verify off pause off column year newline
rem
rem Create temporary file to define MONTH and YEAR variables. rem
spool suresh.sql
select 'define MONTH ='||lpad((to_char(sysdate,'mm')),2,'0'),

       'define YEAR ='||lpad((to_char(sysdate,'yy')),2,'0') year from dual;
spool off
set heading on feedback on verify on pause on rem

rem     Start the temporary file so that defines can be used in the
rem     spool command below.

rem
start suresh.sql
rem

rem     The first period(.) below signifies end of define MONTH variable.
rem     The second period(.) below is the dot(.) between the file name
rem     and its extention.

rem
spool spec_&MONTH..&YEAR
select sysdate from dual;
spool off
host rm suresh.sql
exit

Later !!!

Suresh Bhat
Oracleguru
www.oracleguru.net

GreyWolf_69 <GreyWolf_69_at_alloymail.com> wrote in article <3832FCAD.110D8512_at_alloymail.com>...
> Hi
> I have a sql script that is ran from win98 and out puts a report. I
> want to use a vairablie to set the the month. I then want to use this
> vairable in the spool command to include the month in the file name.
>
> What is the syntax? I've tried a hunder different ways but it doesn't
> work right. I can get the value of the varibale in the file name but I
> end up with the ' marks in the file name. Any help would be great.
>
> Thanks,
>
> Wolf
>
>
Received on Wed Nov 17 1999 - 15:54:37 CST