On Jan 25, 7:10 am, Chris <[email protected]> wrote: > 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;
Not seeing where you are closing the cursors. I might be wrong, I often am, but I don't think that the end of a cursor for loop actually closes the cursor. it is monday morning, i don't move that fast... -- 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
