Re: Ref Cursors

On Fri, 4 Jan 2002 12:03:34 -0500, "Jeff Smith" <jsmit234_at_ford.com> wrote:

>I have a question regarding what is the most efficient way to deal with
>procedures returning ref cursors.
>
>We are developing a very large web application using J2EE and Oracle 8i (see
>below for versions).
>Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
>PL/SQL Release 8.1.7.2.0 - Production
>CORE 8.1.7.0.0 Production
>TNS for SEQUENT DYNIX/ptx: Version 8.1.7.2.0 - Production
>NLSRTL Version 3.4.1.0.0 - Production
>
>All database interaction is done via Oracle procedures, functions and
>packages. This includes generating/obtaining all results sets.
>
>There seems to be some issues. To return more than one row from a stored
>procedure, we use Ref Cursors. But the communication that occurs back and
>forth between the application server (Websphere) appears to be excessive.
>There is a complete round trip occurring between the app server and the
>database for each row.
>
>My question, is there any way to speed up the results that come from a ref
>cursor used as an out parameter in a stored procedure? If we were to execute
>the same sql statement from a JSP or elsewhere on the app server, would our
>peformance be better?
>
>We would prefer to keep all database activity contained within the database.
>Is there a more efficient way to get "sets" of data out of procedures?
>
>Thanks
>Jeff Smith
>
>

A ref cursor will *always* return one row at a time. Hence, this results in one roundtrip, and there is nothing you can do about that. Remember, if you build pure Oracle applications (ie you don't port them from sqlserver) you won't use stored procedure. The only solution is to fetch the data in array's, as there will be one roundtrip per array.
I would look into using the jdbc drivers available from Oracle.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Jan 04 2002 - 15:51:34 CST