Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Dave Walley
Frequent Advisor

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

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
why do i do this to myself
3 REPLIES
Chris Wilshaw
Honored Contributor

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

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
Honored Contributor

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

echo spoolfilename | sql / @

and in

accept v_spoolfilename

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

Regards
Jean-Luc
fiat lux
Brian Crabtree
Honored Contributor

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

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