Skip to Main Content

Unexpected Java Exception when SQL errors exist in referenced files

user8128423Jan 16 2019 — edited Jan 17 2019

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?

Comments
Post Details
Added on Jan 16 2019
1 comment
151 views