JDBC - Retrieve Boolean output from Oracle Procedure

Question

I need to invoke an Oracle procedure with one IN parameter with VARCHAR2 and OUT parameter as BOOLEAN data type.

Below is my code using SimpleJdbcCall

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getTemplate()) 
            .withCatalogName("package_name")
            .withProcedureName("proc_name")
            .withoutProcedureColumnMetaDataAccess()
            .declareParameters(
                new SqlParameter ("userName", Types.VARCHAR), 
                new SqlOutParameter("status", Types.BOOLEAN)
            );                  
Map<String, Object> inParams = new HashMap<String, Object>();
inParams .put("userName",  userInput);
Map<String, Object> outputValue= jdbcCall.execute(inParams);

Exception : CallableStatementCallback; uncategorized SQLException for SQL [{call PACKAGE_NAME.PROC_NAME(?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type: 16; nested exception is java.sql.SQLException: Invalid column type: 16

After doing a research i found that "JDBC drivers do not support the passing of Boolean parameters to PL/SQL stored procedures"

It has been suggested to wrap the PL/SQL procedure with a second PL/SQL procedure. The main problem is that i am restricted for write access in the db as that is client data. please help me to fix this issue.

Some of the links i referred

1. https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/tips3.htm
2. https://community.oracle.com/thread/2139408?tstart=0
3. https://community.oracle.com/thread/887712?tstart=0
4. https://community.oracle.com/thread/975159?tstart=0
5. Stored Function - Sending/Receiving Boolean - BD
6. http://docs.oracle.com/cd/B28359_01/java.111/b31224/apxtblsh.htm#i1005380

Answer 1

From the official Oracle JDBC documentation:

It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types. [...] As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table types, create container procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL boolean, create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, create a stored procedure that handles a record in its individual components, such as CHAR and NUMBER, or in a structured object type.

This is exactly what you did in your answer, but I wanted to add the documentation as reference.

Answer 2

I fixed the issue by writing a wrapper procedure to process the result of the actual procedure and sending back the result as varchar data type . If any of you find it as wrong approach or if you a have any easy way to fix this, please do share your comments.

Below is the procedure:

DECLARE 
userName VARCHAR2(13);  
status BOOLEAN;  
result VARCHAR2(13); 

BEGIN 
userName := ?; 
status := NULL; 
package_name.proc_name ( userName, status); 
BEGIN 
IF status THEN result := 'Yes'; 
ELSIF NOT status THEN result := 'No'; 
ELSE result := 'NULL'; 
END IF; 
END; 
COMMIT; 
? := result;
END ;

Also instead of simpleJdbcCall, i used CallableStatement for processing this. Refer below:

try{
    String wrapperProc= "DECLARE userName VARCHAR2(13);  status  BOOLEAN;  result VARCHAR2(13); BEGIN userName := ?; status := NULL; "+
        "package_name.proc_name ( userName, status ); BEGIN IF status THEN result := 'Yes'; ELSIF NOT status THEN " + 
            "result := 'No'; ELSE result := 'NULL'; END IF; END; COMMIT; ? := result;END ;";
    CallableStatement proc_stmt= null;
    proc_stmt = getTemplate().getDataSource().getConnection().prepareCall(wrapperProc);
    proc_stmt.setString(1, "userName");   
    proc_stmt.registerOutParameter(2, Types.VARCHAR);
    proc_stmt.execute();
    System.out.println("Final Result : "+proc_stmt.getString(2));
} catch(SQLException e){
    System.out.println("SQL Exception : "+e.getMessage());
    e.printStackTrace();
} catch (Exception e) {
    System.out.println("Exception : "+e.getMessage());
    e.printStackTrace();
}

~~Suriya