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

Question

I am trying to pass arguments to a PL / SQL script from a Windows.bat file.

Here is the contents of the batch file:

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

And here is the contents of test.sql:

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

I expect to see a “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?

Answer 1

Remove the spaces around the sign =. Here is my working batch file:

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

Also note that you can use the -Lsqlplus option for batch jobs:

-L Trying to log in only once, instead of repurposing on error.