RE: Bulk collect got truncated?

From: Gorbounov,Vadim <vadim.gorbounov_at_liberate.com>
Date: Tue, 20 May 2003 09:42:35 -0800
Message-ID: <F001.0059E0FF.20030520094235@fatcity.com>

Raj,  

Unfortunately, no errors. It fails quitely. What could be worse.  

Vadim

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

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-----
<mailto:vadim.gorbounov_at_liberate.com> ]
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


<http://www.orafaq.net>  

-- 
Author: Gorbounov,Vadim 
  INET: vadim.gorbounov_at_liberate.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com


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

Received on Tue May 20 2003 - 12:42:35 CDT