-- Start of DDL Script for Trigger SYS.TA_ERRORS
-- Generated 11/11/2010 09:24:08 from SYS@vs01

CREATE OR REPLACE TRIGGER ta_errors
 AFTER
  SERVERERROR
 ON DATABASE
declare
  instance varchar2(20);
  captured_sql varchar2(1000);
  terminal gv$session.terminal%type;
  machine  gv$session.machine%type;
  program  gv$session.program%type;
  osuser   gv$session.osuser%type;
begin
  if ora_server_error(1) not in (1,31,54,604,900,902,903,904,905,906,907,908,909,911,913,917,918,919,920,921,922,923,924,926,927,928,932,933,936,937,938,942,947,955,957,959,964,971,972,979,984,990,997,1002,1008,1013,1017,1031,1400,1401,1407,1408,1418,1427,1430,1438,1440,1445,1446,1453,1458,1468,1476,1490,1704,1719,1720,1722,1735,1747,1756,1785,1789,1791,1795,1821,1830,1835,1839,1840,1841,1843,1847,1849,1850,1858,1861,1917,1927,2000,2019,2051,2248,2158,2261,2264,2267,2270,2289,2290,2291,2292,2298,2299,2402,2404,2429,2430,2443,2449,3217,4043,4070,4071,6502,6550,6564,8108,12154,12899,20000,20034,22920,24372,24761,25228,30021,29902,20620,20601,25254,20501,20101,03001,31050,15000,20206,00028) then --,00028 --> kill session
 --eliminagco de erros para TODAS as instances
    select instance_name into instance from v$instance;
    if instance = 'vs03' and (  --eliminagco de erros para instances especmficas
                             (ora_login_user='LINKDATA' and ora_server_error(1) in (936)) or
                             (ora_login_user='NEWSLETTER_WEB' and ora_server_error(1) in (1002)) ) then
      null;
    else
       begin 
          SELECT q.sql_text, s.terminal, s.machine, s.program, s.osuser INTO captured_sql, terminal, machine, program, osuser
          FROM   gv$sql q, gv$sql_cursor c, gv$session s
          WHERE  s.audsid = audsid
          AND    s.prev_sql_addr = q.address
          AND    q.address = c.parent_handle;
       exception
       when no_data_found then
          null;
       end;
      admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' User: '||ora_login_user||' - Erro: '
      ||ora_server_error(1)||' Msg: '||ora_server_error_msg(1)||'  Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'),
      'Terminal: '||terminal||chr(13)||'Machine: '||machine||chr(13)||'Program: '||program||chr(13)||'Osuser: '||osuser||chr(13)
      ||captured_sql);
    end if;
  end if;
end;
/


-- End of DDL Script for Trigger SYS.TA_ERRORS