Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bulk collect got truncated?

RE: Bulk collect got truncated?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 20 May 2003 12:46:59 -0800
Message-ID: <F001.0059E3EB.20030520124659@fatcity.com>


Vadim,  

SELECT count(*)
  FROM sub_svc_parm
 WHERE parm_id = 10;  

what do you get??  

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 1:43 PM
To: Multiple recipients of list ORACLE-L

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: 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 - 15:46:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US