The DBMS_SYSTEM Package (ksdwrt,set_sql_trace_in_session)

I have found a very useful package. DBMS_SYSTEM which has several functions, but I have choose two of them ksdwrt and SET_SQL_TRACE_IN_SESSION.

Story:

I had the following problem: I have configured EM alert on ORA- errors. But EM was not catching error ORA-20018. As you see error code is between [-20000]-[-20999], so it is user-defined error.
EM catches errors that are generated in alert.log. Not all errors are going to alert.log especially user defined errors.

So let’s introduce ksdwrt that helped me to solve this problem.

ksdwrt

Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);

Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Anther usefull method:

SET_SQL_TRACE_IN_SESSION

Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

The values for SID and SERIAL# can be found using the V$SESSION view.

End of the story:

I have run:

begin
dbms_system.ksdwrt(2,’ORA-20118: myerror’);
raise_application_error(-20118, ‘myerror’);
end;

And error was placed in alert.log. So EM was able to see it.

Source: https://oracle-base.com/articles/8i/dbms_system