This content has been marked as final. Show 4 replies
when you define a statement like:
x is always an implicit cursor type, no matter if you have defined it before.
FOR x IN (SELECT...} LOOP
Probably you need to change your code in this way:
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.
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;
If you want to have additional information post CREATE TABLE and INSERT statements, explain the logic and post the expected output.
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;