Devart Forums

Posted: **Mon 23 Jan 2012 07:38**

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

Posted: **Thu 26 Jan 2012 14:02**

Yes, you can use dotConnect for Oracle with log4net.

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));

2. Here is a simple example of configuring console application:
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>

c) C# code:

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");
        }
    }

Posted: **Fri 27 Jan 2012 07:21**

Thanks a ton, this is what I was looking for.
I had missed the "Devart.Data.Oracle.OracleConnection" from the connection Type.

Posted: **Mon 19 Mar 2012 20:00**

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???

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>

Thanks,
Rob

Posted: **Mon 26 Mar 2012 13:45**

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')" />

You have seven columns in your command text and eight values. Delete one 'testing' and run your application again.

Posted: **Wed 03 Dec 2014 19:02**

Can we call stored procedures as well? I'm trying and it's not working for me:

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>

Here's what I'm calling in Main:

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();
        }
    }
}

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.**

Posted: **Wed 03 Dec 2014 20:24**

Here's how we can call this using a stored procedure (multiple parameters provided):

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>

Here's what I'm calling in Main:

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.

Posted: **Fri 05 Dec 2014 14:24**

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.

Devart Forums

log4net using dotConnect for Oracle

log4net using dotConnect for Oracle

Trying the same however getting exceptions...

Re: log4net using dotConnect for Oracle

Re: log4net using dotConnect for Oracle

Re: log4net using dotConnect for Oracle