This is definitely not a new issue… but it's another example of a situation when SQLcl handles a SQL exception by doing little more than silencing it, when that exception should instead be processed, and duly notified to the end user.
(A similar case of SQLcl spectacularly silencing an unexpected SQL exception was demonstrated in this post. [EDIT: that one is a Java exception, not an SQL exception; the similarity remains.])
Test case:
declare
l_cv sys_refcursor;
begin
open l_cv for select 1 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 1/0 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 3 as num from dual;
dbms_sql.return_result(l_cv);
end;
/
Readout in SQL*Plus:
PL/SQL procedure successfully completed.
ResultSet #1
NUM
----------
1
1 row selected.
ResultSet #2
ERROR:
ORA-01476: divisor is equal to zero
no rows selected
ResultSet #3
NUM
----------
3
1 row selected.
(Almost as expected: actually, the “no rows selected” feedback could be thought of as misleading. But at least we get to see the ORA-01476 error message.)
Readout in SQLcl / SQL Developer:
PL/SQL procedure successfully completed.
ResultSet #1
NUM
----------
1
1 row selected.
ResultSet #2
And that's it. There's no feedback whatsoever about the raised ORA-01476 exception, and ResultSet #3 is not shown either.
Running the test with debug logging enabled reveals the following messages:
Oct 25, 2023 7:09:58 PM oracle.dbtools.raptor.newscriptrunner.PLSQL processImplicitResultSets
WARNING: oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
java.sql.SQLDataException: ORA-01476: divisor is equal to zero
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:123)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1426)
at oracle.jdbc.driver.OracleStatement.fetchMoreRows(OracleStatement.java:4127)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.fetchMoreRows(InsensitiveScrollableResultSet.java:924)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.fetchNextRows(InsensitiveScrollableResultSet.java:831)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.absoluteInternal(InsensitiveScrollableResultSet.java:803)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:504)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:272)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:379)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:353)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:161)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:72)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:933)
at oracle.dbtools.raptor.newscriptrunner.PLSQL.processImplicitResultSets(PLSQL.java:937)
at oracle.dbtools.raptor.newscriptrunner.PLSQL.runNonCreatePLSQL(PLSQL.java:918)
at oracle.dbtools.raptor.newscriptrunner.PLSQL.run(PLSQL.java:93)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runPLSQL(ScriptRunner.java:413)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:260)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:370)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:255)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:1189)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1370)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:350)
Caused by: Error : 1476, Position : 8, Sql = declare
l_cv sys_refcursor;
begin
open l_cv for select 1 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 1/0 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 3 as num from dual;
dbms_sql.return_result(l_cv);
end;
, OriginalSql = declare
l_cv sys_refcursor;
begin
open l_cv for select 1 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 1/0 as num from dual;
dbms_sql.return_result(l_cv);
open l_cv for select 3 as num from dual;
dbms_sql.return_result(l_cv);
end;
, Error Msg = ORA-01476: divisor is equal to zero
Which makes it obvious that SQLcl received the exception from the database, but did not process it properly.
The behaviour of SQL Developer is identical.
Regards,