Variables in Batch Files – Using with Oracle SQL Scripts

When writing SQL scripts it is often a requirement to pass in variables like passwords and usernames so that the script is generic and can be used across multiple environments. The same is true when using Windows command files. However, the difference between the two is that you can use 10 or more in SQL but if you use the variable %10 in a command file it treats it like a %1 with a zero on the end. This can cause a lot of confusion…

So, a way around this is to pass in one long string with a delimiter used to separate out the different variable values. An example of this can be seen below.

This first line is how you would call the finished harness file with the list of variables you want to pass to it separated by semi-colons.


The next set of commands is what you need to put inside of the batch file, named “harness.cmd” in our example here.

set extrapass=%1
for /F “tokens=1-12 delims=:” %%i in (“%extrapass%”) do (
set syspwd=%%i
set dbsid=%%j
set user1pwd=%%k
set user2pwd=%%l
set user3pwd=%%m
set user4pwd=%%n
set user5pwd=%%o
set user6pwd=%%p
set user6pwd=%%q
set user7pwd=%%r
set user8pwd=%%s
set user9pwd=%%t

To use any of the variables within the Windows batch file reference %syspwd%, %dbsid%, user1pwd etc to get the variable. This means you can have a very long list of variables which can be passed to other SQL files or command files.

Like it, share it...

Category: Windows

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *