Pass a variable string in To_Date Oracle function in shell script

Shell Programming and Scripting
1

Hello,

I am trying to execute an SQL query from shell script.

A part of script is something like this:

fromDate=`echo $(date +"%F%T") | sed "s/-//g" | sed "s/://g"`

$ORACLE_HOME/sqlplus -s /nolog <<EOD1
connect $COSDBUID/$COSDBPWD@$COSDBSID
spool $SCMSORDERREPORT_HOME/SCMS_Order_Report.csv;
Select column1 From Table1 Where creationDate > ('$fromDate', 'YYYYMMDDHH24MISS')

spool off;
exit
EOD1

Here, I have initialized a variable fromDate in the beginning of the script, and I am trying to pass it to the SQL query. Is this the right way or I need to change the way how it is passed to query? I am not getting anything in the spooled file and I suspect there is something wrong with the way the parameter has been passed.
Please help!

2

No need to use those echo and sed , you can simply do:

fromDate=$(date +%Y%m%d%H%M%S)

or simply do it in the SQL itself using sysdate instead.

3

Thanks for the reply!

Sorry for the less information. I am actually doing some more arithmetic with the fromDate and not simply passing the current timestamp.

I am just trying to get an idea whether I can pass the variable like the way I have done in the query.

creationDate > ('$fromDate', 'YYYYMMDDHH24MISS')

Will this be the correct way to pass my initialized variable?

4

Whenever in doubt, replace mydbms with cat.

It then becomes the simple question -- does this query look like what I expected?

cat <<EOD1
connect $COSDBUID/$COSDBPWD@$COSDBSID
spool $SCMSORDERREPORT_HOME/SCMS_Order_Report.csv;
Select column1 From Table1 Where creationDate > ('$(date +%Y%m%d%H%M%S)', 'YYYYMMDDHH24MISS')

spool off;
exit
EOD1
5

Thanks a lot! This helped me to verify what was wrong.

1 Like