Ref Cursors

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 Received on Fri Jan 04 2002 - 11:03:34 CST