4 Replies Latest reply: Dec 13, 2012 3:13 AM by Manjusha Muraleedas RSS

    issue while using multiset intersect

    Manjusha Muraleedas
      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
          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
            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
              Thank you Alberto...

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

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