Oracle database change notification not working

[Feneck91]

I'm trying to get all database notification to be able to reflect database changed into my application without having to put refresh button everywhere !

I have read and try this tutorial

I have execute on database :

Code: Select all

GRANT CHANGE NOTIFICATION TO GDD;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO GDD;

And create code :

Code: Select all

        OracleCommand selectCommand = null;
        public void SurveyAllTables()
        {
            // Create an OracleDependency object and set it to track the resul set returned by selectCommand.
            OracleConnection oracleConnection = DatabaseManagerContext.Connection as OracleConnection;
            oracleConnection.Open();

            selectCommand = new OracleCommand("Select * from \"GDD\".\"DMUtilisateur\"", oracleConnection);

            OracleDependency dependency = new OracleDependency(selectCommand);
            // Setting object-based change notification registration 
            dependency.QueryBasedNotification = false;

            // When the IsNotifiedOnce property is true, only the first change  
            // of the traced result set will generate a notification. 
            // Otherwise, notifications will be sent on each change  
            // during the selectCommand.Notification.Timeout period. 
            selectCommand.Notification.IsNotifiedOnce = false; 

            // Specifies whether notifications will contain information on rows changed.
            //selectCommand.Notification.RowLevelDetails = true;

            selectCommand.ExecuteNonQuery();
            dependency.OnChange += dependency_OnChange;
        }

        private void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
        {
            DataTable dt = eventArgs.Details; 
 
            Console.WriteLine("The following database objects were changed:"); 
            foreach (string resource in eventArgs.ResourceNames) 
                Console.WriteLine(resource); 
 
            Console.WriteLine("\n Details:"); 
            Console.Write(new string('*', 80)); 
            for (int rows = 0; rows < dt.Rows.Count; rows++) 
            { 
                Console.WriteLine("Resource name: " + dt.Rows[rows].ItemArray[0]); 
                string type = Enum.GetName(typeof(OracleNotificationInfo), dt.Rows[rows].ItemArray[1]); 
                Console.WriteLine("Change type: " + type); 
                Console.Write(new string('*', 80)); 
            } 
        }

But no idea, when I add or remove item into GDD.DMUtilisateur table, I never receive notification. I'm working with oracle database installed in my own computer (local) 11g version and dotConnect 8.1 pro versions.

Any idea ?

[Pinturiccio]

I have read and try this tutorial

This is the tutorial for ODP.NET. For using OracleDependency feature read the corresponding Devart tutorial

In order for OracleDependency to work, add the following code to the end of the SurveyAllTables() function

Code: Select all

OracleCommand insertCommand=new OracleCommand("your insert query to DMUtilisateur table", oracleConnection);
insertCommand.ExecuteNonQuery();
Thread.Sleep(10000);

Please also note that the OracleDependency feature does not work in Direct mode.

[Feneck91]

I must put Direct to true for several month, devart don't know why it don't work when I set it to false !

Code: Select all

                // Init Connection string
                OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
                oraCSB.Direct = false; // http://forums.devart.com/viewtopic.php?f=1&t=29044&sid=de989fc560357196643526db2ba70821 <= Please also note that the OracleDependency feature does not work in Direct mode.
                oraCSB.Server = DataManager.Properties.Settings.Default.DatabaseHost;
                oraCSB.Port = int.Parse(DataManager.Properties.Settings.Default.DatabasePort);
                oraCSB.Sid = "orcl";
                oraCSB.UserId = "username";
                oraCSB.Password = "password";
                oraCSB.Unicode = true;
                oraCSB.Pooling = false;
                oraCSB.PersistSecurityInfo = true; // Important else the password is lost after use it : http://forums.devart.com/viewtopic.php?f=30&t=27911
                DatabaseManagerContext.Connection = new OracleConnection(oraCSB.ConnectionString);

Exception :
[System.Data.Entity.Core.ProviderIncompatibleException] = {"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct."}
HResult = -2146233087
InnerException = {"The provider did not return a ProviderManifestToken string."}

