Collections: Bug? Table()? BULK COLLECT?

[Paul Rowe]

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; "inconsiste nt datatypes: expected UDT got CHAR" ?? When I
remove the TABLE() operator from the from clause (ie TABLE(collectio n)
--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.getGroupsPe rsonBelongsTo() 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_membe rs
WHERE member_id='prow e1'
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_membe rs a,TABLE(out) b
WHERE a.member_id=b.c olumn_value
AND a.member_type=' G';

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

FOR i IN temp1.FIRST..te mp1.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_PREM IUM
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_INTRANE T
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_SPLASHP AGE
sp0(): temp1(19) = LL-FIA_AGY_OFFERIN GS
sp0(): temp1(20) = LL-FIA_DER_ANALYTI CS
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_ANALYTIC S
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
getGroupsPerson BelongsToWrap() : out.count = 173

PL/SQL procedure successfully completed.

bogus >

[Daniel Morgan]

Re: Collections: Bug? Table()? BULK COLLECT?

Paul Rowe wrote:

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

Bugs are related to operating system, version, edition, and patch level
.... none of which you mention.

--
Daniel Morgan

http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)