Why does Oracle's JDBC driver not support Orac

问题:

I am new to JDBC and have been playing with it. Other posts in the forum indicate that Oracle's JDBC driver does not support Oracle PLSQL Boolean type. I find that really strange:

From the oracle jdbc documentation it seems like it does:

But in another section it says it does not allow passing of BOOLEAN parameters to PL/SQL stored procedures.

Isn't the the documentation contradicting itself ?

It does not let me pass or accept Boolean values from PL/SQL procedures/functions. It gives me the following exception:

Exception occured in the database
Exception message: Invalid column type: 16
Database error code: 17004
java.sql.SQLException: Invalid column type: 16
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3963)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
    at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1579)
    at com.HrManager.insertNewEmployee(HrManager.java:1300)
    at com.HrManager.main(HrManager.java:1411)

I am trying to understand why JDBC Oracle Drivers do not support Boolean types. Is it because PL/SQL "Boolean" accepts null values and Java's primitive type "boolean" does not ?

But the counter to it would be , Java's wrapper class "Boolean" does accept nulls. This can be used to map to PLSQL's Boolean type . Can some one throw more light on this.

回答1:

From: PL/SQL TABLE, BOOLEAN, and RECORD Types

It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types. However, Oracle JDBC drivers support PL/SQL index-by table of scalar element types.

...

As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table types, create container procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL boolean, create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, create a stored procedure that handles a record in its individual components, such as CHAR and NUMBER, or in a structured object type. To wrap a stored procedure that uses PL/SQL tables, break the data into components or perhaps use Oracle collection types.

回答2:

As answered here https://stackoverflow.com/a/55413745/4612499 (with code examples) the Oracle JDBC thin driver supports the PLSQL BOOLEAN type starting in 12.2.

回答3:

Java supports null values for primitive values using the wasNull method on ResultSet and CallableStatement, so that is no reason for not supporting BOOLEAN.

However the documentation you link to says it is not supported. You may be able to use oracle specific extensions. The documentation also links to an example workaround (for parameter passing, but the analogue for OUT is easy to deduce).