How to construct booleans and access them with java/oracle stored proc

Question

First time using Oracle, normally use SQLServer, and ran into a curious issue. I was tearing my hair out with this problem, and trying random things and one happened to work.

So originally, I had only two parameters, and everything worked fine. I then wished to add a boolean value to allow for multiple paths in my stored proc. I test it out in my db environment, and there are no errors and I get back the data I expect. I then add the few lines in my java code to pull this data, and suddenly I get "wrong number or types of parameters" error.

After trying several things, I simply change the type in Oracle from Boolean to Int, leaving the java code still as "setBoolean(3, true)" and everything works.

So my questions are:

1) what am I sending with this setBoolean() on the java side, and what is Oracle getting?

2) what was it expecting before when it was of type "Boolean, and why is this not compatible with setBoolean()?"

3) Should I use Oracle type int with java setBoolean(), or some other combination such as Oracle Varchar, and setString(), when going for speed?

Java:

String jobquery =   "{call PKG_TEST.GET_PLAN_DATA(?,?,?)}";  
            CallableStatement callStmt = con.prepareCall(jobquery);

            callStmt.setString(1,"15105");
            callStmt.setString(2, "");
            callStmt.setBoolean(3, true);

Oracle SP(previous):

PROCEDURE GET_PLAN_DATA(
        NAME_N_IN IN VARCHAR2,
        TYPE_C_IN IN VARCHAR2 DEFAULT NULL,
        ONLY_RESTRICTIONS_IN IN BOOLEAN DEFAULT FALSE)

Oracle changed to:

 PROCEDURE GET_PLAN_DATA(
            NAME_N_IN IN VARCHAR2,
            TYPE_C_IN IN VARCHAR2 DEFAULT NULL,
            ONLY_RESTRICTIONS_IN IN INT DEFAULT 0)

Answer 1

Boolean are not supported in Stored Procedure input It is a known limitation of the JDBC driver

The JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures.

https://docs.oracle.com/cd/B28359_01/java.111/b31224/apxtblsh.htm#i1005380

Answer 2

1) what am I sending with this setBoolean() on the java side, and what is Oracle getting?

From the Java docs:

Sets the designated parameter to the given Java boolean value. The driver converts this to an SQL BIT value when it sends it to the database.

So the Boolean value is converted into a BIT value.

2) what was it expecting before when it was of type Boolean, and why is this not compatible with setBoolean()?"

As said, it converts it into a BIT value, so it was expecting the same thing. Before, there existed a Boolean variable for oracle too. But as you said,After trying several things, I simply change the type in Oracle from Boolean to Int, leaving the java code still as "setBoolean(3, true)" and everything works., reason being, oracle doesn't have boolean variable any more. Why? Read this for a deep discussion on the issue.

3) Should I use Oracle type int with java setBoolean(), or some other combination such as Oracle Varchar, and setString(), when going for speed?

I would say varchar and setString(). Reason, setString() method converts it into a varchar. Hence, you save time as compared to setBoolean() and int, where you go from Boolean to BIT to int.

---

EDIT

Just something copy pasted from one of the above mentioned links to clarify the matter more:

Hi Tom, I have a question regarding boolean datatype.

Below is the SP, written by our client and we need to integrate this with our application.

PROCEDURE sp_xxxx_map (
                ic_opt_number       IN  VARCHAR2,
                in_cast_number    IN  NUMBER,
                in_tyre_amount          IN  NUMBER,
                ob_is_valid             OUT BOOLEAN,
                ob_is_obgr_obgt_valid   OUT BOOLEAN,

--------------- few more parameters....

While integrating the above, everything id fine except boolean OUT parameter. I think(correct me if am wrong) there is no SQL datatype in java(JDBC classes) to catch the
"ob_is_valid", "ob_is_obgr_obgt_valid" parameters. How do can go about this issue. One idea is: Can I write another SP(say SP1), which will call the above SP and will convert the boolean types to CHARs like 'Y' for true and 'N' for false. Finally I let the application to call the newly created SP1.

Followup February 5, 2003 - 9am Central time zone:

just prepare and execute:

declare
   b1 boolean;
   b2 boolean;
   n1 number := 0;
   n2 number := 0; 
begin
   sp_xxxx_map( ?, ?, ?, b1, b2 );
   if (b1) then n1 := 1; end if;
   if (b2) then n2 := 1; end if;
   ? := n1;
   ? := n2;
end;

from the jdbc app.