Launch PL/SQL script from batch file (with arguments)

Question

I'm strugling at passing arguments to a PL/SQL script from a Windows .bat file.

Here is the content of the batch file :

@echo off
set mName = "test"
exit | sqlplus <connection_string> @test.sql %mName%
pause

And here is the content of test.sql :

set verify off
set serveroutput on size unlimited
BEGIN
  DBMS_OUTPUT.PUT_LINE('&&1');
END;
/

I would expect to see "test" appearing in the shell but instead, I get the following message :

Enter value for 1:
SP2-0546: User requested Interrupt or EOF detected.

Does anyone have a solution to this problem ?

Vincent Malgrat · Accepted Answer · 2013-12-10 15:35:04Z

3

Remove the spaces around your = sign. Here's my working batch file:

@echo off
set mName="test"
exit | sqlplus <connection_string> @test.sql %mName%
pause

Also note that you can use the -L option on sqlplus for batch jobs:

-L Attempts to log on just once, instead of reprompting on error.

answered Dec 10, 2013 at 15:07

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

What the heck, I would never have found this by myself... Thanks man !