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

Comments

Post a Comment

Popular posts from this blog

java Multi query from Mysql using netbeans -

java Multi query from Mysql using netbeans - i have java code retrieve info mysql appropriate code in netbeans.the code below. want multi query such illustration finding total number of products , on.could help me please. in advance import java.sql.*; public class javamysqlselectexample { public static void main(string[] args) { seek { string mydriver = "com.mysql.jdbc.driver"; string myurl = "jdbc:mysql://localhost/sample"; class.forname(mydriver); connection conn = drivermanager.getconnection(myurl, "root", "mypasscode1"); string query = "select * products"; statement st = conn.createstatement(); resultset rs = st.executequery(query); while (rs.next()==true) { int code = rs.getint("code"); string productname = rs.getstring("product_name"); string producttype = rs.getstring("product_type"); system.out.format(" %d, %s, %s\n", code, pr
Read more

c# - DotNetZip fails with "stream does not support seek operations" -

c# - DotNetZip fails with "stream does not support seek operations" - i using dotnetzip in c# unzip stream follows: public static void unzipfromstream(stream stream, string outdir) { //omit seek grab block using (zipfile zip = zipfile.read(stream)){ foreach (zipentry e in zip){ e.extract(outdir, extractexistingfileaction.overwritesilently); } } } stream obtained using webclient client = new webclient(); stream fs = client.openread(url); however, got next exception exception during extracting zip stream system.notsupportedexception: stream not back upwards seek operations. @ system.net.connectstream.get_position() @ ionic.zip.zipfile.read(stream zipstream, textwriter statusmessagewriter, encoding encoding, eventhandler`1 readprogress) on server side(asp.net mvc 4), returning filepathresult or filestreamresult both caused exception. should obtain stream differently on client side? or how create server homec
Read more

c++ - StartServiceCtrlDispatcher don't can access 1063 error -

c++ - StartServiceCtrlDispatcher don't can access 1063 error - i write on c++ service , have error, , don't can fixed it. my service main function int main (int argc, tchar *argv[]) {dword f; for(int i=0;i<113;i++) f = getlasterror(); servicepath = lptstr(argv[0]); outputdebugstring(_t("my sample service: main: entry")); //installservice(); service_table_entry servicetable[] = { {service_name, (lpservice_main_function) servicemain}, {null, null} }; // startservice(); if(!startservicectrldispatcher(servicetable)) { f = getlasterror(); //addlogmessage("error: startservicectrldispatcher"); } else if( memcmp(argv[argc-1],"install",7)) { installservice(); } else if( memcmp(argv[argc-1],"remove",6)) { removeservice(); } else if( memcmp(argv[argc-1],"start",5)) { startservice(); } else if( memcmp(argv[argc-1],"stop",4))
Read more