Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic spool file in SQL*Plus ?
Jarek Palka <jpalka_at_interia.KILLSPAM.pl> writes:
> Is it possible to set spool filename dynamic based on return value of
> SELECT statement ?
>
> I'd like to set spool filename the same as username to which I'm connecting:
>
> Such statement don't work :-((
>
> SELECT substr(user,1,8)||'.txt' INTO :uname FROM DUAL;
> SPOOL :uname
> -- script body
> SPOOL off
> --
Instead of a bind variable, use a substitution variable in the SPOOL command: "SPOOL &MYVAR".
You can put a value in the variable by one of these methods:
sqlplus scott/tiger inv270202.txt @myscript.sql
where myscript.sql contains
spool &1
2. Or by setting the variable explicitly
define myvar = "inv270202.txt" spool &myvar
3. Or by using an item returned by a query
column dn new_value mynv noprint select 'inv'||to_char(SYSDATE,'DDMMYY')||'-'||deptno dn from dept where loc = 'BOSTON'; prompt My variable is: &mynv -- Note the double period in the SPOOL command since -- mynv does not already have the file extension spool &mynv..txt ...rest of script...
4. Or by returning it from a PL/SQL procedure
variable mypv varchar2(20)
begin :mypv := 'inv270202.txt'; end; / column dn new_value mynv noprint select :mypv dn from dual; spool &mynv spool
-- Christopher JonesReceived on Tue Mar 12 2002 - 00:00:57 CST