RE: reference cursors

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Fri, 30 Jan 2004 00:09:28 -0000
Message-ID: <000001c3e6c5$54ddbe70$b306bc3e@nlhome>

> I got one here. I'll start without all the details to see if
> the answer is something simple that can be answered without
> the ugly details, trace files, etc.

Well 10046 *will* tell you what takes the time :(

>
> The app calls a procedure in a package which does a select
> and hands back the results to a refcursor which gets used by
> a VB app (don't look at me, I didn't do it). It takes about
> 60 - 70 seconds for the VB app to get its data back.
>
> If I make the procedure a stand-alone procedure that just
> opens a plain old cursor and loops through it with some
> dbms_output.put_line calls, the thing prints out the same
> info, but returns in less than a second. If I do the
> following:
>
> ----------
> SQL> var A refcursor
> SQL> exec THE_PROCEDURE(parm1, parm2, .... , :A)
>
> PL/SQL procedure successfully completed.
>
> SQL> print A
> ----------
>
> The "successfully completed" returns almost immediately. But
> the "print A" takes about a minute to start printing. Is
> there some essential element of using refcursors that has
> been missed here?

So VB is quicker right? ... OK only joking. How much data are you transmitting back to the client over what sort of network. A 10mb resultset over a 9600 modem might take way longer than a minute..

Niall

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jan 29 2004 - 18:09:28 CST