Oracle stored procedure returning refcursor

[Vladimir Dzyuba]

Hi!

I have already studied this topic (see
http://groups.google.com/group/mybatis-user/browse_thread/thread/aa39bf20aee5d973/123c9067f93a18bb
and http://groups.google.com/group/mybatis-user/browse_thread/thread/a1f7850dfd49b130/f16f7a3a2a620a6f),
but I'm still a bit confused (mainly because I'm a MyBatis beginner).

I have the following PL/SQL function (http://code-bin.homedns.org/
1233):
FUNCTION get_foo IS
l_cursor SYS_REFCURSOR := NULL;
BEGIN
OPEN l_cursor FOR
SELECT bar, baz
FROM foo_table;

RETURN l_cursor;
END get_foo;


This JDBC-code (boilerplate stripped) works fine (http://code-
bin.homedns.org/1234):
class Foo {
String bar;
String baz;
}

CallableStatement stmt = connection.prepareCall("{ call ? :=
GET_FOO() }");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
while (rs.next()) {
Foo foo = new Foo();
foo.bar = rs.getString(1);
foo.baz = rs.getString(2);
}

Now I try to translate it to MyBatis.
The mapper looks like this (http://code-bin.homedns.org/1235):
<mapper namespace="alpha.beta">
<resultMap id="fooMap" type="Foo">
<result property="bar" column="bar"/>
<result property="baz" column="baz"/>
</resultMap>
<select id="getFoo" statementType="CALLABLE">
{ call
#{foo,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=fooMap} :=
get_foo() }
</select>
</mapper>

And I call MyBatis like this:
List<Foo> fooList = session.selectList("alpha.beta.getFoo");

The result is an exception:
Error querying database.
Cause: org.apache.ibatis.reflection.ReflectionException:
Could not set property 'alarms' of 'class
org.apache.ibatis.reflection.MetaObject$NullObject' with value
'[alpha.beta.Foo@d3f3a3, alpha.beta.Foo@1205e0a]'
Cause: org.apache.ibatis.reflection.ReflectionException: There is no
setter for property named 'alarms' in 'class java.lang.Class'
The error may involve defaultParameterMap
The error occurred while setting parameters

The questions are:
1) Is it possible to use MyBatis like this to retrieve mapped from ref
cursors returned from db functions?
2) If not, how it can be achieved? Custom TypeHandler?

Thanks in advance!

[Jeff Butler]

The proc doesn't return a result set - the result set is an output
parameter. This is an important distinction in MyBatis. You need to
retrieve the List<Foo> from parameters - not as the return value of
the function.

Change your Java code to this:

Map<String, Object> parms = new HashMap<String, Object>();
session.selectList("alpha.beta.getFoo", parms);
List<Foo> fooList = (List<Foo>) parms.get("foo");

And add parameterType="map" to your <select> element in XML. These
changes should get you closer.

For examples from the MyBatis tests, see here:

http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibatis/submitted/refcursor/

Jeff Butler

[Vladimir Dzyuba]

Thank you, Jeff, I finally grasped the concept.

On 23 апр, 01:00, Jeff Butler <jeffgbut...@gmail.com> wrote:
> The proc doesn't return a result set - the result set is an output
> parameter.  This is an important distinction in MyBatis.  You need to
> retrieve the List<Foo> from parameters - not as the return value of
> the function.
>
> Change your Java code to this:
>
>    Map<String, Object> parms = new HashMap<String, Object>();
>    session.selectList("alpha.beta.getFoo", parms);
>    List<Foo> fooList = (List<Foo>) parms.get("foo");
>
> And add parameterType="map" to your <select> element in XML.  These
> changes should get you closer.
>
> For examples from the MyBatis tests, see here:
>

> http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibat...

>
> Jeff Butler
>
>
>
>
>
>
>
> On Fri, Apr 22, 2011 at 3:37 PM, Vladimir Dzyuba <dzyub...@gmail.com> wrote:
> > Hi!
>
> > I have already studied this topic (see

> >http://groups.google.com/group/mybatis-user/browse_thread/thread/aa39...
> > andhttp://groups.google.com/group/mybatis-user/browse_thread/thread/a1f7...),