Logger, A PL/SQL Logging and Debugging Utility
Posted by Tyler Muth on November 3, 2009
I’ve been gradually building out a logging utility for PL/SQL over the last year or so. It’s been a huge help on some complicated projects, especially in APEX Applications with a lot of AJAX. I’m sure most people reading this have been stuck on a problem, created a logging table, and sprinkled a few inserts in their code to see what’s going on and when. It’s amazing how much faster you resolve these issues if the table is already there and the inserts are now just a short procedure call. Anyone that’s ever used Firebug probably can’t imagine it without console.log() or console.dir(). Yes, you can use dbms_output.put_line or htp.p, but those fall over pretty quick in a web environment with HTTP Posts or worse, a lot of AJAX Posts / Gets. Additionally, if you’re a Tom Kyte groupie, you’ve probably heard him talk about the value of instrumentation more than once.
Now that you know the “why”, lets talk about what Logger is. It’s just a couple of tables and a PL/SQL package that make all of this easier for you. I’ll give you a brief summary of features here, but for full details you should go to the project page at https://logger.samplecode.oracle.com. So, the super short demo is:
logger@orcl> exec logger.log('hello world'); PL/SQL procedure successfully completed. logger@orcl> select * from logger_logs_terse; ID LOGGER_LEVEL TIME_AGO TEXT ------- ------------ -------------------- --------------------- 48 16 35 seconds ago hello world
- Easily Enable / Disable all logging
- Minimal Overhead. There’s even a NO-OP version of the package that you can use for production if you’re really paranoid. It’s just a stub of the logger package that doesn’t write to or depend on any tables (or any other objects).
- Automatically purges debug messages older than 7 days. This is completely configurable
- Ability to time blocks of code
- Log sys_context(‘userenv’,”) variables
- Log APEX Item Names and Values
- Automatically captures key session information such as module, action, client_identifier including APEX session ID, timestamp, call stack.
I’d like to thank all of the community members that tested and contributed ideas for logger including:
- Tom Kyte
- Chris Beck
- John Scott
- Dimitri Gielis
- Anton Nielsen
- Kirk McGowan
- Everyone that commented on my previous post “The Fastest Way to Store / Retrieve a Cross Session Variable“. That was a fun exercise and I think we uncovered a lot of interesting concepts in the comments.
I would love to get more feedback from the community as well. You can either comment here, or use one of the 3 discussion forums I created for logger listed on the project page just above the download link.