Oracle stored procedure with custom data types

[George Chelidze]

Hello,

It's my first post to this list, so I'd like to say hello to all of you.
I have a procedure:

CREATE OR REPLACE PROCEDURE "S"."PREPROCPROC"(aInput in 
TPreprocInputMessageSet, aOutput out TPreprocOutputMessageSet) is
res TPreprocOutputMessageSet;
curi TPreprocInputMessage;
curo TPreprocOutputMessage;
begin
 res:=TPreprocOutputMessageSet();
 res.extend(aInput.Count);

 FOR i IN aInput.First .. aInput.Last
 LOOP
   curi:=aInput(i);
   curo:=TPreprocOutputMessage(null,null,null);
   curo.FieldA := curi.FieldA;
   curo.FieldB := curi.FieldB;
   curo.FieldC := null;
   res(i):=curo;
 END LOOP;
 aOutput:=res;
 res.delete;
end PreprocProc;

and the following custom types:
--
CREATE OR REPLACE TYPE TPREPROCINPUTMESSAGESET AS TABLE OF 
S.TPREPROCINPUTMESSAGE
GO
--
CREATE OR REPLACE TYPE TPREPROCOUTPUTMESSAGESET AS TABLE OF 
B1.TPREPROCOUTPUTMESSAGE
GO
--
CREATE OR REPLACE TYPE TPREPROCINPUTMESSAGE as object (
   FieldA varchar2(32),
   FieldB varchar2(32)
)
GO
--
CREATE OR REPLACE TYPE TPREPROCOUTPUTMESSAGE as object (
   FieldA varchar2(32),
   FieldB varchar2(32),
   FieldC varchar2(32)
)
GO
--
I have attached Main.java, TPreprocTypeHandler.java, SqlMapConfig.xml, 
SqlMapProcedure.xml files. The main problem  that I have is that in 
TPreprocTypeHandler.getResult(ResultGetter getter) method, 
getter.getObject().getArray() returns Object[] which contains instances 
of STRUCT not TPreprocInputMessage, so I have to map them to 
TPreprocInputMessage myself. Is it possible to tell ibatis to return 
instances of TPreprocInputMessage instead of instances of STRUCT?

Thanks in advance,

George

package ibatis;

import com.ibatis.sqlmap.client.SqlMapClient;
import ibatis.datatypes.TPreprocInputMessage;
import ibatis.datatypes.TPreprocInputMessageSet;
import ibatis.datatypes.TPreprocOutputMessage;
import ibatis.datatypes.TPreprocOutputMessageSet;
import ibatis.util.DBConnector;
import java.util.HashMap;

public class Main {
    public static void main(String[] args) {
        try {
            SqlMapClient sqlMapClient = DBConnector.getSqlMapClient();
            TPreprocInputMessage[] inMessages = new TPreprocInputMessage[10];
            for (int i = 0; i < 10; i++) {
                inMessages[i] = new TPreprocInputMessage(null, null);
            }
            TPreprocInputMessageSet inMessageSet = new TPreprocInputMessageSet(inMessages);
            TPreprocOutputMessageSet outMessageSet = new TPreprocOutputMessageSet();
            HashMap params = new HashMap();
            params.put("inMessageSet", inMessageSet);
            params.put("outMessageSet", outMessageSet);
            sqlMapClient.queryForObject("Procedure.PreprocProc", params);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "sql-map-config-2.dtd">

<sqlMapConfig>
    <properties resource="ibatis/util/SqlMap.properties"/>
    <settings
        cacheModelsEnabled="true"
        enhancementEnabled="true"
        lazyLoadingEnabled="true"
        maxRequests="64"
        maxSessions="10"
        maxTransactions="5"
        useStatementNamespaces="true"
        defaultStatementTimeout="5"
    />

    <typeAlias alias="TPreprocTypeHandler" type="ibatis.handler.TPreprocTypeHandler"/>
    
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${username}"/>
            <property name="JDBC.Password" value="${password}"/>
            <property name="JDBC.DefaultAutoCommit" value="true" />
        </dataSource>
    </transactionManager>
    <sqlMap resource="ibatis/util/SqlMapProcedure.xml"/>
</sqlMapConfig>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "sql-map-2.dtd">

<sqlMap namespace="Procedure">
    <parameterMap id="params" class="map">
        <parameter property="inMessageSet" jdbcType="TPREPROCINPUTMESSAGESET" javaType="ibatis.datatypes.TPreprocInputMessageSet" typeName="TPREPROCINPUTMESSAGESET" typeHandler="TPreprocTypeHandler" mode="IN"/>
        <parameter property="outMessageSet" jdbcType="TPREPROCOUTPUTMESSAGESET" javaType="ibatis.datatypes.TPreprocOutputMessageSet" typeName="TPREPROCOUTPUTMESSAGESET" typeHandler="TPreprocTypeHandler" mode="OUT"/>
    </parameterMap>
    <procedure id="PreprocProc" parameterMap="params">
        {call PREPROCPROC(?,?)}
    </procedure>
</sqlMap>
package ibatis.handler;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
import java.sql.SQLException;
import java.sql.Types;

public class TPreprocTypeHandler implements TypeHandlerCallback {
    static {
        JdbcTypeRegistry.setType("TPREPROCOINPUTMESSAGESET", Types.ARRAY);
        JdbcTypeRegistry.setType("TPREPROCOUTPUTMESSAGESET", Types.ARRAY);
    };
   
    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
        setter.setObject(parameter);
    }
   
    public Object getResult(ResultGetter getter) throws SQLException {
       return getter.getObject();
    }
   
    public Object valueOf(String s) {
        return null;
    }
}