1 2 Previous Next 17 Replies Latest reply on Mar 13, 2007 3:31 PM by Billy~Verreynne

    pl/sql event logging

    566180
      hi,

      I am writing a pl/sql schema in which changes will be made to a database. i want to log all the events such as informational messages, errors, warnings etc. I was wondering if there were any ideas on the best approach for this.

      thanks
        • 1. Re: pl/sql event logging
          531621
          Please define informational messages, errors, warnings ;-)

          You could create one or more tables inside your schema that get filled out of your procedures/functions e.g. When a function runs into an exception, you write this information to the table, timestamp, add user information, etc etc.

          Or did I get you wrong?

          Regards,
          Thomas
          • 2. Re: pl/sql event logging
            6363
            What's a PL/SQL schema?
            • 3. Re: pl/sql event logging
              531621
              A very cheap example of what you can do with exceptions(more time = better code + less jokes):


              declare
              i integer;
              evil_exception EXCEPTION;
              begin

              IF Somestate IS NULL
              DBMS_OUTPUT.PUT_LINE('Some funky text before Exception');
              raise evil_exception;
              END IF;

              EXCEPTION
              WHEN evil_exception THEN
              INSERT INTO ERROR_LOG (LOG_DATE, LOG_ACTION, LOG_USER)
              VALUES (sysdate,
              'Some evil exception just dropped in!',
              SYS_CONTEXT('USERENV', 'OS_USER')
              );
              COMMIT;
              end;
              • 4. Re: pl/sql event logging
                mlindsay
                There is a logging framework for pl/sql called log4plsql. it is similar in concept to log4J

                http://log4plsql.sourceforge.net/

                It allows for various levels of logging i.e. errors, debug, warnings etc...

                It also allows you to configure the logging level meaning you can turn debug on and off.

                The documentation will explain things in more detail

                If you choose this approach it would be best to write your own wrapper for the framework to cover you if you decide to change your approach or in log4plsql changes in any way.

                Hope that helps.
                • 5. Re: pl/sql event logging
                  Billy~Verreynne
                  The easiest and simplest method is to create your own LogMessage procedure. Give it two varchar2 parameters - a message type and a message string. Make it an autonomous transaction.

                  Now you can, from this custom proc, do whatever you want. Call DBMS_OUTPUT. Write the data to a DBMS_PIPE for interactive display and debugging. Insert the data into a message log table, adding a date time stamp, PL/SQL stack trace, session stats, process stats, etc to it.

                  APIs like Log4Java/Log4PLSQL is "nice" - but a tad heavy and clunky IMO. And does not always do exactly what I want it to do.

                  The very basic software engineering principle of modularisation applies. Create a single "module" that does your logging. Once you have that, that feature is abstracted and modularised and can be customised in all kinds of ways without having to touch a single byte of existing code.

                  In the PL/SQL context, it would be even a better idea to put this proc into a package as you can then polymorph it.

                  Keep it simple. Simplicity is the ultimate form of elegance and sophistication.
                  • 6. Re: pl/sql event logging
                    mlindsay
                    >APIs like Log4Java/Log4PLSQL is "nice" - but a tad heavy and clunky IMO. And does >not>always do exactly what I want it to do.

                    Hence why i would recommend creating a wrapper that way you can choose what features to use/not use. A simple LogMessage package/procedure is the best way to go however, as you suggested Billy, a package would be better as it would allow for future expansion.

                    There are two approaches to doing this. Either write a single procedure in your package that accepts a message type or create explicit procedures i.e. log_pkg.error(msg), log_pkg.debug() which encapsulate the message type, I prefer the latter as it should help prevent the following copy/paste issue

                    logMessage(p_type => con_debug_type, p_message =>'MY ERROR MESSAGE')

                    That error message would go in as a debug mesage.

                    I find the following much clearer log_pkg.log_error(p_message =>'MY ERROR MESSAGE')

                    Also, while I do not want to sound like some sort of log4plsq salesman I would say one of it's advantages (although this can be easily be replicated if you write your own packages) is that it allows the logging level to be toggled meaning you don't have to remove all of your debug statements if you see them being useful for instrumentation in a live environment.

                    it has functions such as "isDebugEnabled" which can be used in conditional logic meaning you could write the following

                    if isdebugEnabled then
                    log.debug('my_pkg.add() --- start');
                    end if;


                    if you have enabled the debug level debug messages will be written otherwise they will not.

                    This logic of course should be encapsulated in your wrapper.

                    As I hinted at earlier this sort of behaviour can be achieved in your own package if you decide to use your own.

                    I will highlight one part you mentioned Billy as I believe it is the most important part of any logging framework Make it an autonomous transaction. I have seen error logging before that got wiped out with every rollback. Very confusing for developers, who then start coming onto forums asking about oracle bugs and the like.
                    • 7. Re: pl/sql event logging
                      Avi Abrami
                      Hi,
                      I'd like to hijack this thread, since it talks about error messages.
                      Is it possible to have parameterized error messages?
                      For example, a given key value in a lookup table doesn't exist.
                      Then I'd like the error message to say something like: "Key not found: ???"
                      and have the actual key value searched for be displayed in the message instead of the question marks ("???").

                      In java, for example, there is the MessageFormat class where you supply an array of values and use place holders in the message string. When you display the message, the place holder characters are replaced by the values in the array.

                      Thanks,
                      Avi.
                      • 8. Re: pl/sql event logging
                        mlindsay
                        IMO the logging should only be concerned with recording a message. The responsibility for creating the correct error message will fall on your exception handling.

                        Off the top of my head you could create a error package with a raise_exception() procedure to handle your business exceptions (your example seems to be more of a business based exception than one thrown by Oracle)

                        This procedure would in turn call dbms_standard.raise_application_error().

                        you could then do the following

                        if key_not_found then
                        raise_exception(p_error_code => p_error.key_not_found_code, p_message => 'Key Not Found: ' || v_key);
                        end if;


                        Then when logging you only have to concern yourself with logging the error code and the message.

                        I'm sure there are holes in that idea (i.e. handling native oracle exceptions if required) but it would be where I would start. As Billy said previously keep the logging simple, leave the message creation to the module that is raising the exception.

                        You could of course extend the error package to contain similar functionality to that of MessageFormat.

                        Hope that is of some help.
                        • 9. Re: pl/sql event logging
                          Billy~Verreynne
                          We use parameterised error messages via a standard EML (Exception Management Library) - a custom PL/SQL package I developed to standardise our exception management.

                          This package provides two basic API calls:
                          - assert()
                          - raiseError()


                          A basic example:

                          SQL> -- each application creates their own message list up front (TStrings is table of varchar2)
                          SQL> exec EML.ERROR_MESSAGE_LIST := new TStrings( 'Parameter %s1 must be larger than %s2.', '2nd message', '3rd message', 'etc.' );

                          PL/SQL procedure successfully completed.

                          SQL> -- typical assertion in application code
                          SQL> declare
                          2 -- these constants are defined in an application's constant/static lib
                          3 -- (package with header only - kind of like a C header file)
                          4 E_PARAM_TOO_SMALL constant number := 1;
                          5 p_param1 number := 10;
                          6 begin
                          7 assert( p_param1 > 99, -- this must be true or
                          8 E_PARAM_TOO_SMALL, -- else this exception occurs
                          9 TStrings( 'p_param1', 99 ) -- with the following substitution values
                          10 );
                          11* end;
                          12 /
                          declare
                          *
                          ERROR at line 1:
                          ORA-20001: Parameter p_param1 must be larger than 99.
                          ..
                          • 10. Re: pl/sql event logging
                            94799
                            Adrian Billington provides a "REPLACEF" function for just such a purpose at the excellent (IMO) http://www.oracle-developer.net, see...

                            http://www.oracle-developer.net/content/utilities/replacef.sql
                            • 11. Re: pl/sql event logging
                              Billy~Verreynne
                              > I'm sure there are holes in that idea (i.e. handling native oracle exceptions
                              if required) but it would be where I would start.

                              Native exception should be caught where and when needed and then can be re-raised using a custom user exception. E.g.

                              exception when NO_DATA_FOUND then
                              RaiseError(
                              E_INVALID_EMPID, -- custom exception number
                              TStrings(p_empid) -- substitution value for the custom message
                              ); -- result: ORA-20020: Employer id 1235 is not valid
                              > You could of course extend the error package to contain similar functionality
                              to that of MessageFormat.

                              Exactly. In our case, each exception also makes a call to a custom PL/SQL Message API. This in turns logs the process details of the process raising the exception, records the PL/SQL call stack and so on.

                              The great thing is that as the developers work with a user-defined API call to:
                              - log a message
                              - make an assertion
                              - raise an exception

                              And we can make any changes on the back-end implementation side to deal with how that API call should be executed.

                              It is a poor show seeing code from a developer littered with DBMS_OUTPUT and RAISE_APPLICATION_ERROR calls. These do not provide one with any means of adding value (ito debugging, logging, tracing, etc) to the code.
                              • 12. Re: pl/sql event logging
                                531621
                                mlindsay, Thanks for link to LOG4PLSQL - looks very interesting, I'll try to get in touch with that one :-)
                                • 13. Re: pl/sql event logging
                                  mlindsay
                                  Native exception should be caught where and when
                                  needed and then can be re-raised using a custom user
                                  exception. E.g.

                                  exception when NO_DATA_FOUND then
                                  RaiseError(
                                  E_INVALID_EMPID, -- custom exception number
                                  TStrings(p_empid) -- substitution value for the
                                  custom message
                                  ); -- result: ORA-20020:
                                  Employer id 1235 is not valid
                                  We also re-raise exceptions similar to the above example but do not use params as substitution values, nice idea.

                                  I'm going to be really picky here but as it's one of my pet subjects please indulge me. As your RaiseError() procedre is not only raising but performing other tasks (correct me if I'm wrong) I would have called is ProcessError(). Of course this is my personal preference because I am pic ky to the point of annoyance.

                                  I think it should be stressed that an in-house API should be the first port of call when wishing to do something global like logging an exception handling for the reasons Billy has mentioned.

                                  Good luck Snapshot but remember to stick a wrapper on top of it ;-)
                                  • 14. Re: pl/sql event logging
                                    Billy~Verreynne
                                    > I'm going to be really picky here but as it's one of my pet subjects please
                                    indulge me. As your RaiseError() procedre is not only raising but performing
                                    other tasks (correct me if I'm wrong) I would have called is ProcessError().

                                    Correct. It formats the message, makes a Message API call and then calls PL/SQL's raise application error.

                                    And a good point that you have raised. ProcessError() is a much more accurate description. Quite right to nit pick on this.

                                    The original RaiseError() however did simply raise an application error.. Comes from refactoring code. I'm always refactoring code when it needs to be touched. :-)

                                    Hmm.. how would you handle something like this? An API call that outgrows its original base functionality due to it being refactored and improved - where a name change to the API call would be in order?
                                    1 2 Previous Next