Writing to the alert log from PL/SQL with DBMS_SYSTEM.ksdwrt

Writing to the alert log from PL/SQL in Oracle Databases can be a convenient way to monitor significant events or errors that occur during database operations. For example, you could write custom messages to the alert log to track the execution of specific PL/SQL procedures, or to log particular errors that occur during data processing tasks. This can be particularly useful for database administrators who need to troubleshoot issues or analyze the performance of the database.

However, it’s important to note that while the DBMS_SYSTEM.KSDWRT package can be used to write messages directly to the alert log from PL/SQL, this is an undocumented and unsupported feature. As such, it should be used with caution and thorough testing.

In the following example, the first parameter of DBMS_SYSTEM.KSDWRT indicates that the message should be written to the alert log, and the string that follows is the custom message to be written.

set SERVEROUTPUT on

begin
dbms_system.ksdwrt(dbms_system.alert_file, '*** Start writing to alert_file ***');
dbms_system.ksdwrt(dbms_system.alert_file, '     Some output from pl/sql');
dbms_system.ksdwrt(dbms_system.alert_file, '     More output from pl/sql');
dbms_system.ksdwrt(dbms_system.alert_file, '*** Stop writing to alert_file ***');
end;

Now, let’s check the output in the alert log file.

[oracle@primary ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Apr 15 17:10:09 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"

adrci> show homes
ADR Homes: 
diag/rdbms/orcl/orcl
diag/tnslsnr/primary/listener

adrci> set home diag/rdbms/orcl/orcl

adrci> show alert -tail 10
2024-04-15 17:20:08.781000 +01:00
*** Start writing to alert_file ***
     Some output from pl/sql
     More output from pl/sql
*** Stop writing to alert_file ***
adrci> 

Use the parameter dbms_system.trace_file to write directly to the trace insteads of the alert log.

EXEC dbms_system.ksdwrt(dbms_system.trace_file, '*** Writing to trace_file');

For more insights into diagnostics in Oracle Databases, I highly recommend reading my latest post about the Automatic Diagnostic Repository (ADR). It provides a wealth of information on how ADR can simplify the task of diagnosing and resolving issues in Oracle Databases.