RE: Bulk collect got truncated?

Thanks, Stephane,

I don't have a good filling about having plsql table of that size either, although Oracle claims it must work up to 2^32.

I was looking for some practical cases against plsql tables larger than X number of rows.
This failure sounded a good one, but it happens occasionally, in most cases everything works fine. Yes, it works. Well, almost.

I'll try to approach this from efficiency side now, at least in our case plsql table of 200000+ rows could not outperform temp table or plain SQL.

Have a good day
Vadim

-----Original Message-----
Sent: Tuesday, May 20, 2003 3:42 PM
To: Multiple recipients of list ORACLE-L

"Gorbounov,Vadim" wrote:
>
> Hi all,
>
> Is there any "best practice" regarding practical limits on PL/SQL tables?
> We've got an issue here which may potentially invalidate data, no error
> messages.
>
> Here is the case:
>
> Oracle9i Enterprise Edition Release 9.0.1.4.0, Solaris.
>
> DECLARE
> TYPE t_subsvcparmid IS TABLE OF sub_svc_parm.sub_svc_parm_id%TYPE;
> esubsvcparmid t_subsvcparmid;
> BEGIN
> SELECT /*+ index(sub_svc_parm, sub_svc_parm_ix2) */
> sub_svc_parm_id
> BULK COLLECT INTO esubsvcparmid
> FROM sub_svc_parm
> WHERE parm_id = 10;
>
> DBMS_OUTPUT.PUT_LINE(esubsvcparmid.LAST);
> ..................................
>
> The select is expected to return close to 200K records, and usually it
does,
> but sometimes number of records is restricted to 65535 = 2^16-1, it can be
> seen both from DBMS_OUTPUT and 10046 trace:
>
> FETCH

>
#2:c=4370000,e=33876545,p=14895,cr=66960,cu=0,mis=0,r=65535,dep=1,og=4,tim=1
> 052942377932150
>
> ^^^^^^^
> Did anybody see such weird behavior?
>
> TIA
>
> Vadim Gorbounov
>
> Liberate Tech.
>

Vadim,

   I have never heard of this, but it only half surprises me - what surprises me most is that you have seen it work. It's a well known fact with Oracle that array fetches, probably because indices are unsigned short integers, have such a limit. PL/SQL tables are just arrays in disguise.
I must say that I wouldn't think of using them for such a big number of rows. I am far from being a proponent of temporary tables, but that may be the case for using them.  

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: vadim.gorbounov_at_liberate.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).