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 >
