Oracle CQN in devArt does not provide RowID information

[lingster]

Hi,

I am trying to use the Oracle CQN/OracleDependency feature within DevArt/Oracle.
I create the following query:

Code: Select all

select id, last_update_datetime from my_table t where t.id = 12345

and register it for update notifications, then I update a row in the table above to generate a notification from Oracle.

The first problem I have is when I try to set the

Code: Select all

Notification.OracleQueryBasedNotificationMode == OracleQueryBasedNotificationMode.Guaranteed;

I get the following exception:

Code: Select all

ERROR: Devart.Data.Oracle.OracleException (0x80004005): ORA-29977: Unsupported column type for query registration in guaranteed mode
   at Devart.Data.Oracle.be.d(Int32 A_0)
   at Devart.Data.Oracle.de.a(Int32 A_0, s A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteReader()
   at ...**myfunction**...

If I set the mode to

Code: Select all

OracleQueryBasedNotificationMode.BestEffort

then the callback is executed, but no rowid information is returned.
Here's the code I use to setup the callback:

Code: Select all

                    connection.Open();
                    OracleCommand cmd = new OracleCommand(sqlSelect, connection);
                    dep = new OracleDependency(cmd);
                    
                    cmd.Notification.IsNotifiedOnce = true; // only notify once for testing purposes, false will continuously listen for updates
                    cmd.Notification.Timeout = 0; // never timeout
                    cmd.Notification.IsPersistent = false; // persist the message in-memory queue, faster on the db than if set to true
                    cmd.Notification.QueryBasedNotificationMode = QMode;
                    cmd.Notification.RowLevelDetails = true;                        

                    dep.OnChange += new OnChangeEventHandler(callbackAction);
                    var dr = cmd.ExecuteReader();

I run the same setup using the Oracle Managed drivers and this works and executes my callback function and provides the rowids which have changed.

I'm using devart 9.2.x drivers with oracle 12.1 server and 12.1 OCI 64bit drivers.

Has anyone successfully been able to use OracleDependency notifications and let me know if I'm missing anything?

[Pinturiccio]

We have reproduced the issue with the OracleQueryBasedNotificationMode.Guaranteed mode. We will investigate it and post here about the results as soon as possible.

I run the same setup using the Oracle Managed drivers and this works and executes my callback function and provides the rowids which have changed.

Please create and send us a small complete test project, using ODP.NET Managed driver, and corresponding DDL/DML scripts, which reproduce the issue.