This discussion is archived
4 Replies Latest reply: Jul 16, 2012 3:02 AM by 949049 RSS

setting root path in isqlplus

949049 Newbie
Currently Being Moderated
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
    Frank Kulash Guru
    Currently Being Moderated
    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
    949049 Newbie
    Currently Being Moderated
    Thanks! for your reply Frank.As u know thats not my issue.
    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
    rp0428 Guru
    Currently Being Moderated
    >
    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.

    iSql*Plus has been desupported in favor of sql developer - see this thread for that https://kr.forums.oracle.com/forums/thread.jspa?threadID=2298152

    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
    949049 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points