RE: Bulk collect got truncated?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 20 May 2003 08:21:41 -0800
Message-ID: <F001.0059DEC9.20030520082141@fatcity.com>

do you get any errors??

Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Tuesday, May 20, 2003 11:17 AM
To: Multiple recipients of list ORACLE-L

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.

--

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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). Received on Tue May 20 2003 - 11:21:41 CDT