Oracle SQLPLUS command to execute multiple Scripts in a folder

Hi,
I am trying to use Automation to Execute all Scripts in a folder using SQL plus VIA batch Script.
Is it posisble to execute all in a folder with out creating a single file which as ref to all other scripts.

We are using SVN to get all files from source control but creating another file to put all into a single file is an extra task.

Any idea?
sunilbainsAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
use a dir with /b to find all files
add them to a text file
run the new text file

replace the %cd% with the directory you want
it's possible you need to replace %%~fG with %scriptdir%\%%G

set scriptdir=%cd%

set exec_script=all_scripts.sql.x

echo.--script start >%exec_script%
for /F %%G in ('dir /b %scriptdir%\*.sql') do (
  echo.@%%~fG >>%exec_script%
)
echo.--script end >>%exec_script%
echo.exit >>%exec_script%

%oracle_home%\bin\sqlplus -L -S user/password@database @%exec_script%

Open in new window

0
 
DavidSenior Oracle Database AdministratorCommented:
Take your SVN STDOUT and pipe the result set into a DOS FOR loop, example:

C:\> FOR %i in (1 2 3) DO mySQL.bat

where mySQL.bat has the usual batch coding like:

%ORACLE_HOME%/bin/sqlplus usr/pwd <<ENDOFFILE
xxxxx
....
EXIT
ENDOFFILE

You might also search the E-E knowledgebase for examples.  One from SQL Server but perhaps a good template for the loop: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_28319844.html
0
 
sunilbainsAuthor Commented:
But in this case where are you specifying %i in your batch file.

And 1 2 3  are folder or Scripts name?
0
 
DavidSenior Oracle Database AdministratorCommented:
In the example, %1 is simply a variable, taking on the string value represented by the series shown as 1 2 3.  IOW, "for %i in (a.sql b.sql kinggeorgethesecond.sql)...".  I do not know offhand if one can include pathnames, but if not the SQLPATH environment variable is available.

The inner loop, then, might look something like:
...
%ORACLE_HOME%/bin/sqlplus -nolog <EOF
usr/pwd
@%1
exit
EOF
....
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.