I need to call an Oracle Stored Procedure that has an input argument with a nested table type, using Mybatis.
I cannot find any documentation or example concerning this particular usage of MyBatis.
Has anyone done this before, or seen an example?
Many thanks.
Let's take this example:
PROCEDURE PROCEDURERECORD (P_VAL_REC IN Package.RECORD ,P_VAL_NUM IN VARCHAR2 ,P_DAT_VAL OUT DATE );
In your database redefine the SP:
PROCEDURE PROCEDURERECORD_NEW (P_VAL_REC IN RECORD_TYPE (you create it in Types) ,P_VAL_NUM IN VARCHAR2 ,P_DAT_VAL OUT DATE );
you should reconfigure you SP bean with spring :
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
<bean id="PROCEDURERECORD_NEW" parent="storedProcedure">
<constructor-arg index="0" value="PROCEDURERECORD" />
<constructor-arg index="1" value="false" />
<property name="params">
<list>
<bean parent="sqlRecordParamIn">
<constructor-arg index="0" value="P_VAL_REC" />
<constructor-arg index="2" value="RECORD_TYPE" />
</bean>
<bean parent="sqlNumericParamIn">
<constructor-arg value="P_VAL_NUM" />
</bean>
<bean parent="sqlDateParamOut">
<constructor-arg value="P_DAT_VAL"/>
</bean>
</list>
</property>
</bean>
In your implementation code, use SqlStructValue like this:
@Override
public DateTime getSpReturn(RecordClass record,Long valNum){
Map<String, Object> args = new HashMap<String, Object>();
args.put("P_VAL_REC", new SqlStructValue<RecordClass>(record,new RecordClassMapper()));
args.put("P_VAL_NUM", valNum);
Map<String, Object> result = procedureRecordNew.execute(args);
return (DateTime)result.get("P_DAT_VAL");
}
As for the mapper you create it like this:
@Component("RecordClassMapper")
public class RecordClassMapper implements StructMapper<RecordClass> {
@Override
public STRUCT toStruct(RecordClass source, Connection conn, String typeName) throws SQLException {
Object[] objectProperties = new Object[] { new source.getrecordatr1(), source.getrecordatr2(), source.getrecordatr3() };
return new STRUCT(new StructDescriptor(typeName, conn), conn, objectProperties);
}
@Override
public RecordClass fromStruct(STRUCT struct) throws SQLException {
// Auto-generated method stub
throw new UnsupportedOperationException("Not implemented");
}
}
