Oracle stored procedure returning cursor failing

[daver]

I'm sure this is a simple mapping issue but I'm struggling with it and
can't seem to find the right combination. The docs and user guide are
very thin on this sort of mapping.

Here's the error:

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause:
org.apache.ibatis.reflection.ReflectionException: Could not set
property 'result_cursor' of '24' with value
'[mypkg.RawMessage@11563ff]' Cause:
org.apache.ibatis.reflection.ReflectionException: There is no setter
for property named 'result_cursor' in 'class java.lang.Integer'
### The error may involve mypkg.RawMessageMapper.findById-Inline
### The error occurred while setting parameters
### Cause: org.apache.ibatis.reflection.ReflectionException: Could not
set property 'result_cursor' of '24' with value
'[mypkg.RawMessage@11563ff]' Cause:
org.apache.ibatis.reflection.ReflectionException: There is no setter
for property named 'result_cursor' in 'class java.lang.Integer'
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
82)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
at $Proxy1.findById(Unknown Source)

My Mapper interface (dead simple):

public interface RawMessageMapper {
/**
* Locate a message by primary key
* @param messageId
* @return
*/
public RawMessage findById(int messageId);
}

My stored proc to pull the data:

CREATE OR REPLACE FUNCTION get_message_by_id(v_id IN INT)
RETURN SYS_REFCURSOR is result_cursor SYS_REFCURSOR;
BEGIN
OPEN result_cursor FOR
SELECT * FROM my_message WHERE message_id = v_id;
RETURN result_cursor;
END;
/

My mapper file:

<mapper namespace="mypkg.RawMessageMapper">
<resultMap id="RawMessageOut" type="mypkg.RawMessage">
<id property="id" column="MESSAGE_ID" />
<result property="rawMessageContent" column="CONTENT" />
<result property="createdBy" column="CREATE_USER" />
<result property="createdDate" column="CREATE_TIMESTAMP" />
<result property="lastUpdatedBy" column="UPDATE_USER" />
<result property="lastUpdatedDate" column="UPDATE_TIMESTAMP" /
>
</resultMap>
<select statementType="CALLABLE" id="findById"
resultMap="RawMessageOut"
parameterType="int"
resultType="object">
{ call
#{result_cursor,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=RawMessageOut} :=
get_message_by_id (
#{messageId}
)}
</select>

This is MyBatis 3.0.1GA, Java6. Error comes from JUnit test, not that
it's relevant.

Changing the mapper class to return a List<RawMessage> doesn't change
anything either. Leaving off the name (since this is a return value,
not an actual parameter), returns this error instead:

### Error querying database. Cause:
org.apache.ibatis.executor.ExecutorException: There was no TypeHandler
found for parameter mode=OUT of statement
mypkg.RawMessageMapper.findById
### The error may involve mypkg.RawMessageMapper.findById-Inline
### The error occurred while setting parameters
### Cause: org.apache.ibatis.executor.ExecutorException: There was no
TypeHandler found for parameter mode=OUT of
mypkg.RawMessageMapper.findById
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
82)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
at $Proxy1.findById(Unknown Source)

What I am missing or have wrong on my map?

[daver]

Some more info...I cleaned up the mode=OUT and the name of the
parameter, both of which seemed silly for a return type. New map:

<select statementType="CALLABLE" id="findById"

resultType="object"
parameterType="int">
{ call
#{jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=RawMessageOut} :=
get_message_by_id(
#{messageId}
)}
</select>

I've played with a variety of configs on that return type (resultMap,
resultType, values in their, etc).

The error is similar...

### Error querying database. Cause:
org.apache.ibatis.executor.ExecutorException: There was no TypeHandler

found for parameter jdbcType=CURSOR of statement

mypkg.RawMessageMapper.findById
### The error may involve mypkg.RawMessageMapper.findById-Inline
### The error occurred while setting parameters
### Cause: org.apache.ibatis.executor.ExecutorException: There was no

TypeHandler found for parameter jdbcType=CURSOR of statement
com.westernasset.gso.framework.message.core.data.GSORawMessageDAO.findById

at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
at

org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:
38)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
66)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
at $Proxy1.findById(Unknown Source)

Clearly THAT mapper must exist, so this is something that's definitely
off in either my map or the code...Any ideas?

[Guy Rouillier]

I found an example similar to yours here:
http://www.enterprisedt.com/publications/oracle/result_set.html.
Strangely, you are both implementing a stored *function* instead of a
stored *procedure*. I didn't think call worked with functions, but
apparently so.

Do you have this working with straight JDBC? From the error message, I'd
say that iBATIS doesn't know how to map a JDBC column type of CURSOR
into Java. As it is requesting, try supplying a TypeHandler for that
JDBC type.

--
Guy Rouillier

[daver]

Hi Guy,

As the documentation states that jdbcType=CURSOR is supported, seems
like the last suggestion is superfluous (p. 33 of the user guide).

Interestingly enough, I used that example you found as the guide for
my own...Changing from FUNCTION to PROCEDURE created a syntax error
that didn't make sense...I'm no DBA and my PL/SQL-fu is poor. I would
have guessed they would be similar, but that shows precisely how
little I actually know about PL/SQL.

I'll try a JDBC example but if that works, that only leaves MyBatis as
the problem link. :(

[Guy Rouillier]

Well, I took this as one of those oft-spoken-about learning experiences
:). I finally got an example working, starting with your stored
function. I changed it to a stored procedure and altered for the data I
had available.

My mapper function:

Object findById(ServicePlaneRef servicePlaneRef);

I tried making this void, but iBATIS is passing *something* back; it's
always null for me.

mapper.xml:

<resultMap id="servicePlaneMap"
type="com.ibatisdemo.db.ServicePlaneRef" >
... column definitions
</resultMap>

<select statementType="CALLABLE" id="findById"

parameterType="com.ibatisdemo.db.ServicePlaneRef"
resultMap="servicePlaneMap">
{ call
get_service_plane_by_id(
#{servicePlaneId},
#{resultSet, jdbcType=CURSOR, mode=OUT,
javaType=java.sql.ResultSet, resultMap=servicePlaneMap}
)}
</select>

And the definition of ServicePlaneRef.resultSet:

private List<ServicePlaneRef> resultSet;

So, iBATIS appears to take the returned cursor, convert it to a
java.sql.ResultSet, and then via the ResultMap convert that ResultSet to
an ArrayList of the type identified by the ResultMap.

The documentation or the wiki would benefit from a complete example.

--
Guy Rouillier