Chris, This is the nature of recursive call. As Andrew (Trail) have said. You are calling same function from within the same function. It opens the new cursor(s) and keeps them open until you come back from next recursive call. If you cursor "ass_cur" returns 100 rows, your function will open (2*100)! cursors. All the cursors will close, but only when the recursive calls are returning control to respective calling "parents" So in your case you will need to increase open_cursors paramater in init database setup. Hope this helps Thomas
On Jan 25, 11:07 am, Trail <andrew.tr...@gnb.ca> wrote: > Could the issue simply be with the call to itself - it appears that > your code is called, opens the first cursor, calls itself, opens the > first cursor (second instance), calls itself ... without performing > any functions. Seems like it would just open cursors til your > OPEN_CURSORS session parameter blows up. > > I could be way off base - as Rob said, I haven't had a coffee yet > either. > > Failing that - check your OPEN_CURSORS parameter. Default is 50. > Should probably set to 1000 (ref: Tom Kyte) > > On Jan 25, 11:55 am, 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; > > > 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- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - -- 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
