This discussion is archived
4 Replies Latest reply: Dec 13, 2012 1:13 AM by Manjusha Muraleedas RSS

issue while using multiset intersect

Manjusha Muraleedas Newbie
Currently Being Moderated
Can anyone tell me the reason for the following error

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

 CREATE OR REPLACE
PROCEDURE Pr_Split AS
     l_g1g2_items       varchar2_ntt;
     l_split_items      varchar2_ntt;
     l_split_g1g2_items varchar2_ntt;
begin

     SELECT ITEM_NO BULK COLLECT
          INTO l_g1g2_items
          FROM tfo_im_item_lookup_description
     HAVING COUNT(item_no) = 2
      GROUP BY item_no;

     FOR l_split_items IN (Select CAST(COLLECT(D.ITEM_NO) AS          varchar2_ntt)     From item_lookup_description D      Group By D.uda_type_dt) LOOP
     
          l_split_g1g2_items := l_split_items multiset intersect l_g1g2_items;
     
          if cardinality(l_split_g1g2_items) > 0 then
               null;
          end if;
     end loop;
end;
error:

Compilation errors
Error: PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
Text: l_split_g1g2_items := l_split_items multiset intersect l_g1g2_items;

orcle version is :

SQL> select * from v$version
2 /

BANNER

--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Edited by: Manjusha Muraleedas on १३ दिसंबर, २०१२ १२:५६ अपराह्न
  • 1. Re: issue while using multiset intersect
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Manjusha,

    when you define a statement like:
    FOR x IN (SELECT...} LOOP
    x is always an implicit cursor type, no matter if you have defined it before.

    Probably you need to change your code in this way:
    FOR c1 IN (Select CAST(COLLECT(D.ITEM_NO) AS          varchar2_ntt) split_items     From item_lookup_description D      Group By D.uda_type_dt) LOOP
       l_split_g1g2_items := c1.split_items multiset intersect l_g1g2_items;   
    However I don't understand how many records do you expect for the record in the cursor and if it correct to loop and keep doing an intersect with the result of previoous interesect operation and the new nested table retrieved from the table.

    If you want to have additional information post CREATE TABLE and INSERT statements, explain the logic and post the expected output.

    Regards.
    Al
  • 2. Re: issue while using multiset intersect
    Manik Expert
    Currently Being Moderated
    Are you trying something like this???

    Untested, check it...
    CREATE OR REPLACE PROCEDURE Pr_Split AS
       l_g1g2_items         varchar2_ntt;
       l_split_items        varchar2_ntt;
       l_split_g1g2_items   varchar2_ntt;
    BEGIN
         SELECT CAST (ITEM_NO AS VARCHAR2 (4000))
           BULK COLLECT INTO l_g1g2_items
           FROM tfo_im_item_lookup_description
         HAVING COUNT (item_no) = 2
       GROUP BY item_no;
    
         SELECT D.ITEM_NO
           BULK COLLECT INTO l_split_items
           FROM item_lookup_description D
       GROUP BY D.uda_type_dt;
    
       l_split_g1g2_items := l_split_items MULTISET INTERSECT l_g1g2_items;
    
       FOR i IN 1 .. l_split_g1g2_items.COUNT LOOP
          NULL;
       END LOOP;
    END;
    Cheers,
    Manik.
  • 3. Re: issue while using multiset intersect
    Manjusha Muraleedas Newbie
    Currently Being Moderated
    Thank you Alberto...

    It works fine now...
  • 4. Re: issue while using multiset intersect
    Manjusha Muraleedas Newbie
    Currently Being Moderated
    Manik,

    Thanks for the response. I got the answer from the previous post.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points