This content has been marked as final. Show 17 replies
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?
What's a PL/SQL schema?
A very cheap example of what you can do with exceptions(more time = better code + less jokes):
IF Somestate IS NULL
DBMS_OUTPUT.PUT_LINE('Some funky text before Exception');
WHEN evil_exception THEN
INSERT INTO ERROR_LOG (LOG_DATE, LOG_ACTION, LOG_USER)
'Some evil exception just dropped in!',
There is a logging framework for pl/sql called log4plsql. it is similar in concept to log4J
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.
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.
>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');
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.
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.
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);
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.
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:
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
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;
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
ERROR at line 1:
ORA-20001: Parameter p_param1 must be larger than 99.
Adrian Billington provides a "REPLACEF" function for just such a purpose at the excellent (IMO) http://www.oracle-developer.net, see...
> 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.
> You could of course extend the error package to contain similar functionality
exception when NO_DATA_FOUND then
E_INVALID_EMPID, -- custom exception number
TStrings(p_empid) -- substitution value for the custom message
); -- result: ORA-20020: Employer id 1235 is not valid
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.
mlindsay, Thanks for link to LOG4PLSQL - looks very interesting, I'll try to get in touch with that one :-)
Native exception should be caught where and whenWe also re-raise exceptions similar to the above example but do not use params as substitution values, nice idea.
needed and then can be re-raised using a custom user
exception when NO_DATA_FOUND then
E_INVALID_EMPID, -- custom exception number
TStrings(p_empid) -- substitution value for the
); -- result: ORA-20020:
Employer id 1235 is not valid
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 ;-)
> 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?