Want to run sql script (written in window batch file) on oracle DB

Question

Snippet of Batch file

:: This setlocal is done to use variable syntax '!varname!' in nested for loop
setlocal  enableextensions enabledelayedexpansion
echo on
SET AUTOMATION_ACCOUNT_ID = some value
SET DB_SERVER_NAME = some value
SET DB_NAME = some value
SET all remaining variables

::AUTOMATION_ACCOUNT_CHECK
:: Checking if Automation Account is already created on DB (If not then create it first & then move to FREE_TRAIL_ACCOUNT_CHECK process)*

FOR /F "usebackq tokens=1" %%i IN (`sqlcmd -S %DB_SERVER_NAME% -h -1 -d %DB_NAME% -U %DB_SERVER_USERNAME% -P %DB_SERVER_PASSWORD% -Q"set nocount on; select (count(*)) from dbo.[ACCOUNTS] where ACCOUNT_ID ='%AUTOMATION_ACCOUNT_ID%';"`) DO set ACCOUNTCOUNT=%%i

echo "ACCOUNT COUNT is "!ACCOUNTCOUNT!
if !ACCOUNTCOUNT!==0 goto CREATE_AUTOMATION_ACCOUNT:
goto FREE_TRAIL_ACCOUNT_CHECK:`

I want above script to be executed on oracle instance .

Through googling I came to know that sqlcmd won't work for oracle DB , i have to use sqlplus instead . Using sqlplus , I converted above script as follows

::AUTOMATION_ACCOUNT_CHECK
:: Checking if Automation Account is already created on DB (If not then create it first & then move to FREE_TRAIL_ACCOUNT_CHECK process)

 FOR /F "usebackq tokens=1" %%i IN ('sqlplus            
 %DB_SERVER_USERNAME%/%DB_SERVER_PASSWORD%@%DB_SERVER_NAME% @sqlscript.sql') DO @set ACCOUNTCOUNT=%%i
echo "ACCOUNT COUNT is "!ACCOUNTCOUNT!

where sqlscript.sql contains

SELECT COUNT(*) from ACCOUNTS; exit;

However this is not working as I am getting following response in command prompt when trying to execute this batch file

 C:\Setup>FOR /F "usebackq tokens=1" %i IN ('sqlplus uname/pwd@host:port @sqlscript.sql') DO @set ACCOUNTCOUNT=%i 

 C:\Setup>echo "ACCOUNT COUNT is "!ACCOUNTCOUNT! 
 "ACCOUNT COUNT is "sqlplus
 C:\Setup>exit /B 0

I am geting this 'sqlplus' printed instead of Account count ... Can you guys help me to resolve this stuff ?

Moreover when I execute same stuff on sqlplus utility , it works

'Not working' isn't very helpful. What output do you get? In the code you've show there is no semicolon after your query; is that the case in your real script? If so nothing will be executed; but it would also appear to hang as it wouldn't recognise the exit as a separate command. — Alex Poole
– Alex Poole, Commented Sep 1, 2016 at 10:50
Thanks Alex for pointing it out . I have edited question with command prompt response ... — sjethvani
– sjethvani, Commented Sep 1, 2016 at 10:59
You'd probably find it useful to display the whole result; can't tell if it's echoing the command, or saying it can't find the sqlplus command, or something else. (You might also want to add the /s flag to hide the SQL*Plus banner, and your script might need some set commands to hide headings and just show the query result with no distractions; but those aren't the immediate problems) — Alex Poole
– Alex Poole, Commented Sep 1, 2016 at 11:09
Above command is echoed as I have set echo on in first line (question updated) . And yeah , I do have sqlplus utility installed on my machine — sjethvani
– sjethvani, Commented Sep 1, 2016 at 11:14

jeb · Accepted Answer · 2016-09-01 11:59:33Z

1

Using usebackq in the FOR loop options means you should use back quotes, when you want to execute a command, else you use it as a string.

FOR /F "usebackq tokens=1" %%i IN (`sqlplus ...

The SET statements in the beginning of your code will not work as expected.

SET DB_NAME = some value

Will create a variable named DB_NAME<space> so it can't be accessed with %DB_NAME%.
you should avoid spaces when using SET

edited Sep 1, 2016 at 11:59

answered Sep 1, 2016 at 11:45

jeb

83.2k20 gold badges181 silver badges237 bronze badges

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

Comments

Alex Poole · Accepted Answer · 2016-09-01 11:45:21Z

The main problem you have is that you're specifying userbackq but you've then used normal straight quotes around the sqlplus call. You also need to add the -s (silent) flag to suppress the SQL*Plus banner:

FOR /F "usebackq tokens=1" %%i IN (
`sqlplus -s %DB_SERVER_USERNAME%/%DB_SERVER_PASSWORD%@%DB_SERVER_NAME% @sqlscript.sql`
) DO @set ACCOUNTCOUNT=%%i

With those changes you'll see something like:

"ACCOUNT COUNT is "53

So you probably want to remove the double quotes from your echo command too (and hide the commands with @, but I assume you're displaying them for debugging purposes at the moment):

@echo ACCOUNT COUNT is !ACCOUNTCOUNT!

Thanks for your answer , I am not getting expected answer C:\UserCreationSetup>FOR /F "usebackq tokens=1" %i IN (sqlplus -s uname/pwd@hostmachine:port @sqlscript.sql) DO @set ACCOUNTCOUNT=%i C:\UserCreationSetup>echo ACCOUNT COUNT is !ACCOUNTCOUNT! ACCOUNT COUNT is 5 C:\UserCreationSetup>exit /B 0

Collectives™ on Stack Overflow

Want to run sql script (written in window batch file) on oracle DB

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related