Can operating system variables be used in spool file naming within SQL?

[Dave Walley]

Hi.

This is related to my earlier request for sysdate values in spool file names. Can an operating system variable be used for the location of a spool file within sql.

e.g export SCRDIR=/usr/app/oracle

sql> spool $SCRDIR/spoolfile_name

output file should be in /usr/app/oracle/spoolfile_name

Thanks once again

Dave

[Chris Wilshaw]

You could try to do this by redirection on the command line

eg:

NAME=Chris

sql $DATABASE < EOF
select * from address where name = '$NAME'
EOF

I know that this works for an Ingres database, so I'd expect that Oracle can cope with it too.

[Jean-Luc Oudart]

echo spoolfilename | sql / @

and in

accept v_spoolfilename

spool &v_spoolfilename
...
sql code
...
spool off

Regards
Jean-Luc

[Brian Crabtree]

export CRUSER=brian
export PASS=crabtree
sqlplus user/pass <> $ORACLE_SID.log
create user \$CRUSER identified by \$PASS;
grant user connect to \$CRUSER;
exit;
!

This really only works inside of a shell script. Otherwise, you can pass the variables in the command line, and access them with &1 and &2.

Thanks,

Brian