plsql - SQLCL - 引用文件中存在 SQL 错误时出现意外 Java 异常
问题描述
当在命令行脚本与单独的 sql 文件中遇到 sql 错误时, 我使用SQLCL遇到不同的行为。
原始脚本,命令行 - 预期的 sql 错误
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;
当直接从 SQLCL 命令提示符执行创建表语句时,上述脚本按预期执行。正如预期的那样,第二个表创建语句报告了一个错误,表明该表已经存在。
命令行预期的结果:
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
"
更新的脚本,外部文件 - java 异常:
但是,如果修改脚本以将“ create table ”语句移动到单独的 sql 文件中,则第二次执行不会报告 ORA-00955,而是会引发 Java 运行时异常
set errorlogging on
show errorlogging
TRUNCATE TABLE SPERRORLOG;
@create_t1.sql;
@create_t1.sql;
select /*csv*/ timestamp, message from sperrorlog;
create_t1.sql 的内容:
create table t1 (field1 number(10));
导致 java 运行时异常:
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>
更新 2,ext 文件,注销 - 没有 java 异常
如果ERRORLOGGING设置为OFF,则不报java异常,按预期返回SQL错误。
set errorlogging off
show errorlogging
TRUNCATE TABLE SPERRORLOG;
@create_t1.sql
@create_t1.sql
select /*csv*/ timestamp, message from sperrorlog;
结果包含没有 java 异常但缺少必要的 SPERRORLOG 记录的 sql 错误:
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>
任何人都有关于如何使用 sql 文件的建议,这些文件会导致适当的错误被记录到 SPERRORLOG 而没有 java 异常?
解决方案
看起来肯定是一个错误 - 您应该将这些报告给 My Oracle Support 或在论坛上打开一个线程。
推荐阅读
- amazon-web-services - 如何使用 moto_server 运行多个 AWS 服务
- javascript - 接受/触发带有 HTML/JS 的图像键盘 (GIF)
- markdown - Markdown 无法在内联代码段内创建链接
- python - 如何使用 VBA 指定打印机?
- django - DRF 嵌套序列化程序无法更新嵌套数据
- php - codeigniter DOMPDF 显示空白屏幕,没有错误,html 生成正确但 pdf 不生成
- unix - 我使用 nohup 运行了一个脚本,其中调用了 3 个下标
- fortran - 不同的程序结果取决于程序的链接方式
- python - 当输入为 DataFrame 时,使用索引数组在 GridSearchCV 中定义折叠
- typescript - Firebase 云函数使用 onCall 返回处理后的数据