OLF: Appenders
In keeping with a Log4j mode (i.e. levels from the last post) the Oracle Logging Framework (OLF) supplies two appenders or output modes. One outputs to a predefined table and the other to the console (i.e. dbms_output). This is done mainly to keep things simple. Other Oracle logging tools create more code to manage their output modes than they do to manage the actual logging, and this was not something that I wanted to repeat.
The table based appender inserts all logging messages to a single database table.The table has the following format:
SQL> desc dblog_log Name Null? Type ----------------------------------------- -------- ---------------------------- LOGID NOT NULL NUMBER USERNAME NOT NULL VARCHAR2(30) SID NOT NULL NUMBER INST_ID NOT NULL NUMBER DATETIME NOT NULL TIMESTAMP(6) MODULE_NAME VARCHAR2(30) ACTION_NAME VARCHAR2(30) LOG_LEVEL VARCHAR2(10) TASKTIME NUMBER OBJECT_NAME VARCHAR2(61) LINE_NO NUMBER TEXT CLOB SQL_ERRNO NUMBER SQL_ERRMSG VARCHAR2(100) SQL_BACKTRACE VARCHAR2(1000) SQL>
The key output fields include tasktime, object_name and line_no from the call stack, text, the SQL error number, message and backtrace if an error.
The second appender uses DBMS_OUTPUT which requires running the code interactively. This appender is primarily meant to facilitate debugging since most code is not run from SQL*Plus. The output is formatted into two lines of the format:
level:user:sid:inst_id:datetime:module:action level:tasktime:owner.object_name:line_no:text
Errors include three additional lines for error number, message and the backtrace:
ERROR: Oracle error code: 100 ERROR: Oracle error message: ORA-01403: no data found ERROR: Backtrace: ORA-06512: at line 8
The following anonymous PL/SQL block was run with a config level of INFO:
SQL> set serveroutput on;
declare
l_num number;
begin
ilo_task.begin_task(
module => 'module',
action => 'action');
--
dblog.info('Before statement');
select 1 into l_num from dual;
dblog.info('After statement');
--
ilo_task.end_task;
end;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
INFO:DBAMON:38:1:12-APR-12 01.02.01.051535 AM:module:action
INFO:NULL:DBAMON.ANONYMOUS BLOCK:Line 8:Before statement
INFO:DBAMON:38:1:12-APR-12 01.02.01.054261 AM:module:action
INFO:NULL:DBAMON.ANONYMOUS BLOCK:Line 10:After statement
PL/SQL procedure successfully completed.
SQL>
The database log table entries were:
USER DATETIME MODULE ACTION LEVEL OBJECT_NAME LINE TEXT ------ ---------------------------- ------ ------ ----- ---------------------- ---- ---------------- DBAMON 12-APR-12 01.02.01.053545 AM module action INFO DBAMON.ANONYMOUS BLOCK 8 Before statement DBAMON 12-APR-12 01.02.01.056147 AM module action INFO DBAMON.ANONYMOUS BLOCK 10 After statement
For an error condition the error columns look like:
USER MODULE ACTION LEVEL OBJECT_NAME LINE TEXT errno errmsg backtrace ------ ------ ------ ----- ---------------------- ---- ------------------------ ----- ------------------------ -------------------- DBAMON module action ERROR DBAMON.ANONYMOUS BLOCK 13 dblog.error test message 100 ORA-01403: no data found ORA-06512: at line 8
So those are the two planned output modes for the Oracle Logging Facility. Next up we’ll talk a little bit more about the input configuration settings.
Entries