Use Oracle as Log Target

[andreasbraidt]

Hi,
i've seen a past issue with logging to Oracle DB. I tried to configure it. I've already included the Oracle.ManagedDataAccess.Core package in my project and configured log4net in this way:

    <appender name="AdoNetAppender" type="MicroKnights.Logging.AdoNetAppender, MicroKnights.Log4NetAdoNetAppender">
        <connectionType value="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess, Version=3.1.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
        <connectionStringName value="OracleConnection" />
        <connectionStringFile value="appsettings.json" />

This works at the first sight, but i get an exception

   at MicroKnights.Logging.AdoNetAppender.SendBuffer(LoggingEvent[] events)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01006: bind variable does not exist
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at MicroKnights.Logging.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events)
   at MicroKnights.Logging.AdoNetAppender.SendBuffer(LoggingEvent[] events)

my parameters are configured like these:

        <parameter>
            <parameterName value=":message" />
            <dbType value="String" />
            <size value="4000" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%message" />
            </layout>
        </parameter>

I already tried "@" and ":" and checked case etc.

As i get already an ORA- Error the connection to the DB seems to be established.
When i configure a insert statement without any variables then i get the recorid inserted into the table.
As soon as i add one variable then the error occurs.

Have i missed something in my configuration?

[microknights]

Can you show the sql of the configuration?

[microknights]

@andreasbraidt But try do your parameter configuration:

        <parameter>
            <parameterName value="message" />
            <dbType value="String" />
            <size value="4000" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%message" />
            </layout>
        </parameter>

So neither use @ nor : in the <parameterName> declaration.

[andreasbraidt]

Here my config snippet

    <appender name="AdoNetAppender" type="MicroKnights.Logging.AdoNetAppender, MicroKnights.Log4NetAdoNetAppender">
        <connectionType value="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess, Version=3.1.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
        <connectionStringName value="OracleConnection" />
        <connectionStringFile value="appsettings.json" />
        <commandText value="
                        INSERT INTO INFIBULOG
                        (
                        date_created,
                        unit,
                        severity,
                        err_stack,
                        message, 
                        type, 
                        owner
                        ) 
                        VALUES 
                        (
                        sysdate, 
                        'Thread: ', 
                        0, 
                        'logger', 
                        :message, 
                        'datevconnect', 
                        'datevconnect'
                        )
                " />
        <bufferSize value="1" />
        <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>
        <parameter>
            <parameterName value="exception" />
            <dbType value="String" />
            <size value="2000" />
            <layout type="log4net.Layout.ExceptionLayout" />
        </parameter>

Exception:

log4net:ERROR [AdoNetAppender] ErrorCode: GenericFailure. Exception while writing to database Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01006: bind variable does not exist at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at MicroKnights.Logging.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events) at MicroKnights.Logging.AdoNetAppender.SendBuffer(LoggingEvent[] events) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01006: bind variable does not exist at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at MicroKnights.Logging.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events) at MicroKnights.Logging.AdoNetAppender.SendBuffer(LoggingEvent[] events)

[microknights]

Have you tried replacing :message: with 'message' to see if it passes the sql statement? Else try use the Log4NetHelper with InternalDebug, it usually reveals startup problems in regards of the parameter bindings.

[andreasbraidt]

I got it working now. The problem was that the parameter binding is by position and not by name. This is a default behaviour in oracle. Is it possible to set the property OracleCommand.BindByName to true?
With the current state you may not define more parameters in the config than in the SQL Insert statement.

[microknights]

I will 👀 at this Oracle behavior, and what has been "default" previously.