Java – Why does Oracle’s JDBC driver not support Oracle’s Boolean type

booleanexceptionjavajdbcoracle

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:

enter image description here

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.

Best Solution

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.