Hi Guys,
I have written the following procedure which calls itself within a FOR
LOOP. When trying to run the proc I am getting the error 'ORA-1000
Maximum Number of Cursors Exceeded'
As you can see, the cursors are being opened and closed dynamically in
the FOR loops.
I have had a look on metalink etc and as of yet, havent found anything
that relates to this scenario.
Could anyone shed some light on this please?
Thanks
PROCEDURE get_end_assy
(p_org_id IN NUMBER DEFAULT 26
,p_alt_id IN NUMBER
--,p_lowest IN VARCHAR2
,p_current IN VARCHAR2
,p_parent IN VARCHAR2
,p_indent IN NUMBER
,p_insert OUT VARCHAR2
)
IS
CURSOR ass_cur IS
select
--msib1.segment1 LOWEST_ITEM
msib2.segment1 CURRENT_ITEM
,msib3.segment1 PARENT_ITEM
,msib3.item_type
,msib3.inventory_item_status_code
,bit.current_level
,bit.sort_code
,bit.lowest_item_id
,bit.current_item_id
,bit.parent_item_id
--,bit.*
from bom_implosion_temp bit
,mtl_system_items_b msib2 -- current
,mtl_system_items_b msib3 -- parent
WHERE 1=1
AND msib2.organization_id = p_org_id
AND msib2.segment1 = p_current
AND ((p_parent IS NULL)
OR (p_parent IS NOT NULL AND msib3.segment1 = p_parent))
AND bit.current_item_id = msib2.inventory_item_id
AND bit.parent_item_id = msib3.inventory_item_id
ORDER BY bit.sort_code;
CURSOR get_status_cur (p_parent VARCHAR2
,p_org NUMBER)
IS
SELECT msib.ITEM_TYPE
, msib.inventory_item_status_code
, msib.inventory_item_id
FROM mtl_system_items_b msib
WHERE msib.segment1 = p_parent
AND msib.organization_id = p_org;
c_inc NUMBER := 2;
x_indent NUMBER := p_indent;
v_ass_id NUMBER;
v_insert VARCHAR2(1) := 'N';
v_found VARCHAR2(1);
BEGIN
p_insert := 'Y';
x_indent := x_indent + c_inc;
FOR ass_rec IN ass_cur
LOOP
--p_insert := 'N';
get_end_assy
(p_org_id,p_alt_id,p_current,ass_rec.parent_item,x_indent,p_insert);
FOR get_status_rec IN get_status_cur
(ass_rec.parent_item,p_org_id)
LOOP
v_insert := 'Y';
IF v_insert = 'Y'
THEN
SELECT xxmel_apex_eco_assembly_seq.NEXTVAL
INTO v_ass_id
FROM dual;
INSERT INTO xxmel_apex_eco_assembly ass (assembly_id
,parent_part
,alteration_id
,parent_item_type
,parent_item_status_code
,parent_inventory_item_id)
VALUES (v_ass_id
, ass_rec.parent_item
, p_alt_id
, get_status_rec.ITEM_TYPE
, get_status_rec.inventory_item_status_code
, get_status_rec.inventory_item_id);
ELSE NULL;
END IF;
END LOOP;
END LOOP;
END get_end_assy;
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
