1

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.

codeforester
  • 39,467
  • 16
  • 112
  • 140
Murugesh Anand
  • 779
  • 1
  • 6
  • 5

1 Answers1

1

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

Community
  • 1
  • 1
codeforester
  • 39,467
  • 16
  • 112
  • 140
  • Thanks @codeforester for super quick response!!! Ive tried the steps suggested but still getting the error. spoolFile="SPOOL_FILE_"$date".txt" output1=`sqlplus -s username@SID/pwd < – Murugesh Anand Feb 02 '17 at 06:29
  • Is "EOF" at the very beginning of the line and there are no other characters on that line? What is the error you are getting? – codeforester Feb 02 '17 at 06:31
  • Its at the end of the line like -- output1=`sqlplus -s username@SID/pwd < – Murugesh Anand Feb 02 '17 at 08:52
  • Thank you @codeforester !!! It worked. I just re-wrote the code in the similar way mentioned by you and it worked. Thanks a lot !!! – Murugesh Anand Feb 12 '17 at 10:37