Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Call Oracle Stored Procedure with Parameters from Windows Batch File

spalato76Jul 6 2011 — edited Jul 6 2011
Hi,

I have an oracle procedure that requires two parameters to execute, start date and end date as such:

CREATE OR REPLACE PROCEDURE insert_orders(
pSTART_DT IN varchar2
, pEND_DT IN varchar2
)

I want to create a windows batch file to execute the procedure but want to be able to specify the parameters (ie start and end dates) in the batch file as opposed to changing the sql file that the batch file uses to execute the procedure but I don't know what the syntax is. I tried the following but it still doesn't work.

Sql File: call_insert_orders.sql

execute insert_orders('&1','&2');
exit

Batch File:
sqlplus username/password @call_insert_orders.sql %01-jan-2010% %01-jan-2011%

When I execute the batch file, my DOS window still prompts me to enter value 1 so I think it recognizes that there is a variable being used but is not able to fill in the actual value I specify. I'm not an experienced DOS/Windows Batch File person so I'm guessing it's my syntax that's screwed up. There is not a lot of documentation on this subject matter hence my post on this forum. Any helps would be appreciated.

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2011
Added on Jul 6 2011
2 comments
2,277 views