Yep, you hit the nail on the head. I just added a little check to see
if one of the out parameters was a cursor and then treated that cursor
as a normal result set.
Brandon Goodin (JIRA) wrote:
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60696 ]
Brandon Goodin commented on IBATIS-53:
--------------------------------------
Let me see if i understand this correctly...
A cursor must be retrieved using a CallableStatement/Stored Procedure. The
Callable Statement returns a ResultSet as an OUT parameter that you want to map
to objects as normal with ibatis. Your current issue is that you need to
cs.execute instead of cs.executeQuery because you do no expect a resultset
directly back.
Is that correct?
Support for oracle cursors as resultsets
----------------------------------------
Key: IBATIS-53
URL: http://issues.apache.org/jira/browse/IBATIS-53
Project: iBatis for Java
Type: New Feature
Components: SQL Maps
Reporter: Ken Katsma
Priority: Minor
iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
The code change is in SQLExecutor.executeQueryProcedure (see comments):
public void executeQueryProcedure(RequestScope request, Connection conn, String sql, Object[] parameters,
int skipResults, int maxResults, RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
cs = conn.prepareCall(sql);
ParameterMap parameterMap = request.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext.setMoreInfo("Check the output parameters (register output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(request, cs, parameters);
errorContext.setMoreInfo("Check the statement (update procedure failed).");
// ****************************************
// Code changes below
// ****************************************
if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
// If in oracle then execute instead of executeQuery
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
// Get the output parameters first, instead of last
retrieveOutputParameters(cs, mappings, parameters);
// Then find the resultset and handle it
for (int i=0;i<parameters.length;i++)
{
if (parameters[i] instanceof ResultSet)
{
rs = (ResultSet) parameters[i];
break;
}
}
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
//****************************************
// Non-oracle..original code
else
{
errorContext.setMoreInfo("In non-Oracle mode.");
rs = cs.executeQuery();
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(cs, mappings, parameters);
}
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(cs);
}
}
An example mapping looks like:
<parameterMap id="clientParameters" class="map" >
<parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
<parameter property="maxRows" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="getClientListProc" resultMap="clientResult" parameterMap="clientParameters">
{?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
</procedure>
