The new BOOLEAN data type in Oracle Database 23c with PL/SQL and the JDBC Drivers (21c, 23c)

Develop Java applications with Oracle Database

by Juarez Junior

Introduction

A previous blog post introduced the ISO SQL standard-compliant BOOLEAN data type as supported by the Oracle Database 23c Free — Developer Release.

We’ve explored several specific scenarios concerning the details that Java developers must pay attention to when working with the BOOLEAN data type for column definitions, and its use with the Oracle JDBC drivers (23c, 21c).

Besides, we’ve also analyzed what happens when using some different JDBC queries with table columns that use the NUMBER and VARCHAR data types and values that may represent logical boolean values.

This blog post complements those scenarios but now in consideration of such combinations with PL/SQL stored procedures, JDBC and CallableStatements.

If you need an introduction to JDBC and CallableStatements, please check my previous blog post — Invoking Stored Procedures with JDBC CallableStatements.

So, without further ado, let’s get started!

Prerequisites

• JDK — Java Development Kit 17+
• Your preferred Java IDE — Eclipse, IntelliJ, VS Code
• Apache Maven
• Oracle Database 23c Free — Developer Release running on Docker

Oracle Database 23c Free — Developer Release on Docker

We’ll use an Oracle Database 23c Free — Developer Release instance running on a local Docker environment.

Please check my previous blog post for details concerning using the related Docker image — Oracle Database 23c Free — Developer Release for Java Developers with Docker on Windows.

The new BOOLEAN data type, SQL and JDBC CallableStataments

To explain our scenarios, first, let’s create a simple database table called HQ_EMPLOYEE with a BOOLEAN column as below.

To explain our scenarios, first, let’s create a database user and the associated grants as required.

CREATE USER JDBCSP_USER IDENTIFIED BY <JDBCSP_USER_PASSWORD>;
GRANT DB_DEVELOPER_ROLE TO JDBCSP_USER;
GRANT CREATE SESSION TO JDBCSP_USER;
GRANT UNLIMITED TABLESPACE TO JDBCSP_USER;

Next, let’s create a simple database table called HQ_EMPLOYEE with a BOOLEAN column as below.

CREATE TABLE HQ_EMPLOYEE
(
  "EMP_ID"   NUMBER NOT NULL ENABLE,
  "NAME"    VARCHAR2(20 BYTE) DEFAULT NULL,
  "ROLE"    VARCHAR2(20 BYTE) DEFAULT NULL,
  "ACTIVE" BOOLEAN DEFAULT NULL,
  PRIMARY KEY ("EMP_ID")
);
COMMIT;

Inspect the table details to confirm the ACTIVE column data type as a BOOLEAN one.

DESCRIBE HQ_EMPLOYEE;

ACTIVE column as BOOLEAN

Now, we can create a stored procedure that will support us in analyzing the different scenarios as in the previous blog post but now using a CallableStatement with a PL/SQL stored procedure.

The current analysis takes a somewhat different approach as you will note when checking the PL/SQL stored procedure below, as we will use a SQL INSERT statement instead of a SQL SELECT for these scenarios. So, the former is the focus of our analysis, that is, to understand how a JDBC CallableStatement in combination with PL/SQL will support the use of a BOOLEAN data type column, as the latter has already been explored in the scope of our first blog post in this series.

