Re: Firing trigger cuts db connection

"freecycle" <f.cycle_at_yahoo.com> wrote in news:26d83d8f-
> 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);

    [snip]
> casecount);
> select count(*) into candidates from cleardown_comments;
> endrun:=sysdate;
> end loop;
> end;
> /
> show errors
> commit;

Usually an abnormal end of process shows up as a trace in the user_dump_dest directory. Check the alert.log as well

Cheers, Roelof Received on Wed Jul 23 2008 - 13:06:28 CDT