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?

Solution 1

I was amazed when we ran into this, but the Oracle JDBC Driver doesn't support passing booleans into Stored Procedures.... Ya, I'm not making that up :)

Boolean Parameters in PL/SQL Stored Procedures

The JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN.

Solution 2

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); 

Solution 3

I think here is the issue

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

you called from java as above, But you declared in procedure out parameter as varchar2,that means there is a mismatch of datatype.

Try this code,

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

I hope this will work..

Solution 4

I suspect the out statements might need modification to the following (since the type of the out parameters is varchar2 in the procedure):-

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

However, if that does not work, then try modifying the prepareCall statement to the following also :-

cstmt = getConnection().prepareCall("{call " + DBUtil.SCHEMANAME + ".PRODUCT_UTILITIES.validate_product(?,?,?,?,?,?,?)}");

Solution 5

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)

View more solutions

Comments

[ziggy]

Wow i was not expecting that. I suspect this could as well be the reason why it is not work. Thanks.

[Marmite Bomber]

Probably CASE ? WHEN 0 THEN FALSE ... will be safer, but a nice trick!

[Marmite Bomber]

As always with Oracle if you wait long enough the missing feature gets in the new release. Native BOOLEAN binding is available starting with 12.2. See example here