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;
And of course, I was wrong. it does close at the end of a cursor for loop. are you by any chance throwing exceptions that are being caught outside this code? That would leave cursors open. Sorry about the bum steer. going to get coffee now rob -- 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
