java - Implementing a Stored Procedure Call with Optional Parameters in Spring 3.0 -

i've been trying find way make stored proc calls spring 3.0 oracle 11.2 following items in mind:

• many of calls made stored procedures have plethora of optional parameters (parameters have default values).
• there stored procedures can have mix of: in, out, and/or in out.
• i not need handle out's.

i able call stored proc needed parameters (required and/or optional). in other word, do not wish pass value (not null) optional parameters choosing (it seems when null being passed programmatically [not in pl/sql though] parameter mapper, default values don't used). have attempted implement these invocations many possible ways 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 2 required parameters (h2 , h6), , 4 optional ones. ultimate goal call update_stored_proc() passing h1, h2 , h6. when invoking stored proc via simplejdbccall values set, 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 indicates column type somehow invalid:

org.springframework.jdbc.uncategorizedsqlexception: callablestatementcallback; uncategorized sqlexception sql [{call update_stored_proc(?, ?, ?, ?, ?, ?)}]; sql state [99999]; error code [17004]; invalid column type; nested exception java.sql.sqlexception: invalid column type 

i have replaced oracletypes types , taken out withoutprocedurecolumnmetadataaccess() error persists.

this question turned out require several solutions work. first , foremost, oracle's implementation of sql standards not contain boolean type, though propriety pl/sql script language support (more reason dba's not use boolean type in stored procedures). solution came use anonymous block, declare local variable, , assign boolean parameter:

declare   h1_false boolean := false;   h6_ number; begin   update_stored_proc(   h1 => h1_false,   h2 => :h2,   h6 => h6_   ); end; 

note not care value of h1 (and matter, out parameter/h6) parameter particular functionality, imagine having simple if else statement, 1 can assign binded parameter h1, i.e.

declare   h1_false boolean;   h6_ number; begin   if :h1     h1_false = true;   else     h1_false = false;   end if;    update_stored_proc(   h1 => h1_false,   h2 => :h2,   h6 => h6_   ); end; 

the second issue way spring handles optional parameters. if 1 declare optional parameters, spring's simplejdbccall -- , storedprocedure matter -- expects values parameters. therefore, 1 must take care of these values when become available. otherwise, when optional parameter not have value, must pass null will not trigger default value of pl/sql's parameter used. means query string (in case anonymous pl/sql block) must generated dynamically. so, anonymous block becomes:

declare   h1_false boolean := false;   h6_ number; begin   update_stored_proc(   h1 => h1_false,   h2 => :h2,   h6 => h6_ 

i switched storedprocedure solution rather simplejdbccall, turned out more simpler. albeit must add had extend storedprocedure create common class stored procedure classes , use extended class customized stored procedure classes. in storedprocedure class, declare required parameters (make sure not compile() query @ point):

declareparameter(new sqlparameter("h2", oracletypes.number)); 

note: if not need out parameter, in case, not include in declaration. otherwise, assign binding variable, i.e. h6 => :h6 in anonymous block , declare in storedprocedure, i.e. declareparameter(new sqloutparameter("h6", oracletypes.number)); , make sure :h6 value out when execute() returns map<string, object>. if out value of boolean type, not know how retrieve value.

the remaining optional parameters should dynamically constructed in storedprocedure. i.e.:

if (someobj.geth3() != null) {   declareparameter(new sqlparameter("h3", oracletypes.varchar));   parammap.put("h3", someobj.geth3());   anonymousplsqlblockquerystring += " , h3 => :h3"; } 

where parammap represents map going passed storedprocedure#execute(parammap). same h4 , h5, , @ end, have make sure close anonymous block query string, i.e.:

anonymousplsqlblockquerystring += " ); end;"; setsql(anonymousplsqlblockquerystring); setsqlreadyforuse(true); compile();