Devart Forums

Posted: **Wed 08 Mar 2017 17:16**

Hi,

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

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?

Posted: **Mon 13 Mar 2017 14:52**

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

lingster wrote: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.

Devart Forums

Oracle CQN in devArt does not provide RowID information

Oracle CQN in devArt does not provide RowID information

Re: Oracle CQN in devArt does not provide RowID information