SubscribeWhen 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.
John Opincar's Blue Corner 
October 24, 2007 at 11:41 am
Excellent….thank you very much for this article….. it helped me a lot.
Raj
January 28, 2008 at 12:00 pm
I had to do this for Sybase, sybase cannot accept large strings (Text datatype) as parameters so you have to directly execute the SQL!
Oh and I had to create my own SybaseDatabase object because EntLib couldnt use the sybase .net data provider with EntLib.. (due to sybase lack of ado.net 2.0 support) what a nightmare!
Ofcourse now (EntLib 3.0) you can use the Application Block Software Factory to build this stuff.. this really simplifies creating custom logging etc.
April 25, 2008 at 1:30 pm
Thank you! This help a lot today as I was tasked with getting logging to an Oracle database working.
One side effect I did notice is that adding the listener to the config, resulted in theEnterprise Library Confuartion Tool to omit my logging node.
Still, small price to pay. Thank a lot for the post.
August 21, 2008 at 9:21 am
John
This is very helpful, but when I retrieved this file
EnterpriseLibraryLoggingToOracle.zip
it says its corrupt.
The Table and package file worked perfect.
Is the project sample a zip you can email to stevekeefe@comcast.net
Thanx again
August 21, 2008 at 2:33 pm
Steve, try again. I just d/l’d it an it opened fine.
August 29, 2008 at 4:45 pm
Jon the download worked!
I did get this going in .NET 2.0
Your sample was very helpful
Thanx
September 5, 2008 at 8:52 am
This is a comprehensive straight forward solution. I used this with the .NET 3.0 framework and the Enterprise Library 3.1. All that was required was some small tweaks to the listener section of app.config file (version and publickey attributes). Thank you!
September 17, 2008 at 2:25 pm
@Matthew O’Gorman
How did you get the proper publickeytoken?
September 26, 2008 at 11:43 am
Hello,
Thanks for this great article, I followed the steps as mentioned above but i am not able to generate the log entries in the Oracle DB. I have feeling its got something to do with connection string which i pass.
Please email me at achettiar@gmail.com and I can explain you in detail about what i am doing or what i am not doing.
-Andy
October 6, 2008 at 10:38 am
Response to Robert on the publickeytoken question.
1.) Deleted all 7 references to the 2.0.0.0 libraries.
2.) Added the 7 3.1.0.0 references to the BlueCorner.OracleLogSink project
3) Changed the project to Framework 3.0 and recompiled
4) updated the attributes on app.config file of Version=2.0.0.0 to Version=3.1.0.0
5) Grabbed the publickeytoken from auto generated configuration builder from a different .config file PublicKeyToken=null to PublicKeyToken=b03f5f7f11d50a3a
November 10, 2008 at 8:44 am
Thanks for your article. Now I’m using Entlib 4, .Net Framework 3.5 and your oracle scripts with only a few modificacions to match Entlib4. With these version, i don’t have the ADO.Net problems to handle the parameters name. It works great.
Thanks again and sorry for my bad english.
November 13, 2008 at 9:39 am
Thank you very much for the article. Really helpful.
I am facing problem running the application. When I try to run the application and click the ‘ExecuteStoredProc’ button, I am getting error “ORA-12705: Cannot access NLS data files or invalid environment specified”. I tried running on different machine but still same error. Any idea on how to solve this?
Thanks
November 13, 2008 at 11:45 am
Pac,
Could you please mention the steps you used to get it worked on .net 3.5 usng entlib 4. Could you please share the sample if possible.
February 18, 2009 at 7:01 am
Thanks for the articel; I am facing an issue here. btnExecSp_Click is not responding after db.ExecuteNonQuery(cmd) stmt, not getting errors or results. When I pass the parameters wrongly it throws an error but when everything correct application kind of hangs with no response… please guide me on this.
February 26, 2009 at 8:43 am
The above issue is solved. The issue was with the Oracle Client we were using. We had Oracle 8i as client and Oracle 10g as server which caused the issue. When we installed Oracle 10g client it worked fine.
August 28, 2013 at 7:16 am
Thanks for the article. I’m getting “PL/SQL: numeric or value error: character to number conversion error”, so I have tried your solution. But FormattedOracleDatabaseTraceListenerData and FormattedOracleDatabaseTraceListener not working with enterprise library 5.0. Any idea about how to use it with enterprise library 5.0??
August 29, 2013 at 12:11 am
I have solved it. Just modify the script. In writelog procedure just define LogID OUT NUMBER at the end, after FormattedMessage NVARCHAR2. You have declared it at start that’s why you are getting the error. After this, no need of those listener files.
April 11, 2014 at 4:16 am
Hi, I am not able to download the sample working demo project and the sql script.
August 24, 2015 at 8:19 am
Hi, This is really a nice article. But while working with enterprise library 5.0, some of the classes are not supporting. Such as Assembler,InstrumentationProvider.FireTraceListenerEntryWrittenEvent(),LogFormatterCustomFactory.Instance.Create() etc. Can anybody please provide me any idea or working code how to resolve them with enterprise library 5.0. Thanks in advance.