RE: reference cursors

> 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.