时间戳

首页 > 解决方案 > 为什么更改通知不支持以下查询？

oracle - 为什么更改通知不支持以下查询？

问题描述

我正在尝试编写一些 .NET 代码，通过OracleDependency在 Oracle 上注册一个用户子集来监听 Oracle DB 中的更改UNIFIED_AUDIT_TABLE。我已经定义了一个审核策略TEST_AUDIT_POLICY来审核我感兴趣的用户的更改。以下代码是我的 .NET 侦听器：

    private const string TableName = "UNIFIED_AUDIT_TRAIL";
    private const string SelectStatement = "select event_timestamp from " + TableName;

    ...

    public Form1()
    {
        var connection = new OracleConnection(Constr);
        connection.Open();
        var command = connection.CreateCommand();
        try
        {
            var dependency = new OracleDependency(command);
            dependency.OnChange += NotifyTypesChanged;

            command.Notification.IsNotifiedOnce = false;
            command.BindByName = true;
            command.CommandText = SelectStatement;

            new OracleDataAdapter(command)
                {MissingSchemaAction = MissingSchemaAction.AddWithKey}.Fill(DataSet, TableName);
            _dataGrid1.SetDataBinding(DataSet, TableName);
        }
        catch (Exception ex)
        {
        }
    }

当我运行此代码时，出现以下异常：

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-29973: Unsupported query or operation  during change notification registration
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

我是否无法收听更改，UNIFIED_AUDIT_TABLE因为UNIFIED_AUDIT_TABLE不是表格，而只是视图？如果是这样，我如何注册以收到有关TEST_AUDIT_POLICY政策更改的通知？

标签： oracle.net-coreodp.net

解决方案

不幸的是，您不能将 QCN 与视图一起使用。

请参阅：https ://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_cqn.htm#ADFNS576

推荐阅读