CODEWITHSUNDEEP
sqloracle10gsqlplus
Peter Lang
Peter Lang

Reputation: 55534

Oracle sqlplus: relative paths starting at position of script

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?

Upvotes: 6

Views: 13412

Answers (3)

monojohnny
monojohnny

Reputation: 6181

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).

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

(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.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 1

Related Questions