Append date to sql*plus spool (log) file in shell script

SQL*Plus version : 11.2.0.4
OS : Oracle Linux 6.5

SQL*Plus is a client application to connect to oracle database. The log file for this tool is generated via spool command as shown below.

I am trying to append date ( $dateString ) to spool file as shown below.

$ cat test2.sh
#!/bin/bash

dateString=`date +%d-%b-%Y_%H%M%S`

sqlplus scott/tiger@10.82.16.214:1521/ORCL<<EOF

set time on timing on

spool /home/appusr/scripts/'$dateString'_batch.log

select sysdate from dual;

spool off

EOF
exit

But the log file generated has single quote around the date part as shown below. How can I get rid of this ?

$ ls -l
total 8
-rw-rw-r-- 1 appusr appusr 360 Mar 30 18:35 '30-Mar-2017_183552'_batch.log
-rwxr-xr-x 1 appusr appusr 224 Mar 30 18:35 test2.sh

I tried escaping the single quotes using \ character. But, still the same issue.

spool /home/appusr/scripts/\'$dateString\'_batch.log

What happens if you just drop the single quotes from the "here document"?

1 Like

Hi Rudic,
Already tried the following (no single quotes). In this case, logfile is not generated at all !

 spool /home/appusr/scripts/$dateString_batchtest3.log

Tried double quotes too ( "$dateString"_batchtest3.log )

Did you tried

spool /home/appusr/scripts/${dateString}_batchtest3.log
1 Like

Brilliant. Thank You Prasanna.
Thank You Rudic