Re: Getting a ResultSet for a refcursor element.

Barry Lind <blind(at)xythos(dot)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