.NET 6 Web应用NLog写入Oracle数据库报错：表或视图不存在

在.NET 6应用中配置NLog后，文件和控制台日志功能正常，但写入Oracle数据库时抛出ORA-00942（表或视图不存在）错误，数据库中该表实际已存在。已验证连接字符串与appsettings.json一致，且表已创建。

nlog.config配置 #

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      throwConfigExceptions="true"
      internalLogLevel="Trace"
      internalLogFile="c:\Temp\nlog-internal.txt">

  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>

  <targets>
    <!-- Database Target: Logs to Oracle Database -->
    <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(LogLevel, Message, CreatedOn, Exception, URL, Logger, Action) values(:level, :message, :createddate, :exception, :url, :logger, :action)"
            commandType ="Text">
            <parameter name="level" layout="${level}" />
            <parameter name="message" layout="${message}" />
            <parameter name="createddate" 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>

    <!-- Console Target: Logs to Console -->
    <target name="console" xsi:type="ColoredConsole" layout="${longdate} ${logger} ${level:uppercase=true} - ${message}" />
  <target xsi:type="File" name="ownFile-web" fileName="c:\temp\nlog-AspNetCore6-own.log"
        layout="${longdate}|${event-properties:item=EventId:whenEmpty=0}|${level:uppercase=true}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}|action: ${aspnet-mvc-action}|${callsite}" />
  </targets>

  <rules>
    <!-- Log all messages to database and console -->
    <logger name="Microsoft.*" maxlevel="off" final="true" />
    <logger name="System.Net.Http.*" maxlevel="off" final="true" />
     <logger name="*" minlevel="Trace" writeTo="ownFile-web" />
    <logger name="*" minlevel="Trace" writeTo="database,console" />
  </rules>
</nlog>

NLog内部日志错误信息 #

2023-11-08 07:45:15.3687 Trace DatabaseTarget(Name=database): Open connection.
2023-11-08 07:45:15.3756 Trace DatabaseTarget(Name=database): Executing Text: insert into LogTable(LogLevel, Message, CreatedOn, Exception, URL, Logger, Action) values(:level, :message, :createddate, :exception, :url, :logger, :action)
2023-11-08 07:45:15.3756 Trace   DatabaseTarget: Parameter: 'level' = 'Info' (String)
2023-11-08 07:45:15.3756 Trace   DatabaseTarget: Parameter: 'message' = 'Successfully fetched data' (String)
2023-11-08 07:45:15.3756 Trace   DatabaseTarget: Parameter: 'createddate' = '11/08/2023 07:45:15' (DateTime)
2023-11-08 07:45:15.3910 Trace   DatabaseTarget: Parameter: 'exception' = '' (String)
2023-11-08 07:45:15.3910 Trace   DatabaseTarget: Parameter: 'url' = 'http://localhost/api/message/get' (String)
2023-11-08 07:45:15.3910 Trace   DatabaseTarget: Parameter: 'logger' = 'Controller' (String)
2023-11-08 07:45:15.3910 Trace   DatabaseTarget: Parameter: 'action' = 'GetPage' (String)
2023-11-08 09:25:12.2648 Error DatabaseTarget(Name=database): Error when writing to database. Exception: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00942: table or view 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, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at NLog.Targets.DatabaseTarget.ExecuteDbCommandWithParameters(LogEventInfo logEvent, IDbConnection dbConnection, IDbTransaction dbTransaction)
   at NLog.Targets.DatabaseTarget.WriteLogEventSuppressTransactionScope(LogEventInfo logEvent, String connectionString)
2023-11-08 09:25:12.2648 Trace DatabaseTarget(Name=database): Close connection because of error
2023-11-08 09:25:12.2648 Trace DatabaseTarget(Name=database): Close connection (KeepConnection = false).

• 指定表的Schema/所有者：Oracle中如果表不属于当前连接用户的默认Schema，必须在表名前加上所有者前缀，比如将insert into LogTable(...)改为insert into SCHEMA_NAME.LogTable(...)（替换SCHEMA_NAME为实际表所属的用户名）。另外注意Oracle默认会将未加双引号的表名转为大写，若创建表时用了双引号区分大小写，需确保SQL语句中的表名和创建时一致（比如"LogTable"）。
• 检查用户权限：确认连接数据库的用户拥有LogTable的INSERT权限，执行SQL语句授权：GRANT INSERT ON LogTable TO YOUR_USERNAME;（替换YOUR_USERNAME为连接字符串中的User Id）。
• 验证连接的数据库实例：确认NLog配置中的Data Source指向的是正确的Oracle实例，避免连接到其他环境的数据库导致找不到表。
• 启用KeepConnection优化：在DatabaseTarget中添加keepConnection="true"，避免频繁创建/关闭连接可能带来的异常，但这是辅助优化，主要还是前三点。

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