oracle11g - How to Implement Spring Integration int-jdbc:stored-proc-outbound-gateway with advanced Oracle type IN parameter -

i trying implement int-jdbc:stored-proc-outbound-gateway executes oracle store procedure advanced oracle type in parameter.

i not clear how declare advanced type in parameter.

the code , configuration bellow.

create or replace procedure poc2  ( d_list in move_audit, db_response out varchar2) begin ..... end poc2;  create or replace type move_audit object (  move_id  varchar2(50),  message  varchar2(50) ); create or replace type move_audit_tab table of move_audit; 

stored-proc-outbound-gateway configuration:

<int-jdbc:stored-proc-outbound-gateway         id="inparamstorprocgateway" data-source="datasource"         request-channel="requestchannel" is-function="false"         reply-channel="replychannel" stored-procedure-name="poc2"         expect-single-result="false" ignore-column-meta-data="true">         <int-jdbc:sql-parameter-definition             name="d_list" direction="in" type="struct" type-name="move_audit_tab" return-type="sqlreturnstruct"/>         <int-jdbc:sql-parameter-definition name="db_response" direction="out" type="varchar"/>         <int-jdbc:parameter name="d_list" expression="payload"/> </int-jdbc:stored-proc-outbound-gateway>  <bean id="sqlreturnstructarrayconfirmputway"    class="org.springframework.data.jdbc.support.oracle.sqlreturnstructarray">     <constructor-arg name="mapper" ref="confirmputawaystructmapper"/> </bean> <bean id="confirmputawaystructmapper" class="com.test.mapper.confirmdomainstructmapper"/> 

bellow struct mapper:

public class confirmdomainstructmapper implements structmapper<confirmdomain> {    public struct tostruct(confirmdomain odemodomainobject, connection conn, string typename)       throws sqlexception {     structdescriptor descriptor = new structdescriptor(typename, conn);     object[] values = new object[2];     values[0] = odemodomainobject.getid();     values[1] = odemodomainobject.getname();     return new struct(descriptor, conn, values);   }    public confirmdomain fromstruct(struct struct) throws sqlexception {     confirmdomain odemodomainobject=null;     if(null!=struct){       odemodomainobject=new confirmdomain();       datum[] resarray=struct.getoracleattributes();       odemodomainobject.setid(resarray[0].stringvalue());       odemodomainobject.setname(resarray[1].stringvalue());     }     return odemodomainobject;   }  } 

i tried , debugged code not call tostruct method of structmapper.

from log got d_list passed null

first of don't understand how app runs @ all, because of:

else {     parameterbuilder = beandefinitionbuilder.genericbeandefinition(sqlparameter.class);     if (stringutils.hastext(returntype)) {         parsercontext.getreadercontext().error("'return-type' attribute can't provided " +                             "for in 'sql-parameter-definition' element.", storedproccomponent);     } } 

so, return-type can specified out params.

another issue proc definition. can guess should have d_list in type move_audit_tab, because, think, going store several object @ once.

so, forget return-type , confirmdomainstructmapper, beucase makes sence when want read out params.

this <int-jdbc:parameter name="d_list" value="payload"/> if payload object of custom oralce type.

from java perspective is table of type called array. should use oracle jdbc native code create it.

i did once , looks like:

<transformer expression="@oraclearraycreator.create(payload, '${scheme.name}.move_audit_tab', '${scheme.name}.move_audit')"/> 

pay atention, please, type must outside of package. otherwise oracle jdbc driver can't resolve them.

@service @org.springframework.context.annotation.lazy class oraclearraycreator {      @autowired     datasource datasource      @autowired     properties internalproperties      array create(list<map> data, string tablename, string recordname) {         def recordkeys = internalproperties.getproperty(recordname.replacefirst(/.*\./, '')).split(',')         def connection = nativeconnection         def structdescriptor = structdescriptor.createdescriptor(recordname, connection)          def dataarray = []          data.each { recordmap ->             def record = []             if (recordmap) {                 recordkeys.each {                     record << recordmap[it]                 }                 dataarray << new struct(structdescriptor, connection, record.toarray())             }         }          new array(arraydescriptor.createdescriptor(tablename, connection), connection, dataarray.toarray())     }       array create(map data, string tablename, string recordname) {         create([data], tablename, recordname)     }      clob converttoclob(string value) {         clob c = clob.createtemporary(nativeconnection, false, clob.duration_session)         c.setstring(1l, value)         return c     }      connection getnativeconnection() {         datasourceutils.getconnection(datasource).metadata.connection     }  } 

(sorry groovy code).

and place <transformer> before <int-jdbc:stored-proc-outbound-gateway>. of course, can wrap them both <chain>.

and yes, type="array" should d_list param.