Re: dynamic SPOOL filename

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 20 Jun 2002 13:11:49 -0700
Message-ID: <aetcu5020u8@drn.newsguy.com>

In article <3d122e59$0$231$626a54ce_at_news.free.fr>, "Antoine says...
>
>Hello,
>
>I'd like to store the name of the spool file in a variable.
>
>For instance, rather than :
> set echo off newpage 0 space 0 pagesize 0 feed off head off
>trimspool on
> spool oradata.txt
> select col1 || ',' || col2 || ',' || col3
> from tab1
> where col2 = 'XYZ';
> spool off
>
>I want this kind of thing :
>filename:='oradata.txt';
>spool filename
>
>But, obviously, i guess that if i do this thing, oracle will store its
>results
>in a file called 'filename', not 'oradata.txt'.
>
>Thx in advance and sorry for my pathetic english.
>
>Antoine
>
>

define filename=oradata.txt
spool &filename

If you want to dynamically generate that from a table or date or something:

ops$tkyte_at_ORA8I.WORLD> column x new_val filename ops$tkyte_at_ORA8I.WORLD> select to_char(sysdate,'yyyymmddhh24miss')||'.txt' X from dual;

X

---

20020620161700.txt

ops$tkyte_at_ORA8I.WORLD> spool &filename
ops$tkyte_at_ORA8I.WORLD> select * from dual;

D
-
X

ops$tkyte_at_ORA8I.WORLD> spool off
ops$tkyte_at_ORA8I.WORLD> !cat &filename
ops$tkyte_at_ORA8I.WORLD> select * from dual;

D
-
X

ops$tkyte_at_ORA8I.WORLD> spool off

ops$tkyte_at_ORA8I.WORLD> !ls -l &filename
-rw-r--r-- 1 tkyte 84 Jun 20 16:17 20020620161700.txt

ops$tkyte_at_ORA8I.WORLD>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jun 20 2002 - 15:11:49 CDT