Clever Geek Handbook

Using a batch file to loop through Oracle SQL scripts

I have two SQL scripts that are called in a loop that take a number parameter. This is what I am currently using:

for /l %%i in (1, 1, 51) do (
    sqlplus u/p@name @script.sql a%%i.html %%i
    sqlplus u/p@name @script.sql b%%i.html %%i
)

Everything works fine, but it seems like a waste of time and resources connecting twice for each pass. Is there a way I could just log into sqlplus, start a for loop, and then exit? I have tried many alternatives such as posting

sqlplus u/p@name

before the for loop, but then it just hangs at the SQL> prompt without executing any of my two scripts.

Thank.

+1


source to share


2 answers


You can create a masterscript.sql file containing two script.sql statements. The only thing I'm not sure about is whether it will be passed in your variables.

MasterScript.sql will contain:

@@script1.sql
@@script2.sql


and none of your sub scripts should contain an output.

Differences between "@" and "@@" :

http://www.orafaq.com/wiki/SQL * Plus_FAQ # What_is_the_difference_between_.40_and_.40.40.3F

+2


source


try it



(echo @% query% user)
| sqlplus username / password @ (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = XX.XX.XX.XX) (port = 152X)) (CONNECT_DATA = (SID = ABC)))

0


source






More articles:


All Articles