Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@
and when the path starts with a single or double dot.
For example, under x:\some\where
I have the following directory structure:
script.sql
main-dir\main-sub-dir
call-script.sql
script.sql
That is: two script.sql
but at different locations.
The content of script.sql
just under x:\some\where
is simply
prompt SCRIPT root
while the other script.sql
's content is
prompt SCRIPT main-dir/main-subdir
call-script.sql
reads
@@script.sql
@ script.sql
expected output
If I start SQL*Plus from x:\some\where
and then do a
@main-dir/main-sub-dir/call-scripts
The output will be
SCRIPT main-dir/main-subdir
SCRIPT root
This is expected, since the single @
is supposed to search paths from where SQL*Plus was started and @@
is supposed to search paths from the containing script's directory.
unexpected output
Now, if I change call-scripts.sql
so:
@@./script.sql
@ ./script.sql
the double @@
seems to change it's behaviour, in that it searches paths from where SQL*Plus was started, and the output will now be
SCRIPT root
SCRIPT root
which is not what I expected.
Is this behaviour documented somewhere, and more importantly, how do I have to change call-scripts.sql
so that it calls relative paths (@@../../other-dir/other-sub-dir/script
) correctly?
Best Answer
Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future.
One way of working around this is "know" the path for scripts without actually hard coding that path. To do this in SQLPlus requires a trick - if you try to run a non-existent file, then you'll get an error message that includes the path name.
So here's a demo of that in action. To mimic your scenario I've got:
What we can do is add some commands to the front of call_script.sql which will pick up the path. It looks a little odd, but you should not need to change it - its just a fixed thing you paste in
What's happening here, is we're running a non-existent script, which returns:
"SP2-0310: unable to open file "path\_nonexistent_script.sql"
so with a little regexp we can extract the path, store it in a SQLPlus variable and then use from that point on.
So the final version of your call_script.sql would look like this
and when we run that, we get the following
and there you go :-)