[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle SQLPLUS command to execute multiple Scripts in a folder

Posted on 2013-12-19
6
Medium Priority
?
4,417 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 23

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

650 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