Collections: Bug? Table()? BULK COLLECT?

From: Paul Rowe <paul_at_paulrowe.com>
Date: 5 Aug 2003 09:56:59 -0700
Message-ID: <bbd01c1e.0308050856.46ff6a12@posting.google.com>

Hi "You"

I have two collection types declared at the SQL level.

1. Do you know of any known bugs with the BULK COLLECT clause used with the TABLE operator? I have a situation now where I am using a BULK COLLECT clause with a SELECT statement and a TABLE() operator in a join. I am finding that this select statement either returns the wrong result or the right result. The wrong result is always the same... too many rows where the last row is repeated many times. This to me appears to be some type of bug or corruption somewhere. When I run plain SQL queries against the database, everything is fine, but went I run this PL/SQL function, something is not right.. it intermittantly returns the wrong or right result. Do you see any problems with the skeletal procedure below that may bring out a known bug? Do you know what we can check in our database to see what may be causing this issue?
2. Do you know why when I remove the BULK COLLECT clause in a SELECT statement with a joined TABLE() to make it a plain SELECT INTO, I get ORA-00932; "inconsistent datatypes: expected UDT got CHAR" ?? When I remove the TABLE() operator from the from clause (ie TABLE(collection)
   --> collection), then I get ORA-00942. I want to use my collection in
   the FROM CLAUSE, but I can only get it to work when the BULK COLLECT clause is present. Is there a restriction on when I can use collections in the FROM clause?

The stored procedure:

• the following is a skeletal function to emulate what
• is going on in the UCF.getGroupsPersonBelongsTo() function.
• This function disregards the input parameter, and the output
• parameter is not to be used.

CREATE OR REPLACE

FUNCTION sp0(	personId	IN	varchar2) 
	RETURN UCF_GROUP_LIST
AS
	out 	UCF_GROUP_LIST;
	temp1	UCF_GROUP_LIST;

BEGIN

• this select statement always works correctly. SELECT group_id BULK COLLECT INTO out FROM ucf_group_members WHERE member_id='prowe1' AND member_type='P';

    dbms_output.put_line('sp0(): out.count = '||out.count);

• the following select statement is randomly returning
• either 164 rows or 95 rows. 164 rows is the correct return

	SELECT a.group_id
		BULK COLLECT INTO temp1
		FROM ucf_group_members a,TABLE(out) b
		WHERE a.member_id=b.column_value
		AND a.member_type='G';

    dbms_output.put_line('sp0(): temp1.count = '||temp1.count);

        FOR i IN temp1.FIRST..temp1.LAST LOOP     

        dbms_output.put_line('sp0(): temp1('||i||') = '||temp1(i));
        
	END LOOP;

    RETURN out;
END; Sample output:

bogus > @c:\q.sql

