java - Pass array as input parameter to an oracle stored procedure using simple jdbc call -

java - Pass array as input parameter to an oracle stored procedure using simple jdbc call -

i next link http://docs.spring.io/spring-data/jdbc/docs/1.1.0.release/reference/html/orcl.datatypes.html#d0e2387

here oracle procedure specifications

create or replace bundle pkg_re_fi procedure prc_re_fi_details(p_fan_no in varchar2, p_ref_id in ty_app_ref_id, p_comments in varchar2, p_billing_flag in varchar2, p_status out varchar2);

end pkg_re_fi;ty_app_ref_id

create or replace type ty_app_ref_id varray(500) of obj_array_ref_id create or replace type obj_array_ref_id object( app_ref_id varchar2(100) )

i using spring jdbc framework(simplejdbccall object) execute above procedure. below code snippet in have declared

this.refijdbccall = new simplejdbccall(datasource).withcatalogname("pkg_re_fi"). withprocedurename("prc_re_fi_details").withoutprocedurecolumnmetadataaccess().declareparameters(new sqlparameter("p_fan_no", types.varchar), new sqlparameter("p_ref_id", oracletypes.array, "ty_app_ref_id"), new sqlparameter("p_comments", types.varchar), new sqlparameter("p_billing_flag", types.varchar), new sqloutparameter("p_status", types.varchar) ); @override public refiresponse reinitiatefi(refirequest refirequest) { mapsqlparametersource in = new mapsqlparametersource(); // map in = collections.singletonmap("in_actor_ids", new sqlarrayvalue(ids)); in.addvalue("p_fan_no",refirequest.getfanno()); string[] refids = new string[refirequest.getapplicantreferenceid().size()]; refids = refirequest.getapplicantreferenceid().toarray(refids); in.addvalue("p_ref_id", new sqlarrayvalue(refids)); in.addvalue("p_comments", refirequest.getcomments()); in.addvalue("p_billing_flag", refirequest.getfibillingflag()); map result = refijdbccall.execute(in); string status = (string)result.get("p_status"); homecoming null; }

i next error

exception in thread "main" org.springframework.jdbc.uncategorizedsqlexception: callablestatementcallback; uncategorized sqlexception sql [{call pkg_re_fi.prc_re_fi_details(?, ?, ?, ?, ?)}]; sql state [null]; error code [17059]; fail convert internal representation: tmfi10000031a; nested exception java.sql.sqlexception: fail convert internal representation: tmfi10000031a @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:83) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:969) @ org.springframework.jdbc.core.jdbctemplate.call(jdbctemplate.java:1003) @ org.springframework.jdbc.core.simple.abstractjdbccall.executecallinternal(abstractjdbccall.java:391) @ org.springframework.jdbc.core.simple.abstractjdbccall.doexecute(abstractjdbccall.java:354) @ org.springframework.jdbc.core.simple.simplejdbccall.execute(simplejdbccall.java:181) @ com.tcs.fi.dao.refidaoimpl.reinitiatefi(refidaoimpl.java:77) @ com.tcs.fi.business.fireinitiator.reinitiatefi(fireinitiator.java:57) @ com.tcs.fi.business.fireinitiator.reinitiatefi(fireinitiator.java:39) @ com.tcs.fi.business.test.main(test.java:11) caused by: java.sql.sqlexception: fail convert internal representation: tmfi10000031a @ oracle.jdbc.driver.databaseerror.throwsqlexception(databaseerror.java:112) @ oracle.jdbc.driver.databaseerror.throwsqlexception(databaseerror.java:146) @ oracle.jdbc.oracore.oracletypeadt.todatum(oracletypeadt.java:239) @ oracle.jdbc.oracore.oracletypeadt.todatumarray(oracletypeadt.java:274) @ oracle.jdbc.oracore.oracletypeupt.todatumarray(oracletypeupt.java:115) @ oracle.sql.arraydescriptor.tooraclearray(arraydescriptor.java:1314) @ oracle.sql.array.<init>(array.java:152) @ org.springframework.data.jdbc.support.oracle.sqlarrayvalue.createtypevalue(sqlarrayvalue.java:91) @ org.springframework.jdbc.core.support.abstractsqltypevalue.settypevalue(abstractsqltypevalue.java:58) @ org.springframework.jdbc.core.statementcreatorutils.setvalue(statementcreatorutils.java:267) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalueinternal(statementcreatorutils.java:216) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalue(statementcreatorutils.java:127) @ org.springframework.jdbc.core.callablestatementcreatorfactory$callablestatementcreatorimpl.createcallablestatement(callablestatementcreatorfactory.java:212) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:947)

kindly help.

i solved using

map in = new hashmap(); in.put("p_fan_no",refirequest.getfanno()); object[][] refids = new string[refirequest.getapplicantreferenceid().size()][1]; for(int = 0 ; < refirequest.getapplicantreferenceid().size() ; i++){ refids[i][0] = refirequest.getapplicantreferenceid().get(i); } in.put("p_ref_id", new sqlarrayvalue(refids)); in.put("p_comments", refirequest.getcomments()); in.put("p_billing_flag", refirequest.getfibillingflag()); map result = refijdbccall.execute(in); string status = (string)result.get("p_status");

seems expecting 2d-array.

java spring stored-procedures spring-jdbc