Enterprise Library Logging to Oracle Database

When I first needed to log to an Oracle database from the Enterprise Library, I expected it to be a simple matter of running a script against my Oracle database and adding a few lines to my app.config. Thanks to bad decisions on the part of the Enterprise Library designers and problems with ADO.NET, I had to create my own script to create the necessary tables and stored procedures *and* create two classes that derived from two existing classes in Enterprise Library. This post applies to the Jan ’06 version of the Enterprise library. I have not had a chance to check the Apr ’07 edition to see if they now support Oracle directly.

When I first embarked on this incredibly long journey, I looked at the script included in the EntLib project that creates the tables. Unfortunately, it’s T-SQL specific. So, I converted the script to work with Oracle. That wasn’t so bad. I did have to put “timestamp” in double quotes in my Oracle version of WriteLog since its actually an Oracle datatype. But I was able to invoke my procedure directly using PL/SQL code and I foolishly assumed that EntLib would similarly be able to do so from it’s existing FormattedDatabaseTraceListener.

I fired up my test app and clicked the “Log” button that invoked the EntLib logger. Then I looked at the log table but there were no new entries. I added a flatfile listener and ran the app again. This time I was able to see that the EntLib logging system was trying to write logs out to my oracle database, but was getting the following error (which it kindly logged to the trace file):

PLS-00306: wrong number or types of arguments in call to ‘WRITELOG’

So, I double, triple, quadruple check the order, type, and name of the parameters I am passing and everything looks perfect. Finally, I break down and copy the code that invokes the stored procedure from the EntLib class and add an “Exec Stored Proc” button that will allow me to call the stored procedure directly. I get the same error. Now, I am puzzled. I can invoke this procedure from Pl/SQL but I can call it from ADO.Net. Out of desperation, I change the name of the “timestamp” parameter to TimeOf, recompile the stored proc, and things start working. Apparently, ADO.NET/Oracle cannot handle the parameter with the same name as a reserved word.

Now that I know what the problem is, it’s not very easy to fix. I have to create my own dll that extends the Enterprise Library just to change the name of one parameter in a stored procedure call.

Now that you know the background of why its not as easy as just modifying app.config and running a sql script, you can fully appreciate the attachmdents to this post. I will provide an overview of the importants steps of getting this working in your own project.

First, you will need to add references to the following EntLib dlls in the project in which you want to use the BlueCorner.OracleLogSink dll:

Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Logging
Microsoft.Practices.EnterpriseLibrary.Logging.Database

Second, you will need to modify your app.config file. You can view the attached project for all the details, but the only-non-intuitve/non-standard part is the type reference:

<add databaseInstanceName="LoggingDb" writeLogStoredProcName="pkg_logger.WriteLog"
addCategoryStoredProcName="pkg_logger.AddCategory" formatter="Text Formatter"
listenerDataType
="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListenerData, BlueCorner.OracleLogSink"
traceOutputOptions="None" 
type="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListener, BlueCorner.OracleLogSink"
name="Database Trace Listener" 
/>

Note the “listenerDataType” and “type” attributes. When I first attempted this, I thought I could reuse the FormattedDatabaseTraceListenerData for the listenerDataType because I was not altering the structure of the configuration section, just the implementation of FormattedDatabaseTraceListener. Unfortunately, this did not work. I had to make a copy of FormattedDatabaseTraceListenerData and the only difference was the name of the class and the referenced classes.

Third, just add a reference to BlueCorner.OracleLogSink either as a project or as a built dll.

Fourth, run the attached database script against your database and alter the connectionString section of your app.config appropriately.

I’m not covering any standard connectivity steps like editing tnsnames.ora, etc. If you need help getting ADO.net to talk to your oracle schema you will need to look elsewhere.

Here are the working demo project and the sql script you will need. Enjoy.

Advertisements