Re: calling stored functions

[bob robertson]

Thanks for the help everyone, especially Mike.

 

I'm trying out the approach you suggested where no TypeHandler is required. But something is not working. My data is being inserted OK, but I'm getting nothing back. I've tested my stored func (Oracle 9i) and this works being called from another PLSQL prog.

 

The signature is:

 

CREATE OR REPLACE function myRefCursorFunction(userId in numeric)

return sys_refcursor

-- inserts a new record and returns ALL rows in the table, for testing

 

Maps is, as per the example from Mike:

<resultMap id="myResultMap" class="MyClass" >
    <result property="id" column="RECORD_ID" />
    <result property="name" column="NAME" />
    <result property="address" column="ADDRESS" />
</resultMap>

<parameterMap id="myParamMap" class="map" >
       <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
       <parameter property="userId" jdbcType="NUMERIC" mode="IN"/>
</parameterMap>

<procedure id="mycursorfunction" parameterMap="myParamMap"
resultMap="myResultMap" >
        { ? = call myRefCursorFunction(? ) }
</procedure>

My unit test (within Spring, ibatis 2.1.7):

 

            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
            Map map = new HashMap();
            map.put("userId", "123456");
            List list = (List)sqlMap.queryForList("mycursorfunction", map);

            logger.debug(list);

09:16:31.032 DEBUG [TestSqlMaps]- results: []

 

An empty array! This table is not empty, and data was inserted. Calling the PL/SQL directly returns a non-empty cursor. My SqlMap conf ig is 100% correct. Honest.

 

Any ideas? Or what steps do the experts normally take to debug such things.

 

---

Telefonieren Sie ohne weitere Kosten mit Ihren Freunden von PC zu PC!
Jetzt Yahoo! Messenger installieren!