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

Paul,

I am not aware of any bugs, but just to rule out the possibility, can you add DISTINCT clause in your second SELECT statement and see if you are getting the same output or not. Am wondering if there is something wrong with your join (I understand that when you run plain sql, it is working fine, but just a thought).
Also, in your real code, is your pl/sql table declared inside a function or is it in a Package. Since you are getting correct results once in a while, is it possible that your session variables are not getting reinitialized properly?

-bhogak

"Paul Rowe" <paul_at_paulrowe.com> wrote in message news:bbd01c1e.0308050856.46ff6a12_at_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 - 19:44:39 CDT