Actually u must use OPEN and CLOSE as before opening other cursor.However u can do that in a loop.Try it i may be wrong. -- Thanks and Regards, Opus Software Solution(Pune) Niraj Singh Parihar Mobile Number : +919890492566.
On Mon, Jan 25, 2010 at 9:20 PM, Rob Wolfe <wolfe....@gmail.com> wrote: > > > On Jan 25, 7:10 am, Chris <christopherc...@hotmail.com> 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 Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
