reference cursors

From: <Stephen.Lee_at_DTAG.Com>
Date: Thu, 29 Jan 2004 16:59:06 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B079091C3@dtagpo1.dtg.local>

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.

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?

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 - 16:59:06 CST