How to pass parameters to sql scripts via command line

Question

There a requirement where I am trying to automate a process in my project where in a sql need to be run for daily reporting.

sql looks like below: (This is simplest form but my sql is of 400 lines,below is just an example to get the results).

test.sql

select * from table
where create_date between &date1 and &date2;

I wanted to create a bat file that calls the sqlplus and passes the dates via command prompt.Date will be calculated automatically and will be passed in the commmmand prompt itself.

I've tried the below command line :

sqlplus userid/password@db_name @test.sql DATE1 DATE2

But that still prompts me to enter the dates for date 1 and date 2 which I want that to be picked up from arguments automatically.

Could you please help me in achieving the above ?

Answer 1

The parameters that are being passed from the command line are available in SQLPLUS as &1 and &2.

select * from table
where create_date between &1 and &2;

To prevent problems with date formatting you may want to consider changing that to

select * from table
where create_date between to_date('&1','DD-MM-YYYY') and to_date('&2','DD-MM-YYYY');

Or whatever date format you want to use.