Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I call a stored procedure and the parameter is Oracle Object Type
How do I call a stored procedure and the parameter is Oracle Object Type
Added by Yan Liang, last edited by Haikal Saadh on May 23, 2008  (view change)
Labels: 
(None)


create or replace type warrant.test_rshan_typ as object(id VARCHAR2(10),dat VARCHAR2(50));
    
    create or replace type warrant.test_rshan_typ_arr as table of warrant.test_rshan_typ;
    
    create or replace procedure warrant.shan_test_array(arr in test_rshan_typ_arr,isSuccess out varchar2 )
    as
    begin
    for r in 1..arr.count loop
    insert into test_rshan(id, dat) values(arr(r).id,arr(r).dat);
    if MOD(r,20)=0 then --for transaction control...
    commit;
    end if;
    
    end loop;
    commit;
    isSuccess:='success';
    exception
    when others then
    rollback;
    isSuccess:='unsuccess';
    end shan_test_array;

iBATIS:

<parameterMap class="map" id="shanTestParameters">
    <parameter property="arr" jdbcType="oracleARRAY" javaType="OBJECT" mode="IN"/>
    <parameter property="isSuccess" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
    </parameterMap>
    
    <procedure id="shanTest" parameterMap="shanTestParameters" >
    <![CDATA[ {call warrant.shan_test_array(?,?)}]]>
    </procedure>

Java代码:

public static void test() throws SQLException {
    
    Map map = new HashMap();
    Object[][] vals = { { "aaa", "bbb" }, { "aaa1", "bbb1" },
    { "aaa2", "bbb2" }, { "aaa3", "bbb3" }, { "aaa4", "bbb4" },
    { "aaa5", "bbb5" } };
    map.put("arr", vals);
    
    try {
    sqlMapper.startTransaction();
    sqlMapper.insert("shanTest", map);
    sqlMapper.commitTransaction();
    } catch (SQLException e) {
    e.printStackTrace();
    } finally {
    try {
    sqlMapper.endTransaction();
    } catch (SQLException e) {
    // TODO Auto-generated catch block
                e.printStackTrace();
    }
    }
    }

Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators