Calling an Oracle PL/SQL procedure in Java using a CallableStatement with a boolean IN parameter gives an PLS-00306 oracle error:

Question

I have a pl/sql procedure on an Oracle 11g that has the following parameters:

PROCEDURE validate_product
   ( product_id_in IN varchar2 , 
     username_in in varchar2, 
     source_in varchar2,    
     source_id_in varchar2 , 
     isEuProduct in boolean , 
     error_code out varchar2, 
     product_type out varchar2  
     )

I am trying to call the above stored procedure from within java using the following code:

cstmt = getConnection().prepareCall("begin " + DBUtil.SCHEMANAME + ".PRODUCT_UTILITIES.validate_product(:1,:2,:3,:4,:5,:6,:7); end;");

cstmt.registerOutParameter(6, Types.CHAR); 
cstmt.registerOutParameter(7, Types.CHAR); 

cstmt.setString(1, productId);
cstmt.setString(2, username);
cstmt.setString(3, sourceName);
cstmt.setString(4, sourceId);
cstmt.setBoolean(5, isEUProduct);

cstmt.execute();

The types of the java variables are all String with the exception of isEUProduct which is boolean. Whenever i run the above program i get the following error:

PLS-00306: wrong number or types of arguments in call to validate_product ORA-06550: line 1, column 7: PL/SQL: Statement ignored"

I must have debugged the program a hundred times but everything seem to be the correct type and the number of arguments are correct.

I am completely stuck as to what it is i am doing wrong. Having googled around i suspect that maybe i am not setting the boolean parameter correctly.

Any ideas?

Answer 1

There is a simple workaround for that restriction, which does not require a wrapper procedure, simply wrap the boolean parameter in PL/SQL in a CASE statement and use an Integer for binding:

stmt = connection.prepareCall("begin" 
        +"  booleanFunc(par_bool => (CASE ? WHEN 1 THEN TRUE ELSE FALSE END)); "
        +"end;"
       );

// now bind integer, 1 = true, 0 = false
stmt.setInt(1, 0); // example for false

You may wrap the Integer during bind the other way around, if your method uses boolean, for example:

// now bind integer, 1 = true, 0 = false
stmt.setInt(1, myBool ? 1 : 0); 

Answer 2

Starting with Oracle 12.2 there is a JDBC Support for Binding PLSQL_BOOLEAN

I'll demonstrate is on a simple procedure with INand OUT BOOLEAN parameters

create or replace PROCEDURE proc_C (flag_in BOOLEAN, flag_out OUT BOOLEAN) as
begin
  flag_out := flag_in;
end;
/

Unfortunately it is not possible to use directly setBoolean to bind the first parameter.

The driver Version 19.3.0.0.0 returns in this case PLS-00306: wrong number or types of arguments in call to 'PROC_C'

The workaround is simple using setObject(1,true,oracle.jdbc.OracleTypes.PLSQL_BOOLEAN)

The whole example

stmt = con.prepareCall("{ call proc_C(?,?)}") 
stmt.setObject(1,false,oracle.jdbc.OracleTypes.PLSQL_BOOLEAN)
stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.PLSQL_BOOLEAN) 
stmt.execute()
flag = stmt.getBoolean(2)