Raise alert.log error
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.
The steps below show you how.
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;
….
Posted: October 23rd, 2009 under Oracle Scripts.
Tags: 0000, dbas, dbms system, dbms_system.ksdwrt, email, exception handler, handy utility, jan 24, raise alert.log error, scott grant, scott test, substr, user error