Using bind variables for dynamic filename

[frasernm]

Hi,

Using sql*plus I'd like to create the filename that I'm spooling to dynamically using bind variables - is this possible?

Thanks in advance

Fraser

 

[cfsenorin]

Yup, you can use bind variables.

Declare the bind variable like this:
SQL> VARIABLE var1 VARCHAR2

Initial the bind variable:
SQL> BEGIN
2 :var1 := 'file.txt';
3 END;
4 /

To use the bind variable in spool use this:
SQL> spool &var1

Then to stop spooling
SQL> spool off

Is this what you mean?

 

[cfsenorin]

my mistake,

the declaration should be
SQL> VARIABLE var1 VARCHAR2(20)

 

[frasernm]

Hi,

The above prompts me for a filename, and using :var1 sets this literally as the filename.

I'm wanting to use the values of variables already set eg :startDate := '12-SEP-2001';

to set a filename as "sometext120901.txt"

Fraser

 

[sem]

Fsenorin, you've probably mixed up so called substituting variable with bind variable. They are quite different. I'm not sure which one is asked about but your advice is not correct: just try.

To spool to the file you may spool its name to some command file and then run this file. Something like

set heading off
set serverout off
VARIABLE var1 VARCHAR2(200)
VARIABLE file VARCHAR2(100)
exec :file := 'text.txt'

exec :var1 := 'spool '||:file||chr(10)||'@your_report'||chr(10)||'spool off';
spool cmd.sql
print var1
spool off
@cmd

If you mean substituting variable - just pass it to your script, spooling to &&1.

 

[cfsenorin]

try use DEFINE like:

DEFINE var1 = 'sometext.txt'

SPOOL @var1

Maybe others have the solution to add the date.

 

[cfsenorin]

SEM, thanks for the correction, your solution works.