I have ksh script which is calling a SQL script called report.sql.
$ORACLE_HOME/bin/sqlplus swb/swb4gdeprod@gdeprod @$reportHome/report.sql
Currently this SQL script is fetching data for sysdate from the database.
I want to pass a date as input to the SQL script so that it picks data corresponding to that date instead of sysdate.
How can I pass a date from ksh to SQL*Plus?
With substitution variables; start is equivalent to @ here.
You haven't shown the SQL your report contains, but assuming at some point you have something like:
where some_col > trunc(sysdate)
you would change that to:
where some_col > to_date('&1', 'YYYY-MM-DD')
and then pass a date string in the same format on the command line:
$ORACLE_HOME/bin/sqlplus swb/swb4gdeprod@gdeprod @$reportHome/report.sql 2016-07-27
The value you pass in, e.g. 2016-07-27, is the first positional parameter so it can be referenced as a substitution variable using &1. As it's a string that has to be enclosed in single quotes when you reference it in SQL. And because it's a string you have to convert it to a date. The format of the string you pass in has to match the format you specify with the to_date() function.
Also be wary of passing in month names as you may hit language issues at some point, and ideally you'd use a format that is unambiguous. I've used the ISO format YYYY-MM-DD, and if you stick with that you could also use a date literal as the substitution will be done by SQL*Plus before it is interpreted by the SQL engine:
where some_col > date '&1'