Execute scripts by relative path in Oracle SQL Developer

Question

First, this question relates to Oracle SQL Developer 3.2, not SQL*Plus or iSQL, etc. I've done a bunch of searching but haven't found a straight answer.

I have several collections of scripts that I'm trying to automate (and btw, my SQL experience is pretty basic and mostly MS-based). The trouble I'm having is executing them by a relative path. for example, assume this setup:

scripts/A/runAll.sql
       | /A1.sql
       | /A2.sql
       |
       /B/runAll.sql
         /B1.sql
         /B2.sql

I would like to have a file scripts/runEverything.sql something like this:

@@/A/runAll.sql
@@/B/runAll.sql

scripts/A/runAll.sql:

@@/A1.sql
@@/A2.sql

where "@@", I gather, means relative path in SQL*Plus.

I've fooled around with making variables but without much luck. I have been able to do something similar using '&1' and passing in the root directory. I.e.: scripts/runEverything.sql:

@'&1/A/runAll.sql' '&1/A'
@'&1/B/runAll.sql' '&1/B'

and call it by executing this:

@'c:/.../scripts/runEverything.sql' 'c:/.../scripts'

But the problem here has been that B/runAll.sql gets called with the path: c:/.../scripts/A/B.

So, is it possible with SQL Developer to make nested calls, and how?

Answer 1

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.

   Enter the default path to look for scripts as the active working directory:

   "${file.dir}"

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:

   @A1.sql;

   @A2.sql;

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.

Through the act of navigating and opening the runAll.sql worksheet, the default file folder becomes "script".