Re: Bulk collect got truncated?

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