Calling an Oracle PL/SQL procedure in Java using a CallableStatement with a boolean IN parameter gives an PLS-00306 oracle error:
java, jdbc, oracle, plsql, sql
Solution
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.
Problem
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?