Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Explicit Cursor in a For Loop and crashing database

Re: Explicit Cursor in a For Loop and crashing database

From: April <PrivateBenjamin_at_hushmail.com>
Date: 22 Sep 2003 10:30:39 -0700
Message-ID: <54df0379.0309220930.1af5a135@posting.google.com>


If I replace the innermost loop with a call to a procedure that does the same work then it does not crash. Something to do with memory/stack management in
Oracle 7.3.4 ?

btw - the format looks fine in the editor, but doesn't look so fine in the preview

begin
-- for the new xrsl records
  for chld in c_chld loop
  --get the new and old structure_cct_ids

     for cid in c_xrslids (chld.parnt,chld.chld) loop
	 -- insert new xrsip records if applicable
	 insert_new_xrsip(cid.old_scctid,cid.newid,chld.i_s,
         chld.o_s,chld.parnt);
     end loop;

 end loop;

exception

     when others then
       rollback;
	   raise;

end;
/

CREATE OR REPLACE procedure insert_new_xrsip (old_scctid number,new_scctid number, isdate date, osdate date,old_lnsctid number) as

--source dataset for insertion of new insulator recs cursor c_parnt (scctid number) is select * from xref_structure_insulator_poas where structure_cct_id = scctid; exist number;
newos date;
cnt number;

begin

    exist := 0;
    For src in c_parnt(old_scctid) loop

janik_at_pobox.sk (Jan) wrote in message news:<81511301.0309220227.74fa10b0_at_posting.google.com>...

> difficult to read your unformatted code so I didn`t go through it,
> but you have probably infinite loop somewhere. If so, then once any
> session run it, then it becames locked and you cannot recompile it.
> You have to kill the session.
> 
> Try to format your code (make it readable) and then you (or we) will
> easier identify the problems.
> 
> 
> 
> PrivateBenjamin_at_hushmail.com (April) wrote in message news:<54df0379.0309181348.3dd90147_at_posting.google.com>...
> > I have this procedure below which keeps hanging and will evetually
> > crash the database.... Oracle 7.3.4.  The procedure itself seems to be
> > corrupted and cannot be compiled or dropped after trying to run it or
> > step through debugging with Toad.
> > 
> > Is there something wrong with the way I am using the explicit cursors
> > in a For Loop?
> > 
> > I have stepped through the procedure (when it doesn't crash) for small
> > data sets ie. one item for the main cursor loop (c_chld), and it works
> > fine, but when it has to go through another main loop then it crashes,
> > even though it is doing the exact same thing.
> > 
> > Thanks for your help,
> > April
> > 
> > CREATE OR REPLACE procedure align_xrsl_xrsip is
> > /*PURPOSE:  Align xref_structure_insulator_poas (xrsip) with
> > xref_structure_linesection (xrsl)
> > METHOD:  for each record that was added to xrsl, get the original from
> > xrsip and insert a copyinto xrsip with the new structure_cct_id and
> > i_s, o_s.
> > update the original xrsip o_s to min i_s of related recs added to
> > xrsl*/
> > 
> > -- original parent (xrsl.lnsection_id) with new children
> > (xrsl.lnsection_id)
> > cursor c_chld is select * from TEMP_PARENT_CHILD order by parnt,chld;
> > 
> > --source dataset for insertion of new insulator recs
> > cursor c_parnt (scctid number) is select * from
> > xref_structure_insulator_poas where structure_cct_id = scctid;
> > 
> > --old structure_cct_id with the new structure_cct_id
> > cursor c_xrslids (p_id number, c_id number) is
> > select a.oldid old_scctid, b.newid from
> > (select lnsection_id parnt,id oldid,structure_id from
> > xref_structure_linesection where lnsection_id = p_id)a,
> > (select lnsection_id chld,id newid,structure_id from
> > xref_structure_linesection where lnsection_id = c_id)b,
> > temp_parent_child c
> > where
> > a.parnt = c.parnt and
> > b.chld = c.chld and
> > a.structure_id = b.structure_id
> > order by a.oldid,b.newid;
> > 
> > new_os date;
> > exist number;
> > 
> > begin
> > 	 -- for the new xrsl records
> > 	 for chld in c_chld loop
> > 	 	 --get the new and old structure_cct_ids
> > 		 for cid in c_xrslids (chld.parnt,chld.chld) loop
> > 			 -- get the source xrsip records for copying
> > 			 exist := 0;
> > 			 For src in c_parnt(cid.old_scctid) loop
> > 			 	 exist := 1;
> > 				 -- insert the copy of xrsip with updated columns for new
> > structure_cct_id
> > 					insert into xref_structure_insulator_poas (INSULATOR_POAS_ID,
> > STRUCTURE_CCT_ID, INSULATOR_CODE_ID, CONDUCTOR_POSITION_ID,
> > ORIENTATION, NO_UNITS, NO_STRINGS, INSULATOR_ID, I_S, O_S, UPDATED,
> > UPDATED_BY, DISTCONDATTACHABOVEGNDFT, ATTACHED_TO_ID,
> > CONDUCTOR_DAMPER_TYPE_ID, CONDUCTOR_DAMPER_QTY)
> > 					values (src.INSULATOR_POAS_ID, cid.newid, src.INSULATOR_CODE_ID,
> > src.CONDUCTOR_POSITION_ID, src.ORIENTATION, src.NO_UNITS,
> > src.NO_STRINGS, src.INSULATOR_ID, chld.i_s, chld.O_S, sysdate,
> > '180490', src.DISTCONDATTACHABOVEGNDFT, src.ATTACHED_TO_ID,
> > src.CONDUCTOR_DAMPER_TYPE_ID, src.CONDUCTOR_DAMPER_QTY);
> > 		 	 end loop;
> > 		 	 -- get the new o_s for original xrsip rec
> > 			 if exist = 1 then
> > 				 select min_is into new_os from TEMP_NEW_OS_XRSI_POAS where parnt
> > = chld.parnt and id = cid.old_scctid;
> > 				 -- close the original records - update the o_s to min is of new
> > records
> > 				 update xref_structure_insulator_poas set o_s = new_os where
> > structure_cct_id = cid.old_scctid;
> > 			 end if;	 
> > 	 	end loop;
> > 	 end loop;
> > 
> > exception
> >      when others then
> >        rollback;
> > 	   raise;
> > 
> > end;
> > /
Received on Mon Sep 22 2003 - 12:30:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US