CREATE OR REPLACE PROCEDURE INSERT_HQ_EMPLOYEE_PRC
(
 in_emp_id IN HQ_EMPLOYEE.EMP_ID%TYPE,
 in_name IN HQ_EMPLOYEE.NAME%TYPE,
 in_role IN HQ_EMPLOYEE.ROLE%TYPE,
 in_active IN HQ_EMPLOYEE.ACTIVE%TYPE, 
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO HQ_EMPLOYEE (EMP_ID, NAME, ROLE, ACTIVE) 
  VALUES (in_emp_id, in_name, in_role, in_active);
  COMMIT;
  
  out_result := 'TRUE';
 
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

GRANT EXECUTE ON INSERT_HQ_EMPLOYEE_PRC TO JDBCSP_USER;

Last, we can now implement the Java code sample with a CallableStatement, and call the procedure to test everything.

After that, we’ll have our testing workbench ready to support us in analyzing the proposed scenarios.

Scenario 1 — Oracle JDBC driver 23c (23.2.0.0), Oracle Database 23c Free, a CallableStatement with setBoolean(), and a BOOLEAN data type column

Scenario 1

To support the following scenarios, you have to add the Oracle JDBC driver 23c as a dependency to your project. The sample code uses Maven, so you will see a dependency like the one below.

<dependency>
   <groupId>com.oracle.database.jdbc</groupId>
   <artifactId>ojdbc11-production</artifactId>
   <version>23.2.0.0</version>
   <type>pom</type>
  </dependency>

Now, regarding our first scenario, we can call the PL/SQL stored procedure to verify its behaviour in such a combination per the scenario specification above.

In summary, the stored procedure will be executed with the parameter type set in the CallableStatement, which is a java.lang.Boolean (or primitive boolean) — stmt.setBoolean(4, true);

With that, we will verify if the data will be inserted into our target table which has a column defined with the new BOOLEAN data type.

So, concerning this first scenario, the stored procedure will be executed successfully as expected.

PL/SQL stored procedure executed successfully

As a double-check, you can also query the database to confirm that a new record with the ID shown above has been inserted.

Record inserted successfully

Scenario 2 — Oracle JDBC driver 23c (23.2.0.0), Oracle Database 23c Free, a CallableStatement with setInt(), and a BOOLEAN data type column

Scenario 2

Now, in the second scenario, the stored procedure will be executed with the parameter type set in the CallableStatement, which is a java.lang.Integer (or primitive int) — stmt.setInt(4, 1);

With that, once again we will verify if the data will be inserted into our target table which has a column defined with the new BOOLEAN data type.

However, interestingly, when using CallableStatements with stored procedures, it will fail altogether as shown below.

PL/SQL stored procedure failed

Such a result gives us evidence that the extensive support to variation and the flexibility experienced when running queries against a BOOLEAN data type column that we’ve verified in part 1 of this blog series is not the same when using CallableStatements and PL/SQL stored procedures.

Scenario 3 — Oracle JDBC driver 23c (23.2.0.0), Oracle Database 23c Free, a CallableStatement with setString(), and a BOOLEAN data type column

Concerning this scenario, the stored procedure will be executed with the parameter type set in the CallableStatement, which is a java.lang.String — stmt.setString(4, “1”);

As expected, this scenario will not work and cause a failure as well.

PL/SQL stored procedure failed

To make it complete, additional scenarios like the one above but with the JDBC driver version 21c (21.9.0.0) have also been tested, and the results are pretty much the same.

<dependency>
   <groupId>com.oracle.database.jdbc</groupId>
   <artifactId>ojdbc11-production</artifactId>
   <version>21.9.0.0</version>
   <type>pom</type>
  </dependency>

In summary, the key takeaway then is that with a CallableStatement, the only scenario that works is Scenario 1 above, where you set a parameter of type java.lang.Boolean (or the Java primitive boolean) prior to calling the target PL/SQL stored procedure that will insert data into a table with a BOOLEAN data type column.

Wrapping it up

That’s it! This blog post explored different scenarios when using the new BOOLEAN data type in Oracle 23c Free — Developer Release in combination with the Oracle JDBC Drivers 23c (23.2.0.0) and 21c (21.9.0.0).

In particular, beyond the scenarios explored in part 1 of this series, it considered the use of java.sql.CallableStatement and PL/SQL procedures to insert data into a database table that has a BOOLEAN data type column.

Different combinations of input parameters have been set with the respective methods available on the CallableStatement’s interface — setBoolean(), setInt() and setString(), and the resulting behaviour has been analyzed and documented.

I hope it will help you understand the expected behaviour concerning the different scenarios and combinations presented above.

References

Oracle Database 23c Free — Developer Release — Boolean Data Type

Oracle Database JDBC Java API Reference, Release 23c

Product — Oracle Database 23c Free — Developer Release

Technical Blog — Oracle Database 23c Free — Developer Release

Documentation — Oracle Database 23c Free — Developer Release

Forum — Oracle Database 23c Free — Developer Release

Database PL/SQL Language Reference 23

PL/SQL Developer Guides

The Java Tutorials: JDBC Basics

Develop Java applications with Oracle Database

Developers Guide For Oracle JDBC on Maven Central

Oracle JDBC FAQ

JDBC 4.3 API

JSR-000221 JDBCTM API 4.3 Maintenance Release 3

JSR 221: JDBCTM 4.0 API Specification

Oracle SQL Developer

Oracle Developers and Oracle OCI Free Tier

Join our Oracle Developers channel on Slack to discuss Java, JDK, JDBC, GraalVM, Microservices with Spring Boot, Helidon, Quarkus, Micronaut, Reactive Streams, Cloud, DevOps, IaC, and other topics!

Build, test, and deploy your applications on Oracle Cloud — for free! Get access to OCI Cloud Free Tier!