Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic spool file in SQL*Plus ?

Re: Dynamic spool file in SQL*Plus ?

From: <Christopher.Jones_at_oracle.comX>
Date: 12 Mar 2002 17:00:57 +1100
Message-ID: <uvgc2ba06.fsf@oracle.comX>

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:

  1. Passing it from the command line to a script

         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 Jones
Received on Tue Mar 12 2002 - 00:00:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US