Solved

Oracle SQLPLUS command to execute multiple Scripts in a folder

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select unique values from two columns 4 80
return value in based on value passed 6 37
scheduler notification 9 38
SQL Syntax Question 9 22
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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