CODEWITHSUNDEEP
sqloracle-databasebatch-filestored-procedures
Vincent Vance
Vincent Vance

Reputation: 117

Pass EXEC command as a variable into .sql

CURRENT STATE: I have a batch script (mybatch.bat) that calls a stored procedure via a .sql file (file.sql) and currently passes three variables to it - the stored procedure and two parameters. The .sql currently outputs a variable based on this stored procedure.:

file.sql

set serveroutput on
variable out_val varchar2;
exec &1('&2', '&3', :out_val);
print out_val
exit

mybatch.bat

set procedure=%1
set param1=%2
set param2=%3
set strYN = ' '

rem ** This line stores out_val value Y or N as strYN.
for /F "usebackq" %%i in (`sqlplus database/pw@user @"file.sql" %procedure% %param1% %param2%`) do (
    set stryn=%%i
    if /I "!strYN!"=="N" (goto:nextN) else (if /I "!strYN!"=="Y" goto:nextY)
)

PROBLEM: I want to be able to call other stored procedures from this same .sql file that might not have output variables (out_val). So I'd like to be able to concatenate the execution line within my batch and pass it as a variable to the .sql

FUTURE STATE: I'd like to be able to pass the entire command into my .sql as a variable like so:

file.sql

set serveroutput on
variable out_val varchar2;
exec &1
print out_val
exit

mybatch.bat

set procedure=%1
set param1=%2
set param2=%3
set strYN = ' '
set command=%procedure%('%param1%', '%param2%', :out_val);

rem ** This line stores out_val value Y or N as strYN.
for /F "usebackq" %%i in (`sqlplus database/pw@user @"file.sql" %command%`) do (
    set stryn=%%i
    if /I "!strYN!"=="N" (goto:nextN) else (if /I "!strYN!"=="Y" goto:nextY)
)

Is this possible? Or can .sql variables only be used as parameters? Perhaps there's a sql function that accepts variables as a line to be executed?

Upvotes: 0

Views: 963

Answers (1)

Vincent Vance
Vincent Vance

Reputation: 117

Turns out the issue was regarding the semicolon at the end of my %command% variable's value. I removed the semicolon from the value of the variable and added it to the end of the exec command in the .sql file. I also wrapped the %command% parameter pass in quotes because the variable contained spaces.

file.sql

set serveroutput on
variable out_val varchar2;
exec &1;
print out_val
exit

mybatch.bat

set procedure=%1
set param1=%2
set param2=%3
set strYN = ' '
set command=%procedure%('%param1%', '%param2%', :out_val)

rem ** This line stores out_val value Y or N as strYN.
for /F "usebackq" %%i in (`sqlplus database/pw@user @"file.sql" "%command%"`) do (
    set stryn=%%i
    if /I "!strYN!"=="N" (goto:nextN) else (if /I "!strYN!"=="Y" goto:nextY)
)

Upvotes: 1

Related Questions