Why Oracle JDBC Driver Doesn't Support Oracle Boolean Type
I am new to JDBC and am playing around with it. Other forum posts indicate that the Oracle JDBC driver does not support the Oracle PLSQL Boolean type. I find this really strange:
From the Oracle JDBC documentation it seems that this does:
But another section says that it does not allow passing BOOLEAN parameters to PL / SQL stored procedures.
Isn't the documentation contradicting itself?
It doesn't allow me to pass or receive boolean values from PL / SQL procedures / functions. This 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 Oracle JDBC drivers do not support booleans. Is it because PL / SQL "Boolean"
accepts null values while Java primitive types "boolean"
"Boolean"
do not?
In contrast, the wrapper Java class "Boolean"
accepts NULL values. This can be used to map to a PLSQL boolean type. Can anyone shed more light on this.
source to share
From: PL / SQL Table Types, BOOLEAN and RECORD
The Oracle JDBC Drivers cannot support calling arguments or return PL / SQL RECORD, BOOLEAN, or non-scalar table member values. However, Oracle JDBC drivers support the PL / SQL scalar element type table table.
...
As a workaround for PL / SQL RECORD, BOOLEAN, or non-scalar table types, create container procedures that treat data as JDBC-supported types. For example, to wrap a stored procedure that uses a PL / SQL Boolean expression, 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, takes a BOOLEAN argument from the original and passes it as CHAR or NUMBER in JDBC. Likewise, to wrap a stored procedure that uses PL / SQL records, create a stored procedure that handles the record in its own separate components, such as CHAR and NUMBER, or as a structured object. To wrap a stored procedure using PL / SQL tables, break the data into components, or perhapsuse Oracle collection types.
source to share
As with fooobar.com/questions/4087818 / ... (with code examples), the Oracle JDBC thin driver has supported the PLSQL BOOLEAN type since 12.2.
source to share
Java supports null values for primitive values using the wasNull
on ResultSet
and method CallableStatement
, so this is not a reason for support BOOLEAN
.
However, the documentation you link to says is not supported . You can use oracle extensions . The documentation also links to an example workaround (for passing parameters, but the analog for OUT is easy to deduce).
source to share