CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Arav
Arav

Reputation: 5247

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 it's giving errors. Not sure how I can receive string arguments.

Here is how I tried:

SQL> @testing.sql 24/02/2011

Upvotes: 0

Views: 2045

Answers (3)

J. Chomel
J. Chomel

Reputation: 8395

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

Upvotes: 0

Arav
Arav

Reputation: 5247

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';

Upvotes: 2

Nazarii Bardiuk
Nazarii Bardiuk

Reputation: 4342

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

Upvotes: 1

Related Questions