How do I call a stored procedure and the parameter is Oracle RECO RD Type

["Leonard, Joe" <Jo...@fmr.com>]

Hi all,

I am trying to so something that is very very similar to a solution posted
in the iBATIS FAQ, where that solution describes how to call a stored
procedure and the parameter is Oracle OBJECT Type: 
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call+
a+stored+procedure+and+the+parameter+is+Oracle+Object+Type
<http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call
+a+stored+procedure+and+the+parameter+is+Oracle+Object+Type> 

I am trying to do something quite similar, but with little success so far.
My only difference is that my Oracle type is a RECORD and not an OBJECT.

This is what my PL/SQL looks like:


  Type test_my_type IS RECORD(id VARCHAR2(10),dat VARCHAR2(50));
  type test_my_type_arr IS TABLE OF test_my_type;  


  PROCEDURE PRC_CREATE_COMPONENT(subjects IN test_my_type_arr,
                                 researchComponentType IN VARCHAR,
                                 result OUT VARCHAR )
    IS        
    BEGIN
        result := researchComponentType;
    EXCEPTION
        WHEN OTHERS THEN RAISE;
    END PRC_CREATE_COMPONENT;
   


My iBATIS mapping is as follows:

	<parameterMap class="map" id="createComponentParams">
		<parameter property="subjects" jdbcType="oracleARRAY"
javaType="OBJECT" mode="IN"/>
		<parameter property="researchComponentType"
jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="result" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT"/>	  
	</parameterMap>		
		
	<procedure id="createComponent" parameterMap="createComponentParams"
>
		{call PKG_CRS_TMP.PRC_CREATE_COMPONENT(?, ?)}
	</procedure> 



And my Java:

	Map map = new HashMap();
	Object[][] vals = { { "aaa", "bbb" }, { "aaa1", "bbb1" },   
	     			{ "aaa2", "bbb2" }, { "aaa3", "bbb3" }, {
"aaa4", "bbb4" },   
	     			{ "aaa5", "bbb5" } }; 
		
		
	map.put("subjects", vals);
	map.put("researchComponentType", "QuickNote");
		
	SqlMapClientTemplate theSqlMapClientTemplate =
getSqlMapClientTemplate();
	theSqlMapClientTemplate.queryForObject("createComponent", map);

	System.out.println(map.get("result"));



When I execute all this, I get the following "Invalid column type"
exception:

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation:
encountered SQLException [  
--- The error occurred in com/fmr/fimt/crs/dao/ibatis/maps/CRSDaoSQLMap.xml.

--- The error occurred while applying a parameter map.  
--- Check the CRS.createComponentParams.  
--- Check the parameter mapping for the 'subjects' property.  
--- Cause: java.sql.SQLException: Invalid column type]; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/fmr/fimt/crs/dao/ibatis/maps/CRSDaoSQLMap.xml.

--- The error occurred while applying a parameter map.  
--- Check the CRS.createComponentParams.  
--- Check the parameter mapping for the 'subjects' property.  
--- Cause: java.sql.SQLException: Invalid column type




I'm hoping someone might have some ideas on this? My code is almost
identical to the solution posted in the FAQ, with RECORD replacing OBJECT.
Perhaps iBatis does not know how to handle RECORD types?


Thanks in advance,

Joe.