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?

Gupta

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 the active working 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".

Mm

Thanks for the response, Patrick. I think you're suggesting I set a specific default folder (e.g. "C:\users\me\scripts"), yes? I'm trying to avoid that so I can deliver a self-contained package to my coworkers that they can just run as is. Or is your suggestions meant to effectively add './' to the search path? If so, I tried it out (both with & without quotes) but always got the "Unable to open file:" error. Am I missing something? – Gupta

Modified my answer to convey that this approach makes the active worksheet's folder as the default folder and provides specific direction on how to then execute sibling scripts (within the same file). – Mm

To answer your question. Yes, I am setting a default folder, but the default folder is the active folder (associated with the worksheet...which we are making the driver script). This would be self-contained. – Mm

I finally had a chance to try this and it works as you say. (I think my earlier attempts at this were plagued by SQL syntax errors). So, while it doesn't let every piece at every level be fully modular, it does let the overall package stand by itself. Thanks again! – Gupta

To create runAll.sql, simply open cmd and run "dir/s *.sql > runAll.sql", then open in any text editor and remove unnecessary lines and add "@" (find regex ^ and replaceAll) to beginning of each line – Pianist

Answer 2

I don't have access to SQL Developer right now so i can't experiment with the relative paths, but with the substitution variables I believe the problem you're seeing is that the positional variables (i.e. &1) are redefined by each start or @. So after your first @runAll, the parent script sees the same &1 that the last child saw, which now includes the /A.

You can avoid that by defining your own variable in the master script:

define path=&1
@'&path/A/runAll.sql' '&path/A'
@'&path/B/runAll.sql' '&path/B'

As long as runAll.sql, and anything that runs, does not also (re-define) path this should work, and you just need to choose a unique name if there is the risk of a clash.

Again I can't verify this but I'm sure I've done exactly this in the past...

Everybody

Thanks Alex! I had just figured that out myself as well. :) So they're working now, though not a very pretty solution. – Gupta

Answer 3

you need to provide the path of the file as String , give the patch in double quote it will work

**

For Example @"C:\Users\Arpan Saini\Zions R2\Reports Statements and Notices\Patch\08312017_Patch_16.2.3.17\DB Scripts\snsp.sql";

**

Beastly

Answer 4

This is not absolute or relative path issue. It's the SQL interpreter issue, where by default it will look for files which are having .sql extention.

Please make sure to modify the file name to file_name.sql

Ex: if workspace is having file name called "A", then move the file from A to "A.sql"

Stieglitz

Answer 5

Execution of Sql

@yourPath\yourFileName.sql

Folks

Answer 6

How to pass parameters in file

@A1.sql; (Parameter)

@A2.sql; (Parameter)

Darice