0 Replies - 639 Views - Last Post: 11 November 2014 - 06:42 PM

#1 Gmichael  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 05-September 12

Recursicve loop to find parts

Posted 11 November 2014 - 06:42 PM

Hello everyone, So i have created a recursive loop in oracle pl sql and my out put is correct. What im looking to do is when my cursor is finished and i all my values before i end my procedure id like to loop through the list and find duplicate values. i am using oracle 11g i know that in 12c you can use the Table(child_list_table) and select it as if it were a real table.

creates my table of objects
Create or Replace type items as object (num integer, desc1 nvarchar2(50), qty integer);
CREATE OR REPLACE TYPE ITEM_LISTs AS TABLE OF items;




create or replace PROCEDURE GetAllOfItemBOM(
ParentItem IN INTEGER ,
child_Item_list OUT ITEM_LISTs)
IS
CURSOR GetChildCur(Parent_items IN INTEGER)
IS
SELECT
IA.ITEM_ID,BI.Item_Desc, IA.Assembly_Qty
FROM
ITEM_ASSEMBLY IA JOIN BICYCLE_ITEM BI
ON IA.ITEM_ID =BI.ITEM_ID
WHERE IA.PARENT_ITEM_ID = Parent_items;
TEMP_LIST ITEM_LISTs;
ROW_INDEX PLS_INTEGER;
i items;
i2 integer;
counter integer;
VALUES_INT INTEGER;
GetChildRec GetChildCur%ROWTYPE;
BEGIN
child_Item_list := ITEM_LISTs();
OPEN GetChildCur(ParentItem);
LOOP
FETCH GetChildCur INTO GetChildRec;
child_Item_list.EXTEND;
EXIT WHEN GetChildCur%NOTFOUND;
i := items(GetChildrec.Item_iD,GetChildRec.Item_Desc,GetChildRec.Assembly_Qty);
child_Item_list(child_Item_list.LAST) := i; 
i2 := GetChildrec.Item_iD;
GetAllOfItemBOM(i2,TEMP_LIST);
IF TEMP_LIST.COUNT > 1 THEN
ROW_INDEX := TEMP_LIST.FIRST;
LOOP
EXIT WHEN ROW_INDEX IS NULL;
child_Item_list.EXTEND;
child_Item_list(child_Item_list.LAST) := TEMP_LIST(row_index);
row_index := TEMP_LIST.NEXT(row_index);
END LOOP;
END IF;
END LOOP;
CLOSE GetChildCur;

--here is were i would like to loop through child_list_item which now has all my values to output.

END GetAllOfItemBOM;



this is where i test myresult
--Test
DECLARE
   rented_items ITEM_LISTs;
   row_index PLS_INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE( ' Bicycle Report ');
   DBMS_OUTPUT.PUT_LINE( ' ' );
   GetAllOfItemBOM( 10, rented_items);
   row_index := rented_items.FIRST;
   LOOP
      EXIT WHEN row_index IS NULL;
      DBMS_OUTPUT.PUT_LINE(RPAD(rented_items(row_index).num, 5)|| RPAD(rented_items(row_index).desc1, 40) || RPAD(rented_items(row_index).qty, 5));
       row_index := rented_items.NEXT( row_index );
   END LOOP;
   
END;



Is This A Good Question/Topic? 0
  • +

Page 1 of 1