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?