Any idea ?

[MariiaI]

Exception :
[System.Data.Entity.Core.ProviderIncompatibleException] = {"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct."}
HResult = -2146233087
InnerException = {"The provider did not return a ProviderManifestToken string."}
Any idea ?

Please specify the full call stack of this error with all inner exceptions.

[Feneck91]

The project was stopped for one year but I'm back.

I have read the How To Configure Oracle Instant Client.

Lot of evolutions for this problem but it don't work yet...
The first problem was : I haven't understood that I needed an Oracle Instant Client !

In my sample, I have installed the instant client into C:\instantclient_12_1 directory.

I list here all the problems I have fixed :

1. Download Oracle Instant Client : I'm on Windows 7 64 bits system but my C# project is compiled into x86 mode (32 bits) (see Generation / target plateform = x86 into project settings) : you must download the same target as the software that use it (for me 32 bits) !
2. If you are working on 64 bits system and your software is in 32 bits, you must install registry keys into HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ and not into HKEY_LOCAL_MACHINE\SOFTWARE\ else it cannot found registry entry.
3. Like explain into tutorial : add TNS_ADMIN = C:\instantclient_12_1 into environment variable.
4. Your registry sample must be corrected ! All backslash has been lost !
   Here the registry file to import (with Wow6432Node but you can remote it depending of your target plateform).

   Code: Select all

   Windows Registry Editor Version  5.00
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]
   "ORACLE_HOME"="C:\\instantclient_12_1"
   @=""
   "ORACLE_HOME_NAME"="OraHome"
   "ORACLE_GROUP_NAME"="Oracle  -  OraHome"
   "NLS_LANG"=""
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ALL_HOMES]
   "HOME_COUNTER"="1"
   "DEFAULT_HOME"="OraHome"
   "LAST_HOME"="0"
   @=""
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ALL_HOMES\ID0]
   "NAME"="OraHome"
   "PATH"="C:\\instantclient_12_1"
   "NLS_LANG"=""
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\HOME0]
   "ORACLE_HOME"="C:\\instantclient_12_1"
   "ORACLE_SID"="ORCL"
   "ID"="0"
   "ORACLE_GROUP_NAME"="Oracle  -  OraHome"
   "ORACLE_HOME_NAME"="OraHome"
   "NLS_LANG"=""
   "ORACLE_HOME_KEY"="Software\\ORACLE\\HOME0"
   

5. I use a class derived from DbConfiguration. I must create a IManifestTokenResolver class into my derived DbConfiguration class constructor :

   Code: Select all

       public class DatabaseManagerFactoryConfiguration : DbConfiguration
       {
           public DatabaseManagerFactoryConfiguration()
           {
               SetManifestTokenResolver(new CManifestTokenResolverOracle());
           }
       }
   
   
       internal sealed class CManifestTokenResolverOracle : IManifestTokenResolver
       {
           /// <summary>
           /// The default token resolver.
           /// </summary>
           private static readonly IManifestTokenResolver DefaultManifestTokenResolver = new DefaultManifestTokenResolver();
   
           public string ResolveManifestToken(DbConnection _dbConnection)
           {
               string strReturn;
   
               if (here test if your are trying to connect in direct mode or not)
               {   // Here : NOT DIRECT MODE
                   strReturn = "Oracle";//"Oracle, 11.2.0.1"; <-- Both seems working
               }
               else
               {
                   strReturn = DefaultManifestTokenResolver.ResolveManifestToken(_dbConnection);
               }
   
               return strReturn;
           }
       }
   
   

I still have an error message :

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Stack Trace :

à Devart.Data.Oracle.aq.a(b8 A_0, o A_1)
à Devart.Data.Oracle.OracleInternalConnection..ctor(b8 connectionOptions, OracleInternalConnection proxyConnection)
à Devart.Data.Oracle.ao.a(n A_0, Object A_1, DbConnectionBase A_2)
à Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, n A_1, DbConnectionBase A_2)
à Devart.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0, DbConnectionBase A_1)
à Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
à Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.Oracle.OracleConnection.Open()
à Devart.Common.Entity.ae.a(DbConnection A_0)
à Devart.Data.Oracle.Entity.ay.a(OracleConnection A_0)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.GetDbProviderManifestToken(DbConnection connection)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.a(DbConnection A_0)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
à System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
à System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase()
à System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
à System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update()
à DataManagerDAL.DataManagerFactory.ADatabaseManagerFactory.InitDatabaseModel() dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\ADatabaseManagerFactory.cs:ligne 200
à DataManagerDAL.DataManagerFactory.DatabaseManagerFactoryOracle.InitDatabaseModel() dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\DataManagerFactoryOracle.cs:ligne 199
à DataManagerDAL.DataManagerFactory.ADatabaseManagerFactory.SetConnectionInformations(Nullable`1 _connectionInformations) dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\ADatabaseManagerFactory.cs:ligne 268
à DataManager.DataManagerApp.OnStartup(StartupEventArgs _eStartupEvent) dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManager\DataManagerApp.xaml.cs:ligne 151

Questions :

• Really need to add client folder to PATH ? It seem's to work without it
• What is missing ? Need to copy some ora files into C:\instantclient_12_1 folder ?I have tried but nothing is working !

If I found a solution, I'll reply here !

EDIT : I have found a way set the OracleConnectionStringBuilder server like :

Code: Select all

oraCSB.Server = String.Format("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})))",
                               _connectionInformations.Value.Server,
                               _connectionInformations.Value.Port,
                               _connectionInformations.Value.Sid
                             );

Will try to understand.
Modifying Path is not needed !

Don't forget to set access to your user :

Code: Select all

GRANT change notification TO the_user;

[Pinturiccio]

If we understood you correctly, the issue is solved. Could you please confirm it?

[Feneck91]

Yes but it is strange to must do all of this to make it work.
Your tutorial is a little bit simple ! I don't think I have the simplest way to make it work...

Another problem :
If OracleConnectionStringBuilder.Unicode is set to true on connection string, the OracleNotificationEventArgs is unreadable, only the first char is read on table name, rowid...

Patch : Get the connection, make a clone and set Unicode to False.
It's work but...

I get the rowid field ! I record only the ID (primary key) of each data into my software !
How can I get the ID in place of rowid ?
I use code first.
For the moment, this notification cannot be used to update the data displayed to the user because I cannot use the rowid !

[Pinturiccio]

How can I get the ID in place of rowid ?

If you got rowid, you can get the corresponding ID as well. Suppose, you have stored the rowid in a variable:

Code: Select all

String rowid

With it you can get ID with the following query:

Code: Select all

"select ID from TABLE_NAME where rowid='"+rowid+"'";

[Feneck91]

The problem is not this one !
The problem is : when I receive the OracleNotificationEventArgs it contains ONLY the ROWID.
To update GUI, I have the primary key only.

How to make the link between them ? OK, if update / insert I can make request !
But..... when row is deleted, how can I know Which ID is because making a request will not return deleted rows !

I have a way but it force to read ALL the rows to find wich rows are deleted !
I could find by making a command.ExecuteReader() BEFORE the notification and keep the reader while no notification fire !

I will make a post to explain how to use the Oracle Database change and may be some peaple will have another ideas...

[Pinturiccio]

But..... when row is deleted, how can I know Which ID is because making a request will not return deleted rows !

You can use the flashback feature. For example, use the following query:

Code: Select all

"select ID from TABLE_NAME AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) where rowid = '" + rowid + "'";

For more information about flashback feature, please refer to http://docs.oracle.com/cd/B12037_01/app ... fns_fl.htm

[Feneck91]

It's not work all the time !
Create and destroying fast => the element is not found !
I have done this request :

Code: Select all

Select ID from TABLE VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) AND SYSTIMESTAMP where rowid = '" + rowid + "'";

It's work better !