Why is the following query unsupported for change notification?

0

I'm trying to write some .NET code to listen for changes in an Oracle DB from a subset of users, by registering an OracleDependency on the Oracle UNIFIED_AUDIT_TABLE. I've defined an audit policy TEST_AUDIT_POLICY to audit changes from the users I'm interested in. The following code is my .NET listener:

    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)
        {
        }
    }

When I run this code, I get the following exception:

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)

Am I unable to listen for changes on UNIFIED_AUDIT_TABLE because UNIFIED_AUDIT_TABLE is not a table, but just a view? If so, how can I register to be notified of changes against the TEST_AUDIT_POLICY policy?

oracle
.net-core
odp.net
asked on Stack Overflow Jul 6, 2020 by Ben R.

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0