Re: reference cursors
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Jan 2004 23:20:38 +0000 (GMT)
Depends what is in your procedure. If it just opens the ref cursor, then the
work (and hence the
60 seconds) will come from the fetching. Oracle can be quite smart by doing
almost no work until
the first fetch call is issued (after all, if you never do the first fetch,
there is no point in
Oracle doing the work).
hth
Connor
--- Stephen.Lee@xxxxxxxx wrote: > 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
________________________________________________________________________
BT Yahoo! Broadband - Free modem offer, sign up online today and save £80
http://btyahoo.yahoo.co.uk
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
