4 Replies Latest reply: Jul 16, 2012 5:02 AM by 949049 RSS

    setting root path in isqlplus

    949049
      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
          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
            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
              >
              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
                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