Execute Multiple Files in Oracle at time

There was certain times when you have 100s and 1000s of files ad you wanted to execute it either sequentially or randomly. If you gonna open and execute one by one will take more attentions and manually interventions.

In order to do it by some easy steps then please follow the below instruction.

Copy all the files and merge it into one file.

• Open the CMD.exe in windows (Windows +R > Type cmd)
• Navigate to your folders (Shift+right click > open command window here)
• If you have files with some specific names or with some specific file extensions then use below commands.
• copy *.log All.sql (Here I'm assuming we have files with .log extensions and we will be merge it into All.sql file. Please update as per your convenience.)

It will now create a files with All.SQL extension and contains all the details that you have in individual files.

Now we need to setup the process in oracle SQL developer , so that it will run our all files one by one.
 This will require two components as below.wn voteaccepted

This approach has two components:

-Set-up the active SQL Developer worksheet's folder as the default directory.

 -Open a driver script, e.g. runAll.sql, (which then changes the default directory to active directory), and use relative paths within the runAll.sql script to call sibling scripts.

1. Set-up your scripts default folder. On the SQL Developer toolbar, Use this navigation:

Tools > Preferences

In the preference dialog box, navigate to Database > Worksheet > Select default path to look for scripts

2. Create a script file and place all scripts associated in it:

runAll.sql

A1.sql

A2.sql

The content of runAll.sql would include:

${file.dir}  //This is identify the default directory and it will  look for scripts as the active working directory:

set define off; // It will allow you to set the prompt ignore option true and will prompt .

@A1.sql; // File names

@A2.sql; // Another file name.

To test this approach, in SQL Developer, click on File and navigate and open the script\runAll.sql file.

Next, select all (on the worksheet), and execute.

Hope this will help you to automate the multiple files execution without manual intervention

Happy Reading !!!

.