Collections: Bug? Table()? BULK COLLECT?
Date: 5 Aug 2003 09:56:59 -0700
Message-ID: <bbd01c1e.0308050856.46ff6a12_at_posting.google.com>
Hi "You"
I have two collection types declared at the SQL level.
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?
--> 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 > _at_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'TREPEAT!
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 - 18:56:59 CEST