Feeds:
Posts
Comments

Posts Tagged ‘Oracle’

Introduction

This tutorial describes the required steps needed to be followed for logging with log4net on Oracle 10g.

Prerequisites

– Oracle 10g (could also be working with Oracle 11g, however I haven’t tried it – please leave a comment if you try it)
Oracle Data Access Components for Windows (ODAC) / (ODP.NET)
log4net (1.2.10)

Step 1

Creating the SQL table, where all logging messages will be inserted.

CREATE TABLE "LOG4NET"
	("LOG_ID" NUMBER NOT NULL ENABLE,
	 "LOG_DATE" DATE,
	 "LOG_LEVEL" VARCHAR2(10),
	 "LOG_IDENTITY" VARCHAR2(100),
	 "LOG_MESSAGE" VARCHAR2(4000),
	 "LOG_EXCEPTION" VARCHAR2(4000),
	 CONSTRAINT "LOG4NET_PK" PRIMARY KEY ( "LOG_ID" ) ENABLE
	 )
/
CREATE SEQUENCE "LOG4NET_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999
	INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE
/

LOG_ID: This is the PK of the table, it is getting an autonumber value from the sequence “LOG4NET_SEQ”.
LOG_DATE: Logging date for each message.
LOG_LEVEL: log4net’s level (DEBUG, ERROR etc.)
LOG_IDENTITY: Username (Web or Win32 application user)
LOG_MESSAGE: The logging message, or exception message.
LOG_EXCEPTION: Exception’s full stacktrace.

You can always modify this table to include more information according to your logging needs, however you will also have to modify accordingly the configuration file (Step 2).

Step 2

Setting the web.config (Web application) or app.config (Win32 application).
Here is a sample app.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
<log4net>
  <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <bufferSize value="1" />
      <connectionType value="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
      <commandText value="INSERT INTO LOG4NET (LOG_ID, LOG_DATE, LOG_LEVEL, LOG_IDENTITY, LOG_MESSAGE, LOG_EXCEPTION) VALUES (LOG4NET_SEQ.nextval, :log_date, :log_level, :log_identity, :log_message, :log_exception)" />
<parameter>
<parameterName value=":log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%d{dd/MM/yyyy HH:mm:ss}" />
        </layout>
      </parameter>
<parameter>
<parameterName value=":log_level" />
        <dbType value="String" />
        <size value="10" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%level" />
        </layout>
      </parameter>
<parameter>
<parameterName value=":log_identity" />
        <dbType value="String" />
        <size value="100" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%identity" />
        </layout>
      </parameter>
<parameter>
<parameterName value=":log_message" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%m" />
        </layout>
      </parameter>
<parameter>
<parameterName value=":log_exception" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%exception" />
        </layout>
      </parameter>
    </appender>
    <root>
      <level value="All"/>
      <appender-ref ref="AdoNetAppender"/>
    </root>
  </log4net>
</configuration>

The “ConnectionType” is using the Oracle’s ODP.NET dll (Oracle.DataAccess.Client.OracleConnection).
Therefore make sure that you have it referenced on your project.

Step 3

Configuring log4net on source code, as well as setting the connection string. Here is the sample code (in VB.NET) required for a Win32 app:

Private log As log4net.ILog = log4net.LogManager.GetLogger(Me.GetType())
	Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        log4net.Config.XmlConfigurator.Configure()
        Dim h As log4net.Repository.Hierarchy.Hierarchy = log4net.LogManager.GetRepository()
        Dim adoAppender As log4net.Appender.AdoNetAppender = CType(h.Root.GetAppender("AdoNetAppender"), log4net.Appender.AdoNetAppender)
        adoAppender.ConnectionString = "[your connection string]"
        adoAppender.ActivateOptions()
        log.Info( "hello world!" )
    End Sub

You can also set the connection string directly on the .config file (connectionString tag), however that would render the database username/password visible to anyone who has access to the .config file.

Well, there aren’t any more steps, if you have done everything right, check the database table for the “hello world!” message!

Conclusion

Logging is crucial for every application, and log4net is one of the most recommended logging frameworks. As demonstrated above, it is quite easy to set it up, and start logging from your .NET application. You can find more configuration examples here.

Advertisements

Read Full Post »

Whenever I have to deal with Oracle 10g projects at work, Oracle SQL Developer always comes in handy.

However yesterday, the backspace and delete button inside the SQL query editor, all of a sudden, stopped working!
I tried restarting the Oracle SQL Developer, even restarting the computer, but the problem persisted.

I didn’t want to uninstall/re-install the application, so I went on to search the Oracle official forums.
Seems like other people had the exact same problem, and the solution was quite simple!

To resolve the problem you have to go to:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Read Full Post »

Oracle 11g for Windows 32-bit and Linux 64-bit systems has finally been released.

Download

Read Full Post »