Re: Spooling to a dynamic file name with SQL*Plus

"dramos" <dan.ramos_at_gartner.com> wrote in message news:641f8b7.0106291233.50a9236d_at_posting.google.com...
> I am brand new to using Oracle and SQL*Plus. I have inherited some
> reports that are written for SQL*Plus. The output of the reports are
> now spooled to hardcoded file name. I need to be able to generate a
> file name that includes the date the report was run. I wrote the
> following lines of code to generate a file name and to send the report
> to the file.
>
> VARIABLE TST CHAR(50)
> SELECT 'CORE_TOPIC_REPORT_' || TO_CHAR(sysdate,'MM-DD-YYYY') || '.DOC'
> INTO :TST FROM dual;
> SPOOL TST;
>
> I have verified that the variable TST does indeed get the correct file
> name but the SPOOL command seems to treat TST as string rather than a
> variable. Am I doing something incorrectly? Any suggestions?
> Thanks in advance.

Here is a tric that works
column dynfil new_value filnam
 SELECT 'CORE_TOPIC_REPORT_' || TO_CHAR(sysdate,'MM-D-YYYY') || '.DOC' dynfil
FROM dual;
spool &filnam

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Jul 22 2001 - 08:54:26 CEST