5 Logging Manager and Autonomous Error Logging
5.1 Why a Logging Manager?
A major milestone in a programmer’s career is recognizing the need for a disciplined, consistent way for applications to communicate from the back end. Logging is not merely printing error messages—it is the ability to observe, diagnose, and understand what the code is doing at each stage of execution.
Most developers begin by scattering ad-hoc error messages throughout their code. Over time, these messages cluster around key logic branches, giving a partial view of where failures occur. A logging manager elevates this approach: instead of scattered messages, it introduces a structured system with clear rules, consistent formats, and predictable behavior.
The core idea is simple but powerful: a logging manager offers a small, standardized set of procedures that any component in the application can call to report activity, status, or problems. These calls write to one or more logging tables in a uniform structure, allowing developers and operations teams to trace execution paths, analyze failures, and monitor behavior across the entire system using the same tools and the same conventions.
5.2 Logging Manager Concepts
A simple log manager might expose methods like these:
- Log that we entered the CASE table ETL logic. What is running
- log_manager.log_module_start('ETL merge', 'table_case');
- Log that we exited the CASE table ETL logic and everything is OK or NOT. Whether it worked or failed
- log_manager.log_module_end('OK');
- log_manager.log_module_end('ERROR');
- Log an error in the exception block, including a checkpoint code. Where in the code an error occured
- log_manager.log(check_point, error_code);
This gives the developer a standard way for all application code to report :
What is running, When it started and ended, Whether it worked or failed or Where in the code an error occurred
This makes it much easier to answer questions like:
- “Which modules ran last night?”
- “Which step is failing in this ETL pipeline?”
- “What was happening right before the error?”
5.3 Real-World Batch Example
At one client, I was the lead developer responsible for a data mart / data warehouse running a large batch of Oracle jobs every evening.
From about 6 PM (after business hours) until 3 or 4 AM, the system was busy:
- ETL jobs moving data from the CRM system into the data mart
- Long-running reports being calculated as data became available
- Data warehouse jobs loading dimensions and facts for the previous day
Many of these processes ran in parallel, and each could fail in different ways. Errors were allowed to happen (they always do), but they could not be allowed to stop or “gum up” the overall nightly flow.
The “air traffic control” for all of this was the logging manager.
To make this work, the logging manager needed at least three key pieces of information for each log entry:
- Application – which system the code belongs to
- Module – which major process or sub-process is running
- Code checkpoint (check_point) – where in the code we are when we log
In that project, a log entry might look like this conceptually:
- Application: CLARIFY (the CRM system)
- Code block: TABLE_BUILD, FACT_MERGE, DIM_MERGE, REPORT_BUILD, or TABLE_MAINTENANCE
- Specific module instance: MERGE TABLE CASE, MERGE TABLE PERSON, and so on
Logging happened at two levels:
- Assertions (module start/end)
These are always written and act like checkpoints:
- “We entered module X at time T.”
- “We exited module X with status OK/ERROR and row counts.”
Even if there was no work to do (for example, no new customers, so no rows merged), the log still shows that the module ran. This lets you build a clean timeline of all processing, independent of actual data changes.
- Error Logs
These are written inside exception handlers or other error paths, and they:
- Reuse the same application and module names as the assertions
- Add the error code and message
- Add a code checkpoint (check_point) inside the code
The checkpoint is the piece many systems are missing.
5.4 The check_point Pattern
In PL/SQL, there is no built-in, reliable way to say “I’m at line X” in a way that will still be correct after edits, refactoring, or code formatting changes. Line numbers in error messages are helpful, but they are not always exact or stable over time.
To solve this, I use a convention: a string variable called check_point.
- check_point is usually VARCHAR2(200)
- Every major logical step in the code sets check_point to a unique label
- When an error occurs, we log the current value of check_point
Here is a typical pattern:
DECLARE
check_point VARCHAR2(200);
BEGIN
-- Log that we entered the CASE table ETL logic.
log_manager.log_module_start('ETL merge', 'table_case');
check_point := '10 start';
-- Code block: get user name
<<code block return: user_name>>
check_point := '20 entering code block with user name, user_name = ' || user_name;
-- Code block: use USER_NAME and return STATE
<<code block return: state>>
check_point := '30 entering code block with state = ' || state;
-- Code block: do main work
<<code block>>
log_manager.log_module_end('OK');
EXCEPTION
WHEN OTHERS THEN
log_manager.log(check_point, SQLCODE);
log_manager.log_module_end('ERROR');
END;
/
Example 2 – LOG_MANAGER usage with check_point variable
Key points:
- check_point is only set in forward-moving code; you don’t attempt to “fix it up” after the fact.
- When an exception occurs, the last value assigned to check_point is logged.
- That tells you the error happened somewhere between that checkpoint and the next one that would have been set.
If you are consistent with your check_point markers—usually aligned with major comment blocks—you can always identify approximately where an error occurred, even in a large procedure.
An added bonus: during development, you can concatenate variables into check_point to include runtime values:
check_point := '40 validating order, order_id = ' || l_order_id;
This gives you near real-time diagnostics without having to attach a debugger. You can use this information to debug complex ETL or batch flows much faster.
5.5 Autonomous Error Logging (DEBUG_PKG)
This section introduces a simple, reliable way to log diagnostic information from PL/SQL code without ever impacting your main transaction.
The pattern relies on Oracle’s PRAGMA AUTONOMOUS_TRANSACTION, which allows logging code to:
- Run in its own transaction
- Commit independently of the caller
- Swallow all errors so logging never breaks production code
The result is a “fire-and-forget” logger that can be called from any code path—stored procedures, functions, triggers, or anonymous blocks—without risk.
Robert Diekmann's Dev Demo Platform