Using a batch file to loop through Oracle SQL scripts

Question

I have two SQL scripts which get called within a loop that accept a number parameter. Here is what I'm 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 to connect twice for each pass through. Is there a way I could simply log into sqlplus, run the for loop, then exit? I tried many alternatives such as putting

sqlplus u/p@name

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

Thank you.

Brian Schmitt · Accepted Answer · 2008-11-24 17:22:14Z

3

You can create a masterscript.sql that contains your two script.sql statements. The only thing I'm not sure about is if it will pass in your variables.

MasterScript.sql would contain:

@@script1.sql
@@script2.sql

and neither of your sub-scripts should contain an exit.

Differences between "@" and "@@":

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

edited Nov 24, 2008 at 17:22

answered Nov 24, 2008 at 17:13

Brian Schmitt

6,0581 gold badge27 silver badges36 bronze badges

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

Comments

DntFrgtDSemiCln · Accepted Answer · 2013-03-20 13:54:53Z

0

Try This

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

answered Mar 20, 2013 at 13:54

DntFrgtDSemiCln

1,2892 gold badges18 silver badges35 bronze badges

Collectives™ on Stack Overflow

Using a batch file to loop through Oracle SQL scripts

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related