Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

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

Features Include:

  • 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:

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.

The Fastest Way to Store / Retrieve a Cross Session Variable
About these ads

44 Responses to “Logger, A PL/SQL Logging and Debugging Utility”

  1. Doug Gault said

    Tyler,

    This looks absolutely amazing, especially the work that you’ve done integrating this into APEX. Just for fun you might have a look at ILO for Oracle (http://sourceforge.net/projects/hotsos-ilo/files/ILO/) That I wrote with Cary Millsap while at Hotsos.

    While they are not exactly the same, some of the ideas in there might be interesting to you.

    Great work!

    Doug

  2. Mark Lancaster said

    Thanks for sharing Tyler.

    I usually include “p_raise boolean default true” in my parameters when logging exceptions.
    The logger code always does a raise, unless explicitly told not to.

    That way, developers can code exceptions handlers as:

    exception
    when others then
    logger.log_error;
    end;

    Worth including?

    Mark

    • Tyler Muth said

      Mark,

      The downside to your suggestion is that if you “raise” from within logger instead of the place where the error occurred, you lose the context of the error. The full context of the error will still be in the logger_logs table as it gets it from dbms_utility.format_error_backtrace. Not sure if I want to encourage that or not…

      Thanks,
      Tyler

  3. Tyson Jouglet said

    There is a typo under advanced use >> error handling.

    select * from logger_logs where error_level = 2;

    should be:

    select * from logger_logs where logger_level = 2;

    • Tyler Muth said

      Tyson,

      Thanks for catching this. I renamed the column recently as “error_level” didn’t make sense for messages other than errors. I corrected the example.

  4. Peter Paul said

    Hi Tyler,

    Respect for the work you’ve done!
    I’ve some experience with log4plsql. From the discription my assumption is that Logger “adds” APEX and AJAX integration. Could you give a comparison?

    Thanks in advance

    • Tyler Muth said

      I used log4plsql a while ago and first got stuck on the installation as I was trying to install on apex.oracle.com. It required grants that the average user didn’t have. I also got a bit overwhelmed with the complexity of it as I was trying to debug some tough issues and really didn’t want to learn a log framework just to solve them. It wasn’t that complex, but at the time all I really wanted was the classic debug messages like “yep, got to location 1″. Anything beyond that was just noise. It also hadn’t had any active development in 2+ years and that concerned me a bit. I also would have preferred more columns so you could sort and filter the logs more (like module, action, client_identifier, etc), though too many columns is also annoying, so I’ve tried to strike a balance based on my personal preference. I think all of my complaints could be viewed as benefits to someone else, so I would encourage people to try both and see which one feels better to them.

      And now for a quick comparison:
      - Output destinations. Logger has 1, it’s a table. log4plsql has about every one you can think of. Personally, I only need 1 destination but I’m willing to listen to others on this point. Tom Kyte actually suggested a more flexible output, such as file, or a call to your own procedure. I’m thinking about these for 2.0.
      - Ease of installation. To be fair, I haven’t installed log4plsql in a while, but my hunch is that logger is easier. I was careful to make sure it installs in a vanilla schema provisioned by APEX and through the SQL Workshop, ie no sqlplus access and no grants from a DBA. I also provide a create_user.sql script or the grants (which are pretty minimal) to make it super simple to install outside of APEX.
      - APEX Integration. Since I used to be on the APEX team and it’s my primary dev environment, a lot of logger was shaped by that environment. APEX sets module = “APEX:APP 123″, action = “Page 4″, and client_identifier = “TYLER.MUTH:123456789″ (user:session). Actually, Tom convinced me to add that to apex back in the day and I convinced Mike Hichwa. It’s SUPER useful on many levels. As such, I made sure these were separate columns and automagically populated. There’s also the log_apex_items feature which I find soooooooo handy. I’m actually trying to get that feature into a future release of APEX…
      - Maturity. log4plsql has been around a whole lot longer than logger, so it certainly has more features and almost surely more production users.
      - Minimal deployment option. Check out the NO-OP feature of logger. If you think about bundling logger with production software, this at least gives you an option that doesn’t require any objects other than the logger package and should not incur any appreciable performance hit. With that said, I would still opt to leave all of logger in place and just set the LEVEL to ERROR.

      Hope this helps,
      Tyler

      • PeterPaul said

        Hi Tyler,
        This certainly helps. Thanks!

      • John Flack said

        I was trying Log4PlSql and had a similar experience to yours, Tyler. A bit more complex than I’d like. But it did install just fine. One problem I have with Log4PlSql is that if you want to change some defaults for a session – say you normally log only fatal errors, but now you need to log for debugging – you have to use a context record on every call. No package variables that you can change and have it remember for the rest of the session.

        So I’m trying Logger. I tried to install it on 10gR1, and started getting errors all over the place, because some of the conditional compilation options and variables don’t work. It also doesn’t install well if you don’t have ApEx installed. I finally created a modified version of the install script, and removed all references that don’t work in pre-10gR2 versions of the database. I’ll make that available if anyone wants it.

      • Tyler Muth said

        John,

        Any chance you can head over to my Bug Reports forum and post your changes? It allows you to upload files. If you’re feeling particularly generous, can you spool to file and install the original code in another schema? I really want to make sure these issues are resolved. It works fine for me in 10gR1, but I have APEX installed there.

        Thanks again,
        Tyler

      • Hewy said

        Nice, Tyler.
        Personally, I’ve used log4plsql extensively.
        Never had install issues. I also wrapped a lot of the functionality into simpler global procedures to mask some of the complexity. Though, I never used some of its more esoteric output options.

        I also dropped in some code to log to a user specific text file which is invaluable. A user elects to turn file debugging on, and from that point on any debug calls will check the user setting and subsequently output the message to a text file. Really useful in a mod_plsql environment.

        Looking forward to digging deeper into your implementation.

  5. Tony said

    The only thing I would change is to make the purge, purge_all, and set_level procedure to use autonomous transactions so that their actions can be committed. The fact that they weren’t was throwing me off for a bit and given me behavior that I wasn’t expecting.

    The other, quicker solution, would be to update the example to:

    logger.set_level(‘ERROR’);
    commit;

    On the whole, this is really nice and I am busing integrating it into a new project right now. Thanks for sharing with all of us.

  6. [...] Last, Tyler Muth introduces Logger, A PL/SQL Logging and Debugging Utility. [...]

  7. Connor said

    Hi Tyler,

    Nice stuff…

    In terms of controlling logging behaviour at session level, we use a similar concept in our bespoke plsql logger (ie, global context) but a simple addition is to have two attributes in the context, which you check in “ok_to_log”, namely:

    1) sys_context(‘logger’,’global_options’)
    2) sys_context(‘logger’,dbms_session.unique_session_id)

    Thus when an external session wants to enable logging for *all* sessions, they simply pop the appropriate value in (1) above, and for an individual session, just pop the value in (2) above. (Similarly, its easy to convert a sid/serial into the string that dbms_session.unique_session_id uses and vice-versa).

    hth
    Connor

    • Tyler Muth said

      Connor,

      I like your suggestion of enabling logging at the session level, but I’m curious how to clean-up the sys_context entries using dbms_session.unique_session_id? Wouldn’t the number of entries continue to grow over time? My first thought is to check v$session to see if the sid/serial is still valid, but then I would need to grant select on v_$session which means it no longer installs in a non-privileged user. Maybe I’m missing some detail of the implementation…

      I really want to add the ability to enable logging based on CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, or ACTION. APEX makes use of these variables automatically, so I could enable logging for a user, an application, or just a page.

      Thanks,
      Tyler

      • Mark Wagoner said

        Can you tell me if the session-specific logging has ever been implemented? I really need this functionality as enabling debug-level logging on one of our production systems would be too resource intensive. Our hope is to allow the user to enable/disable logging just for their session on an as-needed basis.

        However, we are running an Apex application so does this create problems with session information?

        Thanks,
        Mark

  8. [...] unfortunately, PL/SQL has a weaking functionality on the logging and this is why somebody is trying to implement the fully functioning logging ability with [...]

  9. My attempts at installation are failing because of trigger bi_logger_logs on the line:

    :new.user_name := nvl(v(‘APP_USER’),user);

    I can’t see any reference to a V function. Is this 11-specific?

    • Tyler Muth said

      Neil,

      Sorry about that. v() is an APEX function and clearly you are not running APEX. I updated the source for the next build, but in the meantime can you change the trigger code to this:

      create or replace trigger  bi_logger_logs 
      before insert on logger_logs 
      for each row 
      begin	
      	select logger_logs_seq.nextval into :new.id from dual;
      	:new.time_stamp 	:= systimestamp;
      	:new.client_identifier	:= sys_context('userenv','client_identifier');
      	:new.module 		:= sys_context('userenv','module');
      	:new.action 		:= sys_context('userenv','action');
          $IF $$APEX $THEN
              :new.user_name 		:= nvl(v('APP_USER'),user);
          $ELSE
              :new.user_name 		:= user;
          $END
      	:new.unit_name 	    :=  upper(:new.unit_name);
          $IF $$FLASHBACK_ENABLED $THEN
              :new.scn := dbms_flashback.get_system_change_number;
          $END
      end;
      /
      show errors

      Let me know if you run into any more issues.

      Thanks,
      Tyler

  10. Alessandro Nazzani said

    Hi Tyler.

    Any news about 10gR1 compatibility?

    Oh and… thanks for sharing, of course. :)

    Alessandro

    PS: I was unable to find a way to post this in the project discussion at samplecode (where’s the reply button?), so apologies for “spamming” your blog.

    • Tyler Muth said

      Alessandro,

      Regrettably, 10gR1 doesn’t support the conditional compilation directives that I’m using to enable features such as APEX. I’ve discussed this with some key people in the community and the consensus is 10gR2+ is a “reasonable” target. This is in line with the next release of APEX that will only support 10.2.0.4 or higher. I just don’t have the time to maintain that many dev databases.

      With that said, it shouldn’t take much work to rip out the offending conditional compilation directives and test it. If that’s something you’re willing to do, I’m happy to add you to the project and whenever I release a new version you can take that and make a 10.1 build that you can commit to the svn repository.

      Sorry,
      Tyler

      • Alessandro Nazzani said

        Tyler,

        thanks for your reply.

        If no one more experienced is interested, I’m more than willing to share my modifications (I *need* a 10gR1 logger at the moment).

        Do you have a schedule for the next release (in the project home page version 1.2.0 is announced, but the download link is for 1.1.0)?

        Alessandro

  11. Would you consider defaulting logger_logs.unit_name to something larger than 30? In our testing, schema.package XXXX_OWNER.EXPERIENCE_RATING_BATCH, which is a perfectly legal package name, was too large for the column. Otherwise, we are LOVING this utility.

  12. Tyler Muth said

    Just a note to let everyone know that I just released logger 1.2.0:
    http://tylermuth.wordpress.com/2009/11/23/logger-1-2-0-released/

    Thanks,
    Tyler

  13. Rahul said

    Hi,

    Nice posting. Thanks a lot for sharing.

  14. Hi Tyler,

    Thank you for creating such an excellent logging tool.

    Excellent job!

    Martin

  15. Amitabh said

    Hi Tyler, Nice work. I’m curious to know how does your framework on log rotation? could you explain briefly?

  16. Tony Miller said

    Tyler,
    Just tried installing logger on a hosted instance of APEX (NOT Oracle’s) and the following code in install_logger crashed (Error returned: ORA-02012: missing USING keyword):

    declare
    – the following line is also used in a constant declaration in logger.pkb
    l_ctx_name varchar2(35) := substr(sys_context(‘USERENV’,’CURRENT_SCHEMA’),1,23)||’_LOGCTX’;
    begin
    execute immediate ‘create or replace context ‘||l_ctx_name||’ using logger accessed globally’;

    merge into logger_prefs p
    using (select ‘GLOBAL_CONTEXT_NAME’ PREF_NAME, l_ctx_name PREF_VALUE from dual) d
    on (p.pref_name = d.pref_name)
    when matched then
    update set p.pref_value = d.pref_value
    when not matched then
    insert (p.pref_name,p.pref_value)
    values (d.pref_name,d.pref_value);
    end;

  17. brebu said

    Hi Tyler

    Thank you very much for your package. I have included it in my project also. A nice to have in our environment whould be a parameter which will log in a non-autonomous transaction. There are some situations where COMMIT OR ROLLBACK are given not in procedure or package but outside in another transaction which would call these. Would be nice to have logging just when a COMMIT in the real transaction took place. Otherways is no way to know based on loging that the transaction was commited. Maybe a special logger.log_nonautonomous with parameter DEBUG, WARNING,INFO etc… or existing logger.log etc… with a parameter p_commit TRUE OR FALSE…
    thank you very much

  18. NP said

    Tyler,

    Thanks for writing this logger. I use it a lot.

    One minor issue:
    I’m using the logger in a stored procedure which is being called by a trigger in another schema. The logger fails to insert into logger_logs – UNIT_NAME column because the length of the . exceeds 30 characters.

    Thanks again,

    NP

  19. Keyloggers…

    [...]Logger, A PL/SQL Logging and Debugging Utility « Tyler Muth’s Blog[...]…

  20. tony said

    Hi, The link to the code no longer seems to work.

  21. MJT said

    Hi Tyler,
    Looks a handy piece of code! Do you have a version what will work on 10gR1?
    If not, then is there anyone else out there who can point me in the right direction?
    Thanks

  22. Nigel Olding said

    The download link doesn’t work (6 months, now, at least), and due to the non-unique name of your project (logger? thanks, that’s easy to google) and the brain-dead oracle website search (hence the need for google), your code is lost.

    • Doug Gault said

      No.. Not lost at all.. In fact, if you were to search Tyler’s blog itself for the phrase “logger”, you would have found this:

      http://tylermuth.wordpress.com/2011/11/09/logger-project-moved-temporarilly/

      Tyler, Any news on a permanent home for logger?

      • Nigel Olding said

        Thanks, I just came back to say I’d found it and here is the link, when, lo, here is the link!

        Implying, btw, that the follow-up email notification mechanism may be broken – it didn’t work for me, anyway, this (first) time.

        Thanks again, Doug and Tyler.

      • Nigel Olding said

        Now I’m back to say that when trying to confirm that I want to follow the blog’s posts (click on a button in a confirmation-request email), I get a ‘connection was reset’ error.

  23. shreej said

    Hi Please tell me list of grants required to install the logger package.I am getting the following error, insufficient privilege while
    Error starting at line 86 in command:
    exec logger.log(‘hello world’)
    Error report:
    ORA-01031: insufficient privileges
    ORA-06512: at “SYS.DBMS_SESSION”, line 90

    Please help.

  24. [...] Read More: http://tylermuth.wordpress.com/2009/11/03/logger-a-plsql-logging-and-debugging-utility/ [...]

  25. Justinas said

    Hello
    since project sourced are not reachable I’ve shared version that I have here: https://github.com/gulbinas/plsql-logger

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers

%d bloggers like this: