Solved

Oracle SQLPLUS command to execute multiple Scripts in a folder

Posted on 2013-12-19
6
3,899 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 37

Accepted Solution

by:
Geert Gruwez earned 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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