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 :-)
Best Answer
You need to set the environment variable SQLPATH (through the Control Panel) to tell SQL*Plus where to find the file:
Setting environment variables is explained here: http://support.microsoft.com/kb/310519/en-us