log4net using dotConnect for Oracle
-
- Posts: 5
- Joined: Fri 03 Dec 2010 10:09
log4net using dotConnect for Oracle
Hi,
I have a requirement to log events from my application using log4net. Is it possible to log directly using log4net and dotConnect for Oracle into the Oracle database.
Thanks,
Naveen
I have a requirement to log events from my application using log4net. Is it possible to log directly using log4net and dotConnect for Oracle into the Oracle database.
Thanks,
Naveen
Yes, you can use dotConnect for Oracle with log4net.
1. Create this table in your database:
2. Here is a simple example of configuring console application:
a) add a reference to log4net;
b) App.config:
c) C# code:
1. Create this table in your database:
Code: Select all
CREATE TABLE LOG (
DATETIME TIMESTAMP(3),
THREAD VARCHAR2(255),
LOG_LEVEL VARCHAR2(255),
LOGGER VARCHAR2(255),
MESSAGE VARCHAR2(4000));
a) add a reference to log4net;
b) App.config:
Code: Select all
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<log4net debug="true">
<logger name="Program">
<level value="DEBUG"/>
</logger>
<root>
<appender-ref ref="AdoNetAppender_Oracle"/>
<level value="debug"/>
</root>
<appender name="AdoNetAppender_Oracle" type="log4net.Appender.AdoNetAppender">
<connectionType value="Devart.Data.Oracle.OracleConnection, Devart.Data.Oracle, Version=6.60.283.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>
<connectionString value="Server=dboracle;Direct=true;SID=orcl1120;User ID=scott;Password=tiger;"/>
<commandText value="INSERT INTO Log (Datetime,Thread,Log_Level,Logger,Message) VALUES (:log_date, :thread, :log_level, :logger, :message)"/>
<bufferSize value="128"/>
<parameter>
<parameterName value="log_date"/>
<dbType value="DateTime"/>
<layout type="log4net.Layout.RawTimeStampLayout"/>
</parameter>
<parameter>
<parameterName value="thread"/>
<dbType value="String"/>
<size value="255"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%thread"/>
</layout>
</parameter>
<parameter>
<parameterName value="log_level"/>
<dbType value="String"/>
<size value="50"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%level"/>
</layout>
</parameter>
<parameter>
<parameterName value="logger"/>
<dbType value="String"/>
<size value="255"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%logger"/>
</layout>
</parameter>
<parameter>
<parameterName value="message"/>
<dbType value="String"/>
<size value="4000"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%message"/>
</layout>
</parameter>
</appender>
</log4net>
</configuration>
Code: Select all
using log4net;
using log4net.Config;
// using Devart.Data.Oracle;
namespace ConsoleApplication1 {
class Program {
public static readonly ILog log = LogManager.GetLogger(typeof(Program));
static void Main(string[] args) {
// new OracleMonitor() { IsActive = true };
log4net.Config.XmlConfigurator.Configure();
log.Debug("test3");
}
}
-
- Posts: 5
- Joined: Fri 03 Dec 2010 10:09
Trying the same however getting exceptions...
If I included the : in the parameter name I get an argument exception.
If I take the : out of the parameter I get a generic oracle exception. More similar to the sample above.
If I don't use parameters at all it works just fine.
I cut the parameters back to only 1 in the following clips. What am I missing???
Thanks,
Rob
If I take the : out of the parameter I get a generic oracle exception. More similar to the sample above.
If I don't use parameters at all it works just fine.
I cut the parameters back to only 1 in the following clips. What am I missing???
Code: Select all
<commandText value="INSERT INTO EXCEPTION_LOG (exception_id, exception_date, message, machine, app_user, log_name, exception_type) VALUES (misos.exception_log_seq.NEXTVAL, sysdate, :message, 'testing', 'testing', 'testing', 'testing', 'testing')" />
<parameter>
<parameterName value="message" />
<dbType value="String" />
<size value="1000" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%message" />
</layout>
</parameter>
Rob
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
You have seven columns in your command text and eight values. Delete one 'testing' and run your application again.rlecuyer wrote: <commandText value="INSERT INTO EXCEPTION_LOG (exception_id, exception_date, message, machine, app_user, log_name, exception_type) VALUES (misos.exception_log_seq.NEXTVAL, sysdate, :message, 'testing', 'testing', 'testing', 'testing', 'testing')" />
-
- Posts: 2
- Joined: Wed 03 Dec 2014 15:31
Re: log4net using dotConnect for Oracle
Can we call stored procedures as well? I'm trying and it's not working for me:
Here's what I'm calling in Main:
I'm getting an error:
log4net: Created Appender [AdoNetAppender_Oracle]
log4net: Adding appender named [AdoNetAppender_Oracle] to logger [root].
log4net: Hierarchy Threshold []
log4net:ERROR [AdoNetAppender] ErrorCode: GenericFailure. Exception while writing to database
Devart.Data.Oracle.OracleException (0x80004005): OCI-22062: invalid input string [(null)]
at Devart.Data.Oracle.a3.c(Int32 A_0)
at Devart.Data.Oracle.OracleNumber.Parse(String value, String format)
at Devart.Data.Oracle.OracleNumber.Parse(String value)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_
5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType
A_13, q A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(bt& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, q A_5,
Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(am A_0, Int32 A_1, OracleParameterCollection A_2, q A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startReco
rd, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at log4net.Appender.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events)
at log4net.Appender.AdoNetAppender.SendBuffer(LoggingEvent[] events)
log4net: Shutdown called on Hierarchy [log4net-default-repository]
**EDIT: I've fixed this and will provide the answer in a reply below.**
Code: Select all
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<log4net debug="true">
<logger name="Program">
<level value="DEBUG"/>
</logger>
<root>
<level value="ALL"/>
<appender-ref ref="AdoNetAppender_Oracle"/>
</root>
<appender name="AdoNetAppender_Oracle" type="log4net.Appender.AdoNetAppender">
<connectionType value="Devart.Data.Oracle.OracleConnection, Devart.Data.Oracle, Version=8.4.225.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>
<connectionString value="data source=mydb;User ID=myuser;Password=mypass;persist security info=false;"/>
<commandText value="MYUSER.PROC_TEST"/>
<commandType value="StoredProcedure"/>
<reconnectOnError value="true"/>
<bufferSize value="1"/>
<parameter>
<parameterName value="out_param1"/>
<dbType value="DateTime"/>
<layout type="log4net.Layout.RawTimeStampLayout">
<conversionPattern value="%property{log4net:out_param1}"/>
</layout>
</parameter>
<parameter>
<parameterName value="in_param1"/>
<dbType value="String"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{log4net:in_param1}"/>
</layout>
</parameter>
<parameter>
<parameterName value="in_param2"/>
<dbType value="Decimal"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{log4net:in_param2}"/>
</layout>
</parameter>
</appender>
</log4net>
</configuration>
Code: Select all
using System;
using log4net;
using log4net.Config;
namespace ConsoleApplication1
{
class Program
{
public static readonly ILog log = LogManager.GetLogger(typeof(Program));
static void Main(string[] args)
{
XmlConfigurator.Configure();
String outValue;
//set your values
GlobalContext.Properties["out_param1"] = ""; //initialized to something to prevent blowup
GlobalContext.Properties["in_param1"] = "some message";
GlobalContext.Properties["in_param2"] = 2;
log.Info(""); //call the proc
outValue = GlobalContext.Properties["out_param1"] == DBNull.Value ? null : GlobalContext.Properties["out_param1"].ToString();
}
}
}
log4net: Created Appender [AdoNetAppender_Oracle]
log4net: Adding appender named [AdoNetAppender_Oracle] to logger [root].
log4net: Hierarchy Threshold []
log4net:ERROR [AdoNetAppender] ErrorCode: GenericFailure. Exception while writing to database
Devart.Data.Oracle.OracleException (0x80004005): OCI-22062: invalid input string [(null)]
at Devart.Data.Oracle.a3.c(Int32 A_0)
at Devart.Data.Oracle.OracleNumber.Parse(String value, String format)
at Devart.Data.Oracle.OracleNumber.Parse(String value)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_
5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType
A_13, q A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(bt& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, q A_5,
Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(am A_0, Int32 A_1, OracleParameterCollection A_2, q A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startReco
rd, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at log4net.Appender.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events)
at log4net.Appender.AdoNetAppender.SendBuffer(LoggingEvent[] events)
log4net: Shutdown called on Hierarchy [log4net-default-repository]
**EDIT: I've fixed this and will provide the answer in a reply below.**
-
- Posts: 2
- Joined: Wed 03 Dec 2014 15:31
Re: log4net using dotConnect for Oracle
Here's how we can call this using a stored procedure (multiple parameters provided):
Here's what I'm calling in Main:
I hope this helps someone. I still couldn't figure out how to retrieve the out parameter. If someone comes up with an answer to that one, I'd appreciate it.
Code: Select all
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<log4net debug="true">
<logger name="Program">
<level value="DEBUG"/>
</logger>
<root>
<level value="ALL"/>
<appender-ref ref="AdoNetAppender_Oracle"/>
</root>
<appender name="AdoNetAppender_Oracle" type="log4net.Appender.AdoNetAppender">
<connectionType value="Devart.Data.Oracle.OracleConnection, Devart.Data.Oracle, Version=8.4.225.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>
<connectionString value="data source=mydb;User ID=myuser;Password=mypass;persist security info=false;"/>
<commandText value="MYUSER.PROC_TEST"/>
<commandType value="StoredProcedure"/>
<reconnectOnError value="true"/>
<bufferSize value="1"/>
<parameter>
<parameterName value="out_param1"/>
<dbType value="DateTime"/>
<layout type="log4net.Layout.RawTimeStampLayout">
<conversionPattern value="%property{out_param1}"/>
</layout>
</parameter>
<parameter>
<parameterName value="in_param1"/>
<dbType value="String"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{in_param1}"/>
</layout>
</parameter>
<parameter>
<parameterName value="in_param2"/>
<dbType value="Decimal"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{in_param2}"/>
</layout>
</parameter>
</appender>
</log4net>
</configuration>
Code: Select all
using System;
using log4net;
using log4net.Config;
namespace ConsoleApplication1
{
class Program
{
public static readonly ILog log = LogManager.GetLogger(typeof(Program));
static void Main(string[] args)
{
XmlConfigurator.Configure();
//set your values
ThreadContext.Properties["in_param1"] = "some message";
ThreadContext.Properties["in_param2"] = 2;
log.Info(""); //call the proc. It doesn't matter what the message is.
}
}
}
I hope this helps someone. I still couldn't figure out how to retrieve the out parameter. If someone comes up with an answer to that one, I'd appreciate it.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: log4net using dotConnect for Oracle
We are glad that you have found a workaround, but questions on log4net are beyond the scope of our support. Please contact the log4net support team.