9

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?

  • What's your SQLPATH environment variable set to? That affects which directories are searched. – Philᵀᴹ May 2 '12 at 8:20
  • 1
  • 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
  • 2
    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
7

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:

c:\temp\demo
   script.sql
   maindir
      subdir
         call_script.sql
         script.sql

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

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin 
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on

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

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin 
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on
prompt path was &path      

@@&path\script.sql
@&path\script.sql

and when we run that, we get the following

SQL> @maindir\mainsubdir\call_script
path was maindir\mainsubdir
script in subdir
script in subdir

and there you go :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.