Forum Stats

  • 3,734,713 Users
  • 2,247,030 Discussions
  • 7,857,460 Comments

Discussions

Unexpected Java Exception when SQL errors exist in referenced files

user8128423
user8128423 Member Posts: 4
edited Jan 17, 2019 3:44PM in SQLcl

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 onshow errorloggingTRUNCATE 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 onSQL> show errorloggingerrorlogging is ON TABLE SPERRORLOGSQL> 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 objectSQL> 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 onshow errorloggingTRUNCATE 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 onSQL> show errorloggingerrorlogging is ON TABLE SPERRORLOGSQL> TRUNCATE TABLE SPERRORLOG;Table SPERRORLOG truncated.SQL> @create_t1.sqlTable T1 created.SQL> @create_t1.sqlJan 16, 2019 1:12:40 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor runSEVERE: sun.nio.fs.WindowsPathParser.normalize(Unknown Source)java.nio.file.InvalidPathException: Illegal char <:> at index 4: file:/C:/repos/SKSandbox/sql/create_t1.sqlat 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 selectedSQL>

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 offshow errorloggingTRUNCATE TABLE SPERRORLOG;@create_t1[email protected]_t1.sqlselect /*csv*/ timestamp, message from sperrorlog;

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

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

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?

Answers

Sign In or Register to comment.