PLSQL Command line parameters

I am having a testing.sql program. First list line I receive the command line argument with define run_date = '&1' and use it in cursor.

Argument I want it as string '24/02/2011' and I use it in comparison in sql queries and cursors.

Select * 
  from bill_file b 
 where to_char(b.initial_process_date_time,'DD/MM/YYYY')=&run_date;

When I call the script from sqlplus it's giving errors. Not sure how 开发者_JS百科I can receive string arguments.

Here is how I tried:

SQL> @testing.sql 24/02/2011

Thanks a lot for the info. I enclosed the run_date in single quotes and it worked. Select * from bill_file b where to_char(b.initial_process_date_time,'DD/MM/YYYY')='&run_date';

You forgot quotes @testing.sql "24/02/2011"

Without the entirety of what you did, we can't say.

Here is how it simply works (without quotes in the call, but in the to_date):

file c.sql

define run_date='&1';
select to_date('&run_date', 'DD/MM/YYYY') from dual;

Then the call:

SQL> @c.sql 24/02/2011
old   1: select to_date('&run_date', 'DD/MM/YYYY') from dual
new   1: select to_date('24/02/2011', 'DD/MM/YYYY') from dual
24/02/2011