Raise Alertlog Error
before implementing them on a production server.
Came across this very handy utility recently.
Warning, it is unsupported by Oracle, so don't base your mission-critical alerts on this method.
Let's say you are creating a stored proc as user scott, and instead of
writing your
trapped error message to a table, an error file or sending an email, you
would like it to be reflected
in the database's alert.log.
If not documented, this is guaranteed to make you unpopular with DBAs who manage your database in your absence...
First, grant execution privileges to scott:
as sys:
SQL> grant execute on sys.dbms_system to scott;
Grant succeeded.
Now run and test a procedure which will write to the alert.log
declare
Mess varchar2(200);
begin
Mess := 'ORA-USER_ERROR'||', scott.test_proc, '||', '||substr(SQLERRM,1,100);
sys.dbms_system.ksdwrt(2,Mess);
commit;
end;
This is how it reflects in the alert.log (ORA-0000 because there is no
error.)
Thu Jan 24 14:57:15 2008
ORA-USER_ERROR, scott.test_proc, ORA-0000: normal, successful completion
Lastly : implement it in your procedure's exception handler section:
...
exception
when others then
Mess := 'ORA-USER_ERROR'||', scott.test_proc, '||', '||substr(SQLERRM,1,100);
sys.dbms_system.ksdwrt(2,Mess);
commit;
....
