Re: refcursor v.s. host arrays and... pro*c... and ODBC

a "ref cursor" can be used to get a result set from a single SQL statement. i think of a "ref cursor" as returning a reference to a cursor... which is handled like a normal cursor... that is...

open cursor ;
loop
  fetch cursor into ... ;
  exit when %NOTFOUND;

• process fetched row end loop; close cursor;

using host arrays from pro*c is an effective way to improve the performance of an application dealing with "bulk data" (lots of rows) at the same time.

i've only ever returned "ref cursors" from stored PL/SQL blocks back to java applets through the JDBC driver.

using ODBC is not likely to improve performance.

"Jeff Kish" <ELN/kishj_at_earthlink.net> wrote in message news:HTt2OvJu4+Xgs82S+9MxA+CzCyHi_at_4ax.com...
> Hello. I asked this question before in various places.. I mostly heard
> "use a refcursor". Now I heard "use host arrays".
>
> If I have a stored procedure which
> 1) needs to return large amounts of data which is build by much work
> on the database, i.e. not the result of a single query
> 2) needs to minimize trips to the server
>
> a) Are refcursor or host arrays better? (I am guessing host arrays,
> but am not sure)
> b) can someone shoe me an example of refcursors in pro*c (I see an
> example of host arrays with pro*c in the Oracle documentation)
> c) Are these accessible from odbc also, and if so, does someone have
> an example?
>
> Thanks
> Jeff Kish
> p.s. cc emails appreciated
> kishj_at_earthlink.net
>
Received on Tue Jan 30 2001 - 23:55:39 CST