GET Experience Using Object Change Notification in Oracle / Sudo Null IT News FREE

Recently I had to work with an interesting matter, and I definite to share this with the readers of Habrahabr. I want to talk virtually the experience of using Object Interchange Notification in Oracle. About how to get word that the data has changed without devising a request.

Indeed why do we need Physical object Change Apprisal (OCN)

We required to observe the data changes in the database. The result that first came to mind was a simple ane - updating the data over a conferred interval (for example, in one case a little). The disadvantages of this approach are obvious - the data may non have changed over the selected period of time and then we just load the server and the data transfer communication channel (the information that we observe is very winding). Or the information bequeath change at the beginning of the interval and we won't live about IT until the time runs out (this is non so critical, merely the first is likely). Given that there could be quite lot of customers, the first minus is really serious. Fortunately, we found that Oracle offers a amended solution to this problem, starting with version 10g.

How does OCN form?

OCN give the axe work both exclusively connected the server pull of the database, and inform the client application through with a special interface that the data IT observes has changed.

Details of how this kit and caboodle inside is effortless to Google; I thought that you should not inflate the article with information, which is already abundant, so I'll distinguish you but briefly.

Low gear, you essential enable the server to use request registrations and allow the use of co-occurrent notifications. Then, the drug user registers an alert. When registering, a postulation is indicated that must be determined, for instance:

          pick out * from TESTUSER.TESTTABLE;        

After the data in the result set changes, oracle will notify the client about this (either send a special packet all over the documented ip, or execute the certified stored process). Bread and butter for this mechanism is built into the Database Provider for .NET and Java. Since we wrote our application in .NET, I wish continue to speak in its context (although most of the things, with the exception of the sampling codification, I suppose, do not hinge on this, because the to the highest degree interesting thing is happening on the server).

Where without encipher example

Let's go ready:

• We countenance the substance abuser to register continuous notifications;

                GRANT Interchange NOTIFICATION TO TESTUSER;            

• Situated the value of the "JOB_QUEUE_PROCESSES" variable to a value greater than 0 (by default IT is already greater than 0, only short something has broken the defaults on your server);

                ALTER Organization SET "JOB_QUEUE_PROCESSES"=4;            

• We create a see, add a link to the Oracle.ManagedDataAccess.dll depository library (you can download it from the Seer internet site on with the ODP.Sack with ODAC box, or pick it up via NuGet);
• Then you tin can just endeavour.

Eager code example

              using System; using System.Threading; using System.Information; using Oracle.ManagedDataAccess.Guest; namespace ChangeNotificationSample {     public class ChangeNotificationSample     {         exoteric static bool IsNotified;         public static nullif Main(string[] args)         {             const string constr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(Host=Devoted)(SERVICE_NAME=myServiceName))); Validate Connexion = true; Connection Timeout=180;User Idaho=TESTUSER;Password=myPass";             OracleConnection in opposition = naught;             try             {                 con = radical OracleConnection(constr);                 OracleCommand cmd = new OracleCommand("pick out * from TESTUSER.TESTTABLE", con);                 con.Open();                 // Устанавливаем номер порта для прослушивания уведомлений.                 OracleDependency.Port = 1005;                 //Создаем OracleDependency                 var dep = new OracleDependency(cmd);                 //указывает, что оповещать надо не один раз                 //в противном случае, регистрация удалится из базы после первого оповещения                 cmd.Notification.IsNotifiedOnce = false;                 //таймаут в секундах, время жизни регистрации                 cmd.Notification.Timeout = 300;                 //Подписываемся на событие "Что-то изменилось"                 dep.OnChange += OnMyNotificaton;                 //теперь, когда мы выполним команду, уведомление зарегистрируется на сервере                 cmd.ExecuteNonQuery();             }             charm (Elision e)             {                 Cabinet.WriteLine(e.Subject matter);             }             finally             {                 if (con != null)                 {                     learn.Close();                     con.Dispose();                 }             }             // Чтобы не нагружать процессор, будем проверять наличие уведомления только 10 раз в секунду             while (IsNotified == false)             {                 Wander.Slumber(100);             }             Console.ReadLine();         }         individual unchangeable void OnMyNotificaton(object sender, OracleNotificationEventArgs eventArgs)         {             Console.WriteLine("Telling Accepted");             DataTable changeDetails = eventArgs.Inside information;             Console.WriteLine("Data has denaturized in {0}",               changeDetails.Rows[0]["ResourceName"]);             IsNotified = true;         }     } }                          

I almost completely took an example from the documentation, only supplemented it with a couple of important points in my belief and slightly reduced information technology. After the encipher is written, compiled, and the program is jetting, you can go and execute something like:

          insert into testuser.testtable (col1, col2) values (1,1); commit;        

Having complete, we should see this:

Hooray, everything works! More detailed information can personify taken, for example, in the documentation . It's better to go even to the point that the documentation usually doesn't indicate - the difficulties encountered.

Minuses

For our pillow slip, there were non indeed many minuses that they outweighed the advantages of this coming, and some of them can barely be known as minuses, so, nuances. However, it seems to me that the foe should live famous in individual.

First of all, we tried to run IT at home and everything worked fine for United States. And, as it normally happens, nothing worked for the customer. More unfeignedly, it didn't even set out. Finding impermissible what's the weigh turned out to be a rather non-little task. After you register, you can see it away completing a special request:

          quality * from USER_CHANGE_NOTIFICATION_REGS;        

So, we saw this registration immediately after initiation, but at that moment when the notification should have worked, the registration record bu disappeared. Unfortunately, we could not get anything in the logs that would explain this behavior. I had to take a chance and without looking to blessed the firewall for everything. Fortunately, we guessed just. Therefore, my first testimonial is to remember to chit the firewall and specify the port when registering notifications.

Not all registration requests are supported. We must remember that it may not be possible to record the information in one petition and hang up a apprisal connected IT. If there are a lot of joins in the request, it's amend to mentally prepare yourself to write a similar simply simple petition. In addition, if you register an alert for a query with join, you wish actually register as many alerts as there are tables in the query (each happening its own hold over), which tail waste resources.

By default, registrations are created without a timeout. That is, so to speak, forever. As a rule, nobody needs such registration, because the application's practical clock time is usually finite. Such registration is very difficult to pick come out of the closet from the database, especially if there will live a lot of them. The discipline way, about likely, would be to unregister when you finish working with it (if you DO this in the same connection, and there is a connection to an OracleDependency instance, this is easy to do, sporting call the RemoveRegistration method along the instance of the OracleDependency class). However, it seems to me that this method acting is only when suitable for ideal worlds in which your applications ne'er crash, communication channels never break, and resources are always freed. Otherwise, ultimately, we will have the same thing as without deleting, however, the consequences will begin to certify future (just when we go into production). We solved the problem this way - we began to always indicate a timeout during registration. And a few seconds before its expiration, blue-pencil the rusty registration and create a new 1 (to minimize the time of petit mal epilepsy of registration). The solution, of course, is not the most idealistic, but in principle we were satisfied. If you have ideas happening how to do information technology better, I will make up very happy to rewrite this piece of code.

In order to make alerts work faster within the framework of one application, we separated into separate classes the system of logic for workings with the database, the logic for notifying our objects about data change events, and the logic for managing some. Thanks to this, we were able to habit one commons apprisal mechanics and direct notifications wrong the application manually, without expiration outside (in oracle). For a user who works as part of his session and inevitably to imag the result of his actions instantly, everything deeds easily.

thanks

That they study my article to this place. I would be very laughing for whatever feedback, from unfavorable judgment of my code and methods of presenting the real to the emotions you experienced in the process of getting to know this applied science. If you tell me nigh your experience with OCN, I will likewise be very happy and can contribute some more material to my piggy bank.

Thanks beforehand.

DOWNLOAD HERE

GET Experience Using Object Change Notification in Oracle / Sudo Null IT News FREE

Posted by: brownagen1949.blogspot.com

Share this post