Re: Firing trigger cuts db connection

On Jul 23, 8:34 pm, freecycle <f.cy..._at_yahoo.com> wrote:
> 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;

Uh-oh... Coming from MS SQL ground, aren't you? ;) This use of "temporary" tables (DELETE, INSERT, use the temporary result set,) is common in MS SQL, but things are not done this way in Oracle. First of all, as others already stated, this doesn't look like a task for a trigger, let alone FOR EACH ROW trigger. Secondly, if you can't invent a way to do what you try to do without temporary data sets, Oracle has GLOBAL TEMPORARY tables that have a bit in common with SQL Server's # tables.

As of ORA-3113 - this can be a sign of a server-side core dump, which is usually accompanied by an entry in the alert log, a core dump file and sometimes a trace file. You asked something extraordinary from Oracle and it died trying to obey your will. :) Check the alert log to see if there's an ORA-7445 or some other internal error recorded at about the same time you receive ORA-3113.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jul 24 2008 - 04:42:07 CDT