Implementing a Stored Procedure Call with Optional Parameters in Spring 3.0

Question

I've been trying to find a way to make stored proc calls from Spring 3.0 to Oracle 11.2 with following the items in mind:

• Many of the calls are made to stored procedures that have plethora of optional parameters (parameters that have default values).
• There are stored procedures that can have a mix of: IN, OUT, and/or IN OUT.
• I do not need to handle the OUT's.

I would like to be able to call a stored proc with the needed parameters (required and/or optional). In other word, I do not wish to pass a value (not even a null) to optional parameters to my choosing (it seems like when null is being passed programmatically [not in PL/SQL though] to a parameter mapper, the default values don't get used). I have attempted to implement these invocations as many possible ways as I could but nothing has worked:

create or replace
procedure update_stored_proc (
h1 in boolean default false,
h2 in number,
h3 in varchar2 default 'H3',
h4 in varchar2 default 'H4',
h5 in varchar2 default 'H5',
h6 in out number
);

For update_stored_proc(), there is only two required parameters (h2 and h6), and four optional ones. My ultimate goal is to call update_stored_proc() by passing h1, h2 and h6. Even when I invoking the stored proc via SimpleJdbcCall with all the values set, I get an exception:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(updatingDataSource)
                    .withProcedureName("update_stored_proc")
                    .withoutProcedureColumnMetaDataAccess();

simpleJdbcCall.declareParameters(new SqlParameter("h1", OracleTypes.BOOLEAN))
              .declareParameters(new SqlParameter("h2", OracleTypes.NUMBER))
              .declareParameters(new SqlParameter("h3", OracleTypes.VARCHAR))
              .declareParameters(new SqlParameter("h4", OracleTypes.VARCHAR))
              .declareParameters(new SqlParameter("h5", OracleTypes.VARCHAR))
              .declareParameters(new SqlInOutParameter("h6", OracleTypes.NUMBER));

MapSqlParameterSource in = new MapSqlParameterSource()
            .addValue("h1", false, OracleTypes.BOOLEAN)
            .addValue("h2", 123, OracleTypes.NUMBER)
            .addValue("h3", "h3", OracleTypes.VARCHAR)
            .addValue("h4", "h4", OracleTypes.VARCHAR)
            .addValue("h5", "h5", OracleTypes.VARCHAR)
            .addValue("h6", "h6", OracleTypes.NUMBER);

simpleJdbcCall.compile();
simpleJdbcCall.execute(in);

The exception I get indicates that the column type is somehow invalid:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call update_stored_proc(?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

I have replaced OracleTypes with Types and even taken out withoutProcedureColumnMetaDataAccess() but the error persists.

Answer 1

This question turned out to require several solutions to get to work. First and foremost, Oracle's implementation of SQL standards does not contain BOOLEAN type, even though its propriety PL/SQL script language does support it (more reason for the DBA's not to use BOOLEAN type in their stored procedures). The solution I came up with was to use an anonymous block, declare a local variable, and assign it to the BOOLEAN parameter:

DECLARE
  h1_false BOOLEAN := false;
  h6_ NUMBER;
BEGIN
  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_
  );
END;

Note that I do not care about the value of h1 (and for that matter, the OUT parameter/h6) parameter for my particular functionality, but I could imagine by having a simple IF THEN ELSE statement, one can assign a binded parameter to h1, i.e.

DECLARE
  h1_false BOOLEAN;
  h6_ NUMBER;
BEGIN
  IF :h1 THEN
    h1_false = true;
  ELSE
    h1_false = false;
  END IF;

  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_
  );
END;

The second issue is the way Spring handles the optional parameters. If one to declare the optional parameters, then Spring's SimpleJdbcCall -- and StoredProcedure for that matter -- expects values for those parameters. Therefore, one must take care of these values when they become available. Otherwise, when the optional parameter does not have a value, you must pass NULL which will not trigger the DEFAULT value of the PL/SQL's parameter to be used. This means that your query string (in this case an anonymous PL/SQL block) must be generated dynamically. So, the anonymous block becomes:

DECLARE
  h1_false BOOLEAN := false;
  h6_ NUMBER;
BEGIN
  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_

I switched to StoredProcedure solution rather than SimpleJdbcCall, as it turned out to be more simpler. Albeit I must add that I had to extend StoredProcedure to create a common class for my stored procedure classes and use that extended class for my customized stored procedure classes. In your StoredProcedure class, you only declare the required parameters (make sure not to compile() the query at this point):

declareParameter(new SqlParameter("h2", OracleTypes.NUMBER));

Note: If you do not need the OUT parameter, like in my case, do not include it in your declaration. Otherwise, assign a binding variable, i.e. h6 => :h6 in the anonymous block and declare it in your StoredProcedure, i.e. declareParameter(new SqlOutParameter("h6", OracleTypes.NUMBER)); and make sure to get :h6 value out when execute() returns a Map<String, Object>. If your OUT value is of BOOLEAN type, then I do not know how to retrieve the value.

The remaining optional parameters should be dynamically constructed in your StoredProcedure. i.e.:

if (someObj.getH3() != null) {
  declareParameter(new SqlParameter("h3", OracleTypes.VARCHAR));
  paramMap.put("h3", someObj.getH3());
  anonymousPLSQLBlockQueryString += " , h3 => :h3";
}

where paramMap represents a Map that is going to be passed to StoredProcedure#execute(paramMap). You do the same with h4 and h5, and at the end, you have to make sure to properly close the anonymous block query string, i.e.:

anonymousPLSQLBlockQueryString += " ); END;";
setSql(anonymousPLSQLBlockQueryString);
setSqlReadyForUse(true);
compile();