?
Solved

Oracle SQLPLUS command to execute multiple Scripts in a folder

Posted on 2013-12-19
6
Medium Priority
?
4,179 Views
Last Modified: 2014-06-06
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?
0
Comment
Question by:sunilbains
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:David
ID: 39729597
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
 

Author Comment

by:sunilbains
ID: 39729623
But in this case where are you specifying %i in your batch file.

And 1 2 3  are folder or Scripts name?
0
 
LVL 23

Expert Comment

by:David
ID: 39729861
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
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 39731211
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40116726
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question