sp0(): out.count = 173
sp0(): temp1.count = 95
sp0(): temp1(1) = LL-CRD_CREDIT_PREMIUM
sp0(): temp1(2) = LL-MKD_ROOT
sp0(): temp1(3) = LL-CRS_ROOT
sp0(): temp1(4) = LL-SMF_ROOT
sp0(): temp1(5) = LL-ECD_ROOT
sp0(): temp1(6) = LL-LLF_ROOT
sp0(): temp1(7) = LL-NSR_ROOT
sp0(): temp1(8) = LL-COM_CAD_INTRANET
sp0(): temp1(9) = LL-LAS_BCP
sp0(): temp1(10) = LL-EDV_BASIC
sp0(): temp1(11) = LL-PNT_USER
sp0(): temp1(12) = LL-ETR_BASIC
sp0(): temp1(13) = LL-WCM_BASIC
sp0(): temp1(14) = LL-GIS_BASIC
sp0(): temp1(15) = LL-ESM_MW_INTERNAL
sp0(): temp1(16) = LL-WKI_ROOT
sp0(): temp1(17) = LL-AMO_ROOT
sp0(): temp1(18) = LL-LLS_PCS_SPLASHPAGE
sp0(): temp1(19) = LL-FIA_AGY_OFFERINGS
sp0(): temp1(20) = LL-FIA_DER_ANALYTICS
sp0(): temp1(21) = LL-LLS_PERFORMANCE_MGT
sp0(): temp1(22) = LL-EFI_ROOT
sp0(): temp1(23) = LL-EIN_BASIC
sp0(): temp1(24) = LL-IBD_INTERNAL
sp0(): temp1(25) = LL-FIA_FI_ANALYTICS
sp0(): temp1(26) = LL-ECM_SYNTICKET
sp0(): temp1(27) = LL-ECS_ROOT
sp0(): temp1(28) = LL-LMD_EMPLOYEES
sp0(): temp1(29) = LL-IOF_USER
sp0(): temp1(30) = LL-FXS_BASIC
sp0(): temp1(31) = LL-SIF_ROOT
sp0(): temp1(32) = LL-PIM_ROOT
sp0(): temp1(33) = LL-LLP_BASIC
sp0(): temp1(34) = LL-MYC_BASIC
sp0(): temp1(35) = LL-REP_ROOT
sp0(): temp1(36) = LL-USN_ROOT
sp0(): temp1(37) = LL-OWA_LINK
sp0(): temp1(38) = LL-CRD_MUNICIPALS
sp0(): temp1(39) = LL-IDR_ROOT
sp0(): temp1(40) = LL-EDW_USER
sp0(): temp1(41) = LL-EDW_USER
sp0(): temp1(42) = LL-EDW_USER
sp0(): temp1(43) = LL-EDW_USER
sp0(): temp1(44) = LL-EDW_USER
sp0(): temp1(45) = LL-EDW_USER
sp0(): temp1(46) = LL-EDW_USER
sp0(): temp1(47) = LL-EDW_USER
sp0(): temp1(48) = LL-EDW_USER
sp0(): temp1(49) = LL-EDW_USER
sp0(): temp1(50) = LL-EDW_USER
sp0(): temp1(51) = LL-EDW_USER
sp0(): temp1(52) = LL-EDW_USER
sp0(): temp1(53) = LL-EDW_USER
sp0(): temp1(54) = LL-EDW_USER
sp0(): temp1(55) = LL-EDW_USER
sp0(): temp1(56) = LL-EDW_USER
sp0(): temp1(57) = LL-EDW_USER
sp0(): temp1(58) = LL-EDW_USER
sp0(): temp1(59) = LL-EDW_USER
sp0(): temp1(60) = LL-EDW_USER   ... THIS IS WRONG !!!!  SHOULDN'T

REPEAT!

sp0(): temp1(61) = LL-EDW_USER
sp0(): temp1(62) = LL-EDW_USER
sp0(): temp1(63) = LL-EDW_USER
sp0(): temp1(64) = LL-EDW_USER
sp0(): temp1(65) = LL-EDW_USER
sp0(): temp1(66) = LL-EDW_USER
sp0(): temp1(67) = LL-EDW_USER
sp0(): temp1(68) = LL-EDW_USER
sp0(): temp1(69) = LL-EDW_USER
sp0(): temp1(70) = LL-EDW_USER
sp0(): temp1(71) = LL-EDW_USER
sp0(): temp1(72) = LL-EDW_USER
sp0(): temp1(73) = LL-EDW_USER
sp0(): temp1(74) = LL-EDW_USER
sp0(): temp1(75) = LL-EDW_USER
sp0(): temp1(76) = LL-EDW_USER
sp0(): temp1(77) = LL-EDW_USER
sp0(): temp1(78) = LL-EDW_USER
sp0(): temp1(79) = LL-EDW_USER
sp0(): temp1(80) = LL-EDW_USER
sp0(): temp1(81) = LL-EDW_USER
sp0(): temp1(82) = LL-EDW_USER
sp0(): temp1(83) = LL-EDW_USER
sp0(): temp1(84) = LL-EDW_USER
sp0(): temp1(85) = LL-EDW_USER
sp0(): temp1(86) = LL-EDW_USER
sp0(): temp1(87) = LL-EDW_USER
sp0(): temp1(88) = LL-EDW_USER
sp0(): temp1(89) = LL-EDW_USER
sp0(): temp1(90) = LL-EDW_USER
sp0(): temp1(91) = LL-EDW_USER
sp0(): temp1(92) = LL-EDW_USER
sp0(): temp1(93) = LL-EDW_USER
sp0(): temp1(94) = LL-EDW_USER
sp0(): temp1(95) = LL-EDW_USER

getGroupsPersonBelongsToWrap(): out.count = 173

PL/SQL procedure successfully completed.

bogus > Received on Tue Aug 05 2003 - 11:56:59 CDT