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 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';
b.initial_process_date_time as a date, and converting run_date into an Oracle date for the comparison. Particularly if the column is indexed as the to_char will prevent the index being used. Of course, as there's a time component you'd need to do it as a range, e.g. where b.initial_process_date_time >= to_date('&run_date','DD/MM/YYYY') and b.initial_process_date_time < to_date('&run_date','DD/MM/YYYY') + 1.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