Re: Getting a ResultSet for a refcursor element.

Barry Lind <blind@xythos.com> writes:

> > Why couldn't we do that with a ResultSet?
> >
> It can't be done with a result set since the point of a refcursor it to
> pass around the pointer to the cursor.  It is the final function that
> will take the pointer and do the fetching.  If the rows have already
> been fetched that code will not work.
>
> So you have function a() that returns a refcursor.  It has some black
> box implementation that is creating a query and returning the refcursor
> to that query.  Then you have function b(refcursor) that takes a
> refcursor and fetches the results and processes them.  So when the
> refcursor is passed to function b() the assumption is that function b()
> can get the rows from the refcursor.

But you could do it inside the CallableStatement by converting a
refcursor based ResultSet into the original refcursor again.

So you'd have this:

   CallableStatement getCurs = con.prepareCall("{ ? = pl.get_cur() }");
   getCurs.registerOutParameter(1, Types.OBJECT);
   getCurs.execute();
   ResultSet rs = (ResultSet) getCurs.getObject(1);

   // Now pass to another proc.
   CallableStatement sendCurs = con.prepareCall("{ pl.send_cur( ? } }");
   sendCurs.setObject(1, rs);
   sendCurs.execute();


And the implementation of the result set is completly hidden.


> It has been a while, but I beleive that Oracle has two different types
> of cursors in plsql, one that is similar in nature to refcursors (i.e. a
> pointer to a query) and a second that more or less is the result set.  I
> want to spend some time going through the Oracle doc to understand the
> different functionality in this area.

There are two different types, and that is (more or less) the
difference. But only the 2nd type can be returned from a function
(you can return the first type by assigning it to a variable of the
second type).



Nic