1 Reply Latest reply: Aug 17, 2013 11:38 AM by Jim Smith RSS

    How to handle relative folders in script

    trent

      Just say I have the following folder: C:\test

       

      Inside there is one file, install.sql, and a folder 'addons' with the file three.sql

       

      How should I run install.sql in SQL Dev?

       

      install.sql:

       

      @@addons/three.sql;
      

       

       

      @@c:\test\install.sql results in:

       

      Error starting at line 4 in command:

      @@addons/three.sql

      Error report:

      Unable to open file: "addons/three.sql"

       

       

      This is a basic example. The actual application is for install pl/json... It has the following lines in the install script:

       

      PROMPT ------------------------------------------;
      PROMPT -- Adding optional packages for PL/JSON --;
      PROMPT ------------------------------------------;
      @@addons/json_dyn.sql --dynamic sql execute
      @@addons/jsonml.sql --jsonml (xml to json)
      @@addons/json_xml.sql --json to xml copied from http://www.json.org/java/org/json/XML.java
      @@addons/json_util_pkg.sql --dynamic sql from http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html
      @@addons/json_helper.sql --Set operations on JSON and JSON_LIST
      

       

       

      But running @@C:\pljson\install.sql

       

      ------------------------------------------

      -- Adding optional packages for PL/JSON --

      ------------------------------------------


      Error starting at line 52 in command:

      @@addons/json_dyn.sql --dynamic sql execute

      Error report:

      Unable to open file: "addons/json_dyn.sql"


      Error starting at line 53 in command:

      @@addons/jsonml.sql --jsonml (xml to json)

      Error report:

      Unable to open file: "addons/jsonml.sql"


      Error starting at line 54 in command:

      @@addons/json_xml.sql --json to xml copied from http://www.json.org/java/org/json/XML.java

      Error report:

      Unable to open file: "addons/json_xml.sql"


      Error starting at line 55 in command:

      @@addons/json_util_pkg.sql --dynamic sql from http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html

      Error report:

      Unable to open file: "addons/json_util_pkg.sql"


      Error starting at line 56 in command:

      @@addons/json_helper.sql --Set operations on JSON and JSON_LIST

      Error report:

      Unable to open file: "addons/json_helper.sql"


        • 1. Re: How to handle relative folders in script
          Jim Smith

          This is SQL*Plus compatible - i.e. the expected behaviour.    Although I have to say it took me by surprise until I tested it.

           

          As you can see from the example below,  it doesn't work in sqlplus either, unless your current directory is where install.sql is.

           

          You can make it work in SQL Developer by setting the Select default path to look for scripts preference to the location of install.sql

           

          C:\Documents and Settings\Administrator>sqlplus jim/xxxxx@xe

           

          SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 17 12:28:32 2013

           

          Copyright (c) 1982, 2011, Oracle.  All rights reserved.

           

           

          Connected to:

          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

           

          jim@XE> @q:\dev\scratch\install

          SP2-0310: unable to open file "addons\test1.sql"

          jim@XE> quit

          Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

           

          C:\Documents and Settings\Administrator>q:

           

          Q:\>cd \dev\scratch

           

          Q:\dev\scratch>sqlplus jim/xxxx@xe

           

          SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 17 12:29:33 2013

           

          Copyright (c) 1982, 2011, Oracle.  All rights reserved.

           

           

          Connected to:

          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

           

           

           

           

          jim@XE> @install

           

          'TEST

          -----

          test1

           

          1 row selected.

           

          Elapsed: 00:00:00.00

          jim@XE>