1 Ответить Последний ответ: 17.01.2019 20:44, автор: Glen Conway

    Unexpected Java Exception when SQL errors exist in referenced files

    user8128423

      I am experiencing different behaviors using SQLCL when sql errors are encountered in scripts on command line vs separate sql file.

       

      Original script, command line - expected sql error

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

      set errorlogging on
      show errorlogging
      TRUNCATE TABLE SPERRORLOG;
      create table t1 (field1 number(10));
      create table t1 (field1 number(10));
      select /*csv*/ timestamp, message from sperrorlog;
      

       

      The above script executes as expected when the create table statements are executed directly from the SQLCL command prompt.   As expected, the second table create statement reports an error indicating the table already exists.

       

      Results as expected from command line:

      SQL> set errorlogging on
      SQL> show errorlogging
      errorlogging is ON TABLE SPERRORLOG
      SQL> TRUNCATE TABLE SPERRORLOG;
      Table SPERRORLOG truncated.
      SQL> create table t1 (field1 number(10));
      Table T1 created.
      SQL> create table t1 (field1 number(10));
      Error starting at line : 1 in command -
      create table t1 (field1 number(10))
      Error report -
      ORA-00955: name is already used by an existing object
      SQL> select /*csv*/ timestamp, message from sperrorlog;
      "TIMESTAMP","MESSAGE"
      16-JAN-19 12.56.36.000000000 PM,"ORA-00955: name is already used by an existing object"
      

      ----------

       

       

      Updated script, external file - java exception:

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

      However, if the script is modified to move the "*create table*" statement to a separate sql file, the second execution does NOT report the ORA-00955 but rather throws a Java runtime exception

       

      set errorlogging on
      show errorlogging
      TRUNCATE TABLE SPERRORLOG;
      @create_t1.sql;
      @create_t1.sql;
      select /*csv*/ timestamp, message from sperrorlog;
      

       

      The contents of create_t1.sql:

      create table t1 (field1 number(10));
      

       

      Results in a java runtime exception:

      SQL> set errorlogging on
      SQL> show errorlogging
      errorlogging is ON TABLE SPERRORLOG
      SQL> TRUNCATE TABLE SPERRORLOG;
      Table SPERRORLOG truncated.
      
      
      SQL> @create_t1.sql
      
      
      Table T1 created.
      
      
      SQL> @create_t1.sql
      Jan 16, 2019 1:12:40 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
      SEVERE: sun.nio.fs.WindowsPathParser.normalize(Unknown Source)
      java.nio.file.InvalidPathException: Illegal char <:> at index 4: file:/C:/repos/SKSandbox/sql/create_t1.sql
      at sun.nio.fs.WindowsPathParser.normalize(Unknown Source)
      at sun.nio.fs.WindowsPathParser.parse(Unknown Source)
      at sun.nio.fs.WindowsPathParser.parse(Unknown Source)
      at sun.nio.fs.WindowsPath.parse(Unknown Source)
      at sun.nio.fs.WindowsFileSystem.getPath(Unknown Source)
      at java.nio.file.Paths.get(Unknown Source)
      at oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext.errorLog(ScriptRunnerContext.java:2360)
      at oracle.dbtools.raptor.newscriptrunner.SQL.handleSQLException(SQL.java:245)
      at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:217)
      at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:404)
      at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:230)
      at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:341)
      at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:224)
      at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runExecuteFile(SQLPLUS.java:3900)
      at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:209)
      at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:420)
      at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:257)
      at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:341)
      at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:224)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:404)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:415)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1247)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:491)
      
      
      SQL> select /*csv*/ timestamp, message from sperrorlog;
      "TIMESTAMP","MESSAGE"
      no rows selected
      SQL>
      

       

      Update 2, ext file, logging off - no java exception

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

      If ERRORLOGGING is set to OFF, the java exception is not reported and the SQL error is returned as expected.

       

      set errorlogging off
      show errorlogging
      TRUNCATE TABLE SPERRORLOG;
      @create_t1.sql
      @create_t1.sql
      select /*csv*/ timestamp, message from sperrorlog;
      

       

      Results contain the sql error without the java exception but lack the necessary SPERRORLOG record:

       

      SQL> set errorlogging off
      SQL> show errorlogging
      errorlogging is OFF
      SQL> TRUNCATE TABLE SPERRORLOG;
      
      
      Table SPERRORLOG truncated.
      
      
      SQL> @create_t1.sql
      
      
      Table T1 created.
      
      
      SQL> @create_t1.sql
      
      
      Error starting at line : 1 File @ C:\repos\SKSandbox\sql\create_t1.sql
      In command -
      create table t1 (field number(10))
      Error report -
      ORA-00955: name is already used by an existing object
      
      
      SQL> select /*csv*/ timestamp, message from sperrorlog;
      "TIMESTAMP","MESSAGE"
      no rows selected
      SQL>
      
      

       

       

      Anyone have suggestions for how to use the the sql files that will result in appropriate errors being logged to SPERRORLOG without the java exception?