Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Explicit Cursor in a For Loop and crashing database
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;
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