How to write messages to the Oracle alert log and custom trace files - dbms_system.ksdwrt

I've been finding this useful on occasion because I have plenty of jobs reading and reporting on alert log errors, so it's saved me having to think of another way to yell "hey DBA" :

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');


example:

exec dbms_system.ksdwrt(2, 'ORA-09999 hey you wake up and investigate this');


You can put a wrapper around the call and grant execute on the function:

create or replace function write_alert_log(log_or_trace in number, text_message in varchar2) return number

is

begin

  SYS.DBMS_SYSTEM.KSDWRT(log_or_trace,text_message);

  return 0;

  exception

  when others then

  return 1;

end;