CODEWITHSUNDEEP
linuxoracle-databasebashshellunix
Murugesh Anand
Murugesh Anand

Reputation: 879

Passing the name of spool file to sqlplus from a shell script

I am trying to write a unix program, in which I need to connect to the SQL DB and fetch the data and store it into a file.

Currently I am using the following command:

output1=`sqlplus -s username@SID/password <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING ON ECHO OFF;
SPOOL EMP_NAMES.txt
select emp_name from employee order by emp_name;
Spool off;

This is working fine. But my requirement was that I want to pass the value of spool file such that everytime a new Spool file would be generated.

I basically want to append the date at the end of the file name like:

date=`date +'%d/%m/%Y_%H:%M:%S:%2N'`
output1=`sqlplus -s username@SID/password <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING ON ECHO OFF;
SPOOL EMP_NAMES_$date.txt

Kindly let me know as to how this can be done.

Upvotes: 1

Views: 7697

Answers (1)

codeforester
codeforester

Reputation: 42999

If you call your sqlplus with a heredoc, you can do this easily:

spool_file=: ... your date logic here ...
sql_ouput=: ... path for sqlplus output & errors ...
sqlplus -s username@SID/password << EOF &> "$sql_output"
  SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING ON ECHO OFF;
  spool $spool_file
  # SQL statements
  spool off
EOF
if [[ $? != 0 ]]; then
  : ... error handling ...
fi
  • It's better to capture stdout/stderr of sqlplus into a file rather than a shell variable.
  • I think it is possible to hide the password by removing it from the command line and adding it as the first line of heredoc (this will prevent password from showing in ps)

Take a look at this related post: Connect to sqlplus in a shell script and run SQL scripts

Upvotes: 1

Related Questions