Oracle sqlplus: relative paths starting at script position

Question

When I have two sql files, one of them in a subdirectory

main_test.sql sub/sub_test.sql 

and sub_test.sql calls @../main_test.sql (or @@../main_test.sql ), then this works fine when executing it from a subdirectory

 sub> sqlplus xxx @ sub_test.sql 

But when I call

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

This leads to

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

since the path is evaluated from my working directory, not the SQL directory of the file that I am calling.

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

Answer 1

It’s a shame that the "file" URL is not supported, as it works very well when using the "http: //" paths!

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

 @@../main.sql 

puts the entire directory structure in the Tomcat webapps / ROOT context, and it works if you call like this:

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

Probably a little too much with setting up a web server, I think? (It also works with FTP apparently - I have not tried it).

Answer 2

I don't know how to achieve this, I'm afraid, but this illustrates one of the problems with this hosted script approach.

I remember when I started with Oracle in version v7.1. Whatever scripts we used for almost everything, there were so many tasks that could only be achieved with external scripts that you could use for everything. However, I think there were so many reasons to do this with each release, and the only ones I have now will be reorganized to pl / sql from 11g.

Answer 3

(It seems to me that the file name in the line with error SP2-0310 was entered incorrectly. I believe that the file you wanted to specify was "../main_test.sql" and I will continue the line).

If you are in the directory containing main_test.sql and you execute sub / sub_test.sql, and inside sub / sub_test.sql you call main_test.sql as "../main_test.sql", yes, you will get an error, because that your current working directory is the one that contains main_test.sql, so finding one level of the directory with ".." will not work. In this case, you will need to remove ".." or use ".". (one period - indicates the current directory) to make the work work.

Share and enjoy.