Oracle sqlplus: relative paths starting at position of script

Question

When I have two sql-files, one of them in a sub-directory

main_test.sql
sub/sub_test.sql

and sub_test.sqlcalls @../main_test.sql (or @@../main_test.sql) then this works fine when executing it from the sub-directory

sub> sqlplus xxx @ sub_test.sql

But when I call

sub> cd ..
> sqlplus xxx @ sub/sub_test.sql

this results in

SP2-0310: unable to open file "../main_test.sql"

since the path is evaluated from my working directory, not the directory of the sql-file I call.

Is there a way to use relative paths starting from the directory of file containing the call?

Answer 1

I don't know of a way of achieving this I'm afraid, but it does illustrate one of the problems of this hosted script approach.

I remember back when I started with Oracle in v7.1.whatever we would use scripts for almost everything, but there were so many tasks that could only be achieved with external scripts that you might as well use them for everything. However I think that there have been fewer reasons to do so with every release, and the only ones I have now would be refactored to pl/sql with 11g.

Answer 2

(It looks to me like the filename in the line with the SP2-0310 error was mis-typed. I believe that the file you meant to indicate was "../main_test.sql" and I'll proceed along that line).

If you're in the directory containing main_test.sql and you execute sub/sub_test.sql, and within sub/sub_test.sql you're invoking main_test.sql as "../main_test.sql", yes, you're going to get an error because your current working directory is the one that contains main_test.sql, so looking up one directory level by using ".." isn't going to work. In this case you'll either need to remove the ".." or use "." (single period - indicates current directory) to get things to work.

Share and enjoy.

Answer 3

Shame the 'file' url isn't supported - since this actually works very well when using 'http://' paths !

I set up 'sub_test\sub_test.sql' to contain the double-ats:

@@../main.sql

put the whole directory structure under a Tomcat webapps/ROOT context and it works if you call like this:

SQL> @http://host:port/sub_test/sub_test.sql

Probably a bit of overkill setting up a web-server I guess ?? (Also works with FTP apparently - not tried it).