Read it too quickly... so you have the problem before the parts I
comment on. One further question: where do you set the "return type"
as a cursor in your Ibatis version (the
"cstmt.registerOutParameter(1,OracleTypes.CURSOR)" in the original
version)?
The problem with cursor types is that they are not in the JDBC spec.
Most database vendors support them but using their own db specific
classes.
In general the "fetch out of sequence" in Oracle you usually get when:
- You fetch from a closed cursor
- You fetch from an open cursor after a commit.
My hunch here would be that Oracle gets confused because you don't
seem to register your return value as cursor type.
Regards,
Sven
On Mon, 16 May 2005 06:49:24 -0500, you wrote:
>Sven,
>
>Actually this is a case of a cursor returned within another cursor, so I
>haven't performed a fetch until I called result.next() below.
>
>See the straight Java example I included to understand the nesting I'm
>trying to accomplish. Or perhaps there is something I'm not understanding
>about how getObject() works?
>
>Thanks,
>
>Ken
>
>Ken,
>
>To get back to the original question, the problem you have lies in the
>part:
>
>public Object getResult(ResultGetter arg0) throws SQLException {
> System.out.println("Object: " +
> arg0.getObject().getClass().getName());
> ResultSet result =3D (ResultSet) arg0.getObject();
> System.out.println("Iterating");
> while(result.next())
> System.out.println("test");
> result.close();
>
> return result;
>}
>
>1) You already fetch the result in the method above (for debugging
>purposes probably), so by the time you want to really extract the
>data, the data has already been fetched. This is the explanation of
>the Oracle error "ORA-01002 fetch out of sequence" (have a look at the
>documentation for it).
>Cursor are for single use... if you used a cursor, you have reexecute
>the query to be able to fetch again
>
>2) You close result before returning it???
>
>Regards,
>Sven
>
>
>On Sun, 15 May 2005 20:00:28 -0600, you wrote:
>
>>I'm not really sure what the problem could be. It should work fine.
>>
>>Is there no way to just join the data and return a set of repeating groups
>>and use the iBATIS N+1 Selects solution?
>>
>>http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem%3F
>>
>>Cheers,
>>Clinton
>>
>>
>>On 5/15/05, Ken Katsma <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> wrote:
>
>>>
>>> Hi,
>>>
>>> My group is currently using stored procedures in Oracle for all our
>>> database access. To get around the n+m problem we are considering
>>> several alternatives. One we know will work is to do a user function
>>> for each column that maps to a "m" table that gives us the data in
>>> string format (name,value pairs).
>>>
>>> Another approach, (which we would prefer) is to return the results for
>>> each "m" column as a cursor. I can do this in java with code that looks
>>> something like this:
>>>
>>> class RefCursor
>>> {
>>>
>>> public static void main (String args [])
>>> throws SQLException, ClassNotFoundException
>>> {
>>> String query = "begin "+
>>> "open ? for "+
>>> "'select dname, CURSOR(select ename "+
>>> "from emp "+
>>> "where
>>> emp.deptno=dept.deptno) "+
>>> "from dept'; "+
>>> "end;";
>>>
>>> DriverManager.registerDriver
>>> (new oracle.jdbc.driver.OracleDrive r());
>>>
>>> Connection conn=
>>> DriverManager.getConnection
>>> ("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
>>> "scott", "tiger");
>>>
>>> Statement trace = conn.createStatement();
>>>
>>> CallableStatement cstmt = conn.prepareCall(query);
>>>
>>> cstmt.registerOutParameter(1,OracleTypes.CURSOR);
>>> cstmt.execute();
>>>
>>> ResultSet rset= (ResultSet)cstmt.getObject(1);
>>>
>>> for(int i = 0; rset.next(); i++ )
>>> {
>>> System.out.println( rset.getString(1) );
>>> ResultSet rset2 = (ResultSet)rset.getObject(2);
>>> for( int j = 0; rset2.next(); j++ )
>>> System.out.println ( " " + rset2.getString(1) );
>>> rset2.close();
>>>
>>> }
>>>
>>> rset.close();
>>> cstmt.close();
>>> }
>>> }
>>>
>>> To get the same functionality in iBatis we tried writing a custom type
>>> handler for the cursor column that looks like this:
>>>
>>> import java.sql.Ref;
>>> import java.sql.ResultSet;
>>> import java.sql.SQLException;
>>>
>>> import com.ibatis.sqlmap.client.extensions.ParameterSetter;
>>> import com.ibatis.sqlmap.client.extensions.ResultGetter;
>>> import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
>>> import com.ibatis.sqlmap.engine.type.ResultGetterImpl;
>>>
>>> /**
>>> * @author S001027
>>> *
>>> * TODO To change the template for this generated type comment go to
>>> * Window - Preferences - Java - Code Style - Code Templates
>>> */
>>> public class RefTypeHandler implements TypeHandlerCallback {
>>>
>>> /* (non-Javadoc)
>>> * @see
>>> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#setParameter (
>>> com.ibatis.sqlmap.client.extensions.ParameterSetter,
>>> java.lang.Object)
>>> */
>>> public void setParameter(ParameterSetter arg0, Object arg1)
>>> throws SQLException {
>>> if (arg1 != null)
>>> {
>>> arg0.setObject(arg1);
>>> }
>>> }
>>>
>>> /* (non-Javadoc)
>>> * @see
>>> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#getResult(
>>> com.ibatis.sqlmap.client.extensions.ResultGetter )
>>> */
>>> public Object getResult(ResultGetter arg0) throws SQLException {
>>> System.out.println("Object: " +
>>> arg0.getObject().getClass().getName());
>>> ResultSet result = (ResultSet) arg0.getObject();
>>> System.out.println("Iterating");
>>> while(result.next())
>>> System.out.println("test");
>>> result.close();
>>>
>>> return result;
>>> }
>>>
>>> /* (non-Javadoc)
>>> * @see
>>> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#valueOf(
>>> java.lang.String)
>>> */
>>> public Object valueOf(String arg0) {
>>> // TODO Auto-generated method stub
>>> return null;
>>> }
>>>
>>> Unfortunately, when the statement:
>>>
>>> ResultSet result = (ResultSet) arg0.getObject();
>>>
>>> executes, we get an error from oracle that says "FETCH OUT OF
>>> SEQUENCE". Is there something with the cursor going on in iBatis that
>>> prevents us from touching the nested cursor? I guess my next step is to
>>> start stepping through the iBatis code in debug to see whats happening,
>>> but I was hoping somebody may already have a solution or know the problem.
>>>
>>>
>>> Thanks,
>>>
>>> Ken
>>>
