Oracle DB target from .NET - ORA-01036: illegal variable name/number

[Ted Cooper]

Hi guys,

Trying to set up an Oracle DB target in nLog 2.1 using .NET 4.5

I have a stored procedure within a package that I'm calling, passing in a bunch of XML as the "message."

I have compiled and verified this stored procedure by calling it manually from SQL Developer, and it works.  But calling it from nLog I get the following error:

Error Error when writing to database System.Data.OracleClient.OracleException (0x80131938): ORA-01036: illegal variable name/number

   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)...

My nLog config (slightly sanitized)

<?xml version="1.0" encoding="utf-8" ?>

<nlog throwExceptions="true" internalLogFile="c:\\code\\nlog.txt" internalLogLevel="Error"

      xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <!-- 

  See http://nlog-project.org/wiki/Configuration_file 

  for information on customizing logging rules and outputs.

   -->

  <targets>

    <target type="Database" 

            name="database" 

            dbProvider="System.Data.OracleClient" 

            connectionstring="Data Source=dbtnsname;User Id=username;Password=password;">

      <commandText>

        EXEC logging_package.HandleLogMessages @MessageXML

      </commandText>

      <parameter name="MessageXML" layout="${message}"/>

    </target>

  </targets>

  <rules>

    <logger name="*" minLevel="Info" writeTo="database"/>

  </rules>

</nlog>

The procedure in Oracle is defined as:

Create or replace PACKAGE BODY LOGGING_PACKAGE IS

PROCEDURE HandleLogMessages

 (MessageXML IN CLOB)

    IS

BEGIN  ...

Any help appreciated, thank you!

[Ted Cooper]

Replying to my own question, for the benefits of future generations...

The syntax that I had to use to get this to work was... unlikely.  It certainly didn't agree with what I had read online for calling an Oracle Stored Procedure from nLog.

<target type="Database" 

            name="database" 

            dbProvider="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"  

            connectionstring="Data Source=db;User Id=username;Password=password;">

      <commandText>

        begin LOGGING_PACKAGE.HandleLogMessages(:MessageXML); end;

      </commandText>

      <parameter name="MessageXML" layout="${message}"/>

    </target>

[Kim Christensen]

Glad this was resolved, but I'll look into the problem with the other syntax.