Re: Getting a ResultSet for a refcursor element.

Nic Ferrier wrote:
> 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.
>
>

But now we essentially have two types of ResultSets, regular ones and
refcursor ones. Refcursor ones need to be treated differently, because
if you want to do the above the refcursor based result set can't
populate its results until you attempt to get the results (i.e. call
next()), this is because if you do intend to pass it on you can't get
the results because then the called function will have nothing to fetch
since the data will already be fetched.

Also it wouldn't be appropriate to pass any old result set in as a bind
since only refcursor result sets could be passed on.

But perhaps there is a middle ground between our two view points of view
that combines them.

You are suggesting getting a refcursor returns a regular result set, I
am suggesting it returns a special object that retains the pointer
characteristics of a refcuror.

By combining these ideas we could do the following. Introduce a new
object RefCursorResultSet that extends ResultSet. It basically acts as
my wrapper object and can be passed on to other function calls. However
at the same time it can also fully implement ResultSet. So that once
you call next() it will actually go out and fetch the data from the
refcursor. So I would see the implementation of this object as having a
method like String getCursorID() that would return the refcursor name.
It would also have a member variable that would be the real result set.
The implementation of next() would initialize the member the first
time it was called and then it and all other methods from ResultSet
would simply call the corresponding methods on the internal ResultSet
object.

How does this sound?

thanks,
--Barry

>
>>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
>
>