CODEWITHSUNDEEP
oracle-databaseshellsqlplusksh
nithin
nithin

Reputation: 73

Pass parameter from shell script to SQL script

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?

Upvotes: 1

Views: 5834

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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'

Upvotes: 2

Related Questions