Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What's your SQLPATH environment variable set to? That affects which directories are searched. –  Phil May 2 '12 at 8:20
    
    
Same behaviour under Linux, FWIW. (And an ampersand is &, not @; which doesn't seem to have a real name). Seems to be bug, since it's inconsistent. Only thing that comes to mind is to set a variable it the top level script with the full path and do everything based on that, but that's not very convenient unless the directory structure below that is fixed. –  Alex Poole May 2 '12 at 9:03
    
Thanks for pointing out the @ vs ampersands thing... I should have known it, but when I wrote the post I didn't really pay attention. It's now fixed in the title. –  René Nyffenegger May 2 '12 at 9:10
    
I just attacked sqlplus with strace. Here's the relevant calls: pastebin.com/cVK1QQu4 Note that it didn't attempt to stat or access "script.sql" files in any other directories before attempting to open the ones seen in the pastebin output. –  Phil May 2 '12 at 10:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.