log4net using dotConnect for Oracle

log4net using dotConnect for Oracle

Postby naveen_ramesh » 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
naveen_ramesh
 
Posts: 5
Joined: Fri 03 Dec 2010 10:09

Postby Shalex » 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");
        }
    }
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Postby naveen_ramesh » 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.
naveen_ramesh
 
Posts: 5
Joined: Fri 03 Dec 2010 10:09

Trying the same however getting exceptions...

Postby rlecuyer » 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
rlecuyer
 
Posts: 1
Joined: Mon 19 Mar 2012 19:50

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44

Re: log4net using dotConnect for Oracle

Postby codeswithwolves » 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.**
codeswithwolves
 
Posts: 2
Joined: Wed 03 Dec 2014 15:31

Re: log4net using dotConnect for Oracle

Postby codeswithwolves » 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.
codeswithwolves
 
Posts: 2
Joined: Wed 03 Dec 2014 15:31

Re: log4net using dotConnect for Oracle

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle