Re: Ref Cursors

In article <5m8c3u8rdnejki2il1jr8crdvk8njn6u0e_at_4ax.com>, Sybrand says...
>
>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.

absolutely FALSE. a ref cursor is just like a cursor. See http://groups.google.com/groups?q=group:comp.databases.oracle.*+insubject:ref+insubject:cursors+author:tkyte%40us.oracle.com&selm=a15i6h01cmm%40drn.newsguy.com&rnum=2

which demonstrates this.

>Remember, if you build pure Oracle applications (ie you don't port
>them from sqlserver) you won't use stored procedure.

absolutely FALSE. Using stored procedures and never putting SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice, one that I encourage (it makes it so much easier to tune when I can sit at a terminal, look at a tkprof, find the bad query, read the code out of the database, fix it in vi, put it back in -- no redeploy this war/ear/jar/zip/whatever file, bounce the app server, reload the application, yadda yadda yadda.....).

I gain security enhancements by doing this. I gain reusability by doing this.
I gain ease of maintainence by doing this. I gain control by doing this.

>The only solution is to fetch the data in array's, as there will be
>one roundtrip per array.

and ref cursors do that quite easily

>I would look into using the jdbc drivers available from Oracle.
>

that I agree with.

>Hth
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp