4 Replies Latest reply on Jul 16, 2012 10:02 AM by 949049

# setting root path in isqlplus

This is an issue running my runscript to deploy my pl/sql changes via i sqlplus.

Im running a script from the below path like (run.sql is my calling script, which calls many such files on the same root path but in diff folders)

h:\appln\scripts\runscript\run.sql

From within the run.sql script im calling another sql file as below

h:\appln\scripts\module1\insert_table.sql

as below

*@@..\module1\insert_table.sql*

But it says *'unable to open file ..\module1\insert_table.sql'*

I wanted to know how do i set the @@ to point h:\appln\scripts path by default for all the called scripts as all are lying in different folders in the same root path.

When i run the same run.sql runscript from TOAD, its working fine.Im just trying to replicate in i sql*plus as well.

Regards
Pandian

Edited by: 946046 on Jul 13, 2012 4:59 AM
• ###### 1. Re: setting root path in isqlplus
Hi,

Sorry, I don't know any way to do that.

One thing you can do is define a substitution variable as the path name, or part of the path name.
For example, in runscript.sql, you might include the statement
DEFINE  script_home =  h:\appln\scripts
Later in the session it doesn't matter which script you're in, or what folder it's in) you can say
@&script_home\module1\insert_table
to run h:\appln\scripts\module1\insert_table.sql.

I realize this is not what you wanted, but I hope it helps.
• ###### 2. Re: setting root path in isqlplus
Actually when i use @@ and run the same script in TOAD its able to pick up the root folder.
Im just trying to replicate the same in i sqlplus which cant access the called files.

Thanks
Pandian

Edited by: 946046 on Jul 13, 2012 5:04 AM
• ###### 3. Re: setting root path in isqlplus
>
I wanted to know how do i set the @@ to point h:\appln\scripts path by default for all the called scripts as all are lying in different folders in the same root path.
>
That would subvert the definition of @@ which is
>
When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script.
>
As I tried to tell you in the sql developer forum for sql*plus you can set the SQLPATH environment variable.

See the sql*plus documentation for the syntax and use of @ and @@ and how they interact.
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12002.htm#i2696724
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12003.htm#i2696759

In particular note that the ONLY the url form is supported in iSQL*Plus
>
Syntax
@{url | file_name[.ext] } [arg...]
Runs the SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. Only the url form is supported in iSQL*Plus. The @ command functions similarly to @@ and START.
. . .
@@{url | file_name[.ext] } [arg...]
Runs a script. This command is almost identical to the @ ("at" sign) command. When running nested scripts it looks for nested scripts in the same path or url as the calling script. Only the url form is supported in iSQL*Plus. The @@ command functions similarly to @ and START.
>
And review the example that uses a URL to see what the @@ does
>
Suppose that the same script PRINTRPT was located on a web server and you ran it with START HTTP://machine_name.domain:port/PRINTRPT. When it reaches the @ command, it looks for the script named EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the script named WKRPT in the same url as PRINTRPT, HTTP://machine_name.domain:port/WKRPT.SQL and runs it.
• ###### 4. Re: setting root path in isqlplus
Its working after i ve put my root path in REGEDIT-SQLPATH
It able to pick all my called scripts available in diff folder on a common root path updated on SQLPATH.

Regards
Pandian