Firing trigger cuts db connection

From: freecycle <f.cycle_at_yahoo.com>
Date: Wed, 23 Jul 2008 09:34:09 -0700 (PDT)
Message-ID: <26d83d8f-ac10-4433-a008-fb648bdf5ed0@k13g2000hse.googlegroups.com>

Hi,
I've created a trigger using the script below. whenever I fire it by the statement
update site_control_t set oid = oid;
I get
ERROR at line 1:
ORA-03113: end-of-file on communication channel and I'm no longer connected to Oracle.
Can anyone suggest a reason please, this is driving me crazy! Thanks in advance,
|FC

create or replace trigger site_control_t_update_trg before update on site_control_t for each row declare

    maxruntime number(12):=0;
    retentiontime number(12):=0;
    maxcasedate char(8);
    casedate char(8);
    startrun date;
    endrun date;
    startloop date;
    endloop date;

    candidates number(12):=0;     -- cases in cleardown_comments
    deletions number(8):=0;       -- cases deleted
    casecount number(8):=0;       -- cases remaining

    sql_statement varchar(1000);
begin

    startrun:=sysdate;
    maxruntime:= 1; -- minutes
    retentiontime:= 36; -- months
    maxcasedate:=to_char(sysdate - round(retentiontime * 365.25 / 12),'YYYYMMDD');
    delete from cleardown_comments;
    insert into cleardown_comments

        select case_number,
               substr(max(date_time),1,8)
          from case_comment_t
         group by case_number
        having (substr(max(date_time),1,8) <= maxcasedate)
           and (substr(case_number,1,8) <= maxcasedate);

    select count(*) into candidates from cleardown_comments;     endrun:=sysdate;
    casedate:='';
    while (trunc(to_number(endrun-startrun)*1440) < maxruntime) -- main loop

      and (candidates > 0)
        loop
            startloop:=sysdate;
            select substr(min(case_number),1,8)  -- get deletion date
              into casedate
              from cleardown_comments;
            delete from cleardown_cases;
            insert into cleardown_cases
                select ca.case_number
                  from case_t ca,
                       cleardown_comments co
                 where (ca.case_number = co.case_number)
                   and (ca.case_number <= casedate || '99999')
                   and (ca.open_dt <= to_number(casedate));

            select count(*) into deletions from cleardown_cases;
            delete from case_address_t
             where case_number in (select * from cleardown_cases);
            delete from case_adjust_t
             where case_number in (select * from cleardown_cases);
            delete from case_advice_t
             where case_number in (select * from cleardown_cases);
            delete from case_comment_t
             where case_number in (select * from cleardown_cases);
            delete from case_eft_t
             where case_number in (select * from cleardown_cases);
            delete from case_field_t
             where case_number in (select * from cleardown_cases);
            delete from case_print_t
             where case_number in (select * from cleardown_cases);
            delete from case_prov_t
             where case_number in (select * from cleardown_cases);
            delete from case_research_t
             where case_number in (select * from cleardown_cases);
            delete from case_tran_t
             where case_number in (select * from cleardown_cases);
            delete from delete_queue_t
             where d_que_case_number in (select * from
cleardown_cases);
            delete from interest_t
             where case_number in (select * from cleardown_cases);
            delete from res_queue_t
             where case_number in (select * from cleardown_cases);
            delete from research_det_t
             where case_number in (select * from cleardown_cases);
            delete from research_rltd_t
             where case_number in (select * from cleardown_cases);
            delete from research_susp_t
             where case_number in (select * from cleardown_cases);
            delete from case_t
             where case_number in (select * from cleardown_cases);
            delete from cleardown_comments
             where (case_number <= casedate || '99999');
            select count(*) into casecount from case_t;
            endloop:= sysdate;
            insert into cleardown_log values (
                to_char(sysdate,'YYYYMMDD HH24:MI:SS'),
                casedate,
                deletions,
                to_char(trunc(to_number(endloop-startloop)*24)) || ':'


||

                to_char(mod(trunc(to_number(endloop-startloop)*1440),
60)) || ':' ||
                to_char(mod(trunc(to_number(endloop-startloop)*86400),
60)),
                casecount);
            select count(*) into candidates from cleardown_comments;
            endrun:=sysdate;
        end loop;

end;
/
show errors
commit; Received on Wed Jul 23 2008 - 11:34:09 CDT