Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQLcl 23.3: 23c SQL Boolean support

user9540031Sep 24 2023 — edited Oct 30 2023

[Apologies if this has already been reported.]

Proof:

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 23.3.0.0 build: 23.3.0.251.1308

SQL> select true;

no rows selected

SQL> select false;

FALSE        
____________ 
-Infinity

Remark: as of this writing, apparently this build of SQLcl (23.3.0.251.1308) is available only in the latest VirtualBox appliance for 23c FREE (see this comment); it's not officially released per the usual SQLcl release download links.

The same can be achieved in SQLcl 23.2, if configured to use the ojdbc11.jar JDBC driver.

SQL*Plus (23.3.0.23.09) returns the expected results.

What really happens in SQL 23.3 is clearer with debug logging enabled: the following exception happens when the select true query returns:

Sep 24, 2023 12:10:29 PM oracle.dbtools.raptor.format.ResultsFormatter print
WARNING: Index 0 out of bounds for length 0
java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
        at oracle.sql.NUMBER._fromLnxFmt(NUMBER.java:3333)
        at oracle.sql.NUMBER.toBigDecimalFull(NUMBER.java:765)
        at oracle.sql.NUMBER.toBigDecimal(NUMBER.java:628)
        at oracle.sql.NUMBER.bigDecimalValue(NUMBER.java:2221)
        at oracle.dbtools.raptor.format.ANSIConsoleFormatter.printColumn(ANSIConsoleFormatter.java:80)
        at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:274)
        at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:222)
        at oracle.dbtools.raptor.format.ResultsFormatter.print(ResultsFormatter.java:519)
        at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:137)
        at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:72)
        at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:933)
        at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:835)
        at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:95)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:433)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:250)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:359)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:242)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:1208)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1389)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:380)

So it would seem SQLcl handles the BOOLEAN column as a number, resulting in a Java exception; and—worse—it silences that exception and pretends that the query didn't return any row.

SQLcl mishandling the BOOLEAN column as a number also explains the readout of select false;

Regards,

This post has been answered by thatJeffSmith-Oracle on Oct 30 2023

Jump to Answer

Added on Sep 24 2023

#boolean, #sqlcl

9 comments

564 views

1 mention