.NET 6 Web应用NLog写入Oracle数据库遇ORA-00928错误求助

问题场景 #

.NET 6应用中NLog可正常写入文件和控制台，但写入Oracle数据库时触发ORA-00928（缺少SELECT关键字）错误，NLog内部核心报错信息如下：

2023-11-08 07:45:15.4523 Error DatabaseTarget(Name=database): Error when writing to database. Exception: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00928: missing SELECT keyword
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   ...

当前NLog配置中数据库目标的SQL语句：

commandText = "insert into LogTable(Level, Message, CreatedOn, Exception, URL, Logger, Action) values(:level, :message, :date, :exception, :url, :logger, :action)"

错误原因 #

Oracle数据库中，Level、Action、Message等属于保留关键字，直接作为列名使用时会导致SQL语法解析错误，触发ORA-00928异常。

解决方案 #

修改SQL插入语句，将所有与Oracle保留关键字冲突的列名用双引号包裹，让Oracle将其识别为标识符而非关键字。同时注意在XML配置中，双引号需要转义为"避免解析报错。

修改后的数据库目标配置片段 #

<target name="database" xsi:type="Database"
        dbProvider="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess"
        connectionString ="Data Source=DataSource;User Id=username;Password=pwd;"
        commandText ="insert into LogTable(&quot;Level&quot;, &quot;Message&quot;, CreatedOn, &quot;Exception&quot;, URL, &quot;Logger&quot;, &quot;Action&quot;) values(:level, :message, :date, :exception, :url, :logger, :action)"
        commandType ="Text">
        <parameter name="level" layout="${level}" />
        <parameter name="message" layout="${message}" />
        <parameter name="date" layout="${longdate}" dbType="DateTime"/>
        <parameter name="exception" layout="${exception:format=ToString}" />
        <parameter name="url" layout="${aspnet-request-url}" />
        <parameter name="logger" layout="${logger}" />
        <parameter name="action" layout="${aspnet-mvc-action}" />
</target>

关键验证点 #

• 确认所有冲突列名都已用转义后的双引号包裹
• 检查参数名与列名的映射关系无拼写错误

内容的提问来源于stack exchange，提问作者MWKhan1996