Introduction

I am sure most of you instrument your APEX and PL/SQL code using a logging tool. APEX has a robust logging mechanism built around the apex_debug PL/SQL and apex.debug JavaScript APIs, and many in the Oracle APEX and Database community (including me) use the Open Source logging tool Logger.

Whatever tool you use, good log messages can be invaluable during development and save developers time. I am guessing fewer of you think about logs once your App is in production. Errors occur, users create tickets, and bugs get fixed. I believe that we can do better. Everyone wins if we action warning and error messages and fix issues before users find them. Users do not have to deal with bugs, and developers don't have to deal with production support issues at inopportune times.

In this post, I will discuss three topics related to making the information in your logs actionable 🚀

1. Good Log Message Principles
2. Timely Notification of Errors
3. Make Decisions with your Logs

Good Log Message Principles

This list represents a set of best practices I use when it comes to logging:

• The log level debug, information, warning, and error should mean something. Understand when and where to use each type of log message
  • Log Information level messages to inform you what features are being used
  • Log Warning level messages for exceptions that are handled by your code but could cause issues if they are allowed to persist. The user may not be aware that the issue occurred, but it may still indicate a problem. Warning messages should be actioned but may not need to be addressed immediately. Examples include missing configurations, repeated attempts to use an expired token, failed login attempts, certain validation failures occur that you would not expect to happen, etc.
  • Log Error level messages for errors that require human intervention to resolve. This could be PL/SQL errors, an unhandled exception, an error calling a web service, a critical configuration missing, a scheduled job failing, etc. Error level logs should be reserved for issues that need to be addressed urgently
• Don't spam your logs with Error or Warning level messages that are not actionable. If you see these kinds of logs, either fix them or change them to Debug level messages
• Switch Debug level logging off in production but make sure you can turn it on when necessary. Even better have the ability to switch debug level logging on for a specific user or session
• Write meaningful log messages that describe in English what is going on at that point in your code. You can even use well written debug level messages instead of comments
• Make sure you capture as much detail as possible about the session state, who the user was, and what they were doing during a Warning or Error event. You may be looking at an error twenty-four hours after the fact
• Avoid adding log messages in loops that have thousands of iterations. Too much logging can impact performance adversely
• Make sure you log context so you can quickly identify the APEX page, PL/SQL procedure, REST Service, etc. that generated the log message
• Make sure you have a routine to purge logs after some time. Automated log purging is taken care of for you with APEX and Logger logs
• Don't log sensitive information like passwords, API keys, etc.
• Pick a logging tool and stick to it
• Educate your team on the tool you do use and provide best practices and examples
• Don't forget to add log messages to any PL/SQL-based ORDS services
• If you are using APEX, don't forget to create an Error Handling Function so that you can trap APEX errors and create log messages using the logging tool of your choice

Timely Notification of Errors

Logs are most valuable when Warnings and Errors can be communicated and actioned promptly. This allows developers to be proactive in resolving issues. You may even be able to fix bugs before users notice there is an issue.

As an IT professional, you look much better if you can respond to a bug report with "yes, we noticed that issue, and we are working on a fix now", rather than "oh crap, I better see if this bar has wi-fi 🍺".

To achieve this, you must notify support teams as soon as possible after errors occur. With the introduction of APEX Automations, we are better placed than ever to build scheduled database jobs to do this.

An automated approach to error log notification could look something like this:

• An APEX Automation runs on a schedule
• The APEX Automation calls a PL/SQL procedure which checks for new warning and error log messages since the last time it ran
• The PL/SQL procedure generates an HTML document listing the warning and error messages and emails the support team using APEX_MAIL
• For bonus points, you could even send Error log messages to a Teams Channel or via SMS

❗There is not much point in sending emails in the middle of the night. Whatever notification method you use should be appropriate for when your support team is available and how they receive urgent messages. If you are a 24/7 shop, you will likely be integrating with something like PagerDuty.

📖 You should also build a daily digest email that summarizes all warnings and errors from the previous day. A digest email can help ensure errors do not fall through the cracks and help identify trends. A digest email may even be all you need for smaller non-24/7 environments.

Action the Logs

I can't stress enough the importance of acting on warning and error messages.

If the same message keeps coming up on the alert email, then either fix it or change the level of the message to something other than Error or Warning. If it stays on the Alert email daily, people will lose faith in the urgency of the alert.

Capture All the Logs

Even if you are using something like Logger, you should check other tables where APEX could be logging warnings and errors. These include:

• APEX Debug Logs apex_debug_messages
• APEX Failed Login Attempts apex_workspace_access_log
• APEX Automation Logs apex_automation_log, apex_automation_msg_log
• REST Data Synchronization Logs apex_rest_source_sync_log
• REST Web Service Activity Logs apex_webservice_log
• Logger (if you are using it) logger_logs

Make Decisions with your Logs

Logs can be beneficial in compiling actionable management information. Here is a list of potential metrics (and the tables they can be generated from), along with some actionable insights that can be derived from them.

💡All of these metrics can be generated using SQL. I recommend generating these metrics Quarterly and presenting them to senior management. Regular reporting of these metrics facilitates discussion, reinforces APEX's importance to the organization, and helps identify trends.

Conclusion

Logging is a fundamental part of the development and support lifecycle. With some extra thought, you can make your logs actionable and use them to drive management decisions. I hope this post inspires you to look at how you do logging in to your organization.