Yeah, May be after reaching to the top most item of this hierarchy that is when parent item is null you will need to exit the outer for loop.
-Sonty On Jan 26, 12:09 pm, sonty <[email protected]> wrote: > Strange code, try these to simplify the code > have parametrized cursor. > have at least a single exit or return in your recursive call. > > Also, I will advise you to use recursive function in place of > recursive procedure as I can see only one out parameter. > > Again I am an Oracle Application Consultant too so if you can tell me > what you really want to acheive with this piece of code I might be > more able to help more effectively. > > -Sonty > > On Jan 25, 9:33 pm, Thomas Olszewicki <[email protected]> wrote: > > > 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 <[email protected]> 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 <[email protected]> wrote: > > > > > 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- 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 [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
