none
ODP.net Could not able to listen notification from Oracle Db RRS feed

  • Question

  • My oracle 11g db is some server.

    I am connecting to Oracle to thorugh my .net 4.0 app using ODP.Net.

    In oracle DB i have created a queue as below

    BEGIN
      DBMS_AQADM.CREATE_QUEUE_TABLE(
        queue_table=>'SYSTEM.test_q_tab',
        queue_payload_type=>'RAW',
        multiple_consumers=>FALSE);
     
      DBMS_AQADM.CREATE_QUEUE(
        queue_name=>'SYSTEM.test_q',
        queue_table=>'SYSTEM.test_q_tab');
     
      DBMS_AQADM.START_QUEUE(queue_name=>'SYSTEM.test_q');
    END;
    /

    and i am using the follwoing code to get notifications when a messge is enqueued to my queue

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Windows.Forms;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    using System.Text;

    namespace ODPSample
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            //[STAThread]
            //static void Main()
            //{
            //    //Application.EnableVisualStyles();
            //    //Application.SetCompatibleTextRenderingDefault(false);
            //    /////Application.Run(new Form1());
            //}

            static bool isNotified = false;

            static void Main(string[] args)
            {
                // Create connection
                string constr = "user id=SYSTEM;password=password;Data Source= some datasource";
                OracleConnection con = new OracleConnection(constr);
              

                // Create queue
                OracleAQQueue queue = new OracleAQQueue("SYSTEM.test_q", con);

              

                try
                {
                    // Open connection
                    con.Open();

                    // Set message type for the queue
                    queue.MessageType = OracleAQMessageType.Raw;

                    // Add the event handler to handle the notification. The
                    // MsgReceived method will be invoked when a message is enqueued
                    queue.MessageAvailable +=
                      new OracleAQMessageAvailableEventHandler(Program.MsgReceived);

                    Console.WriteLine("Notification registered...");

                    // Begin txn for enqueue
                    OracleTransaction txn = con.BeginTransaction();

                    Console.WriteLine("Now enqueuing message...");

                    // Prepare message and RAW payload
                    OracleAQMessage enqMsg = new OracleAQMessage();
                    byte[] bytePayload = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
                    enqMsg.Payload = bytePayload;

                    // Prepare to Enqueue
                    queue.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;

                    // Enqueue message
                    queue.Enqueue(enqMsg);

                    Console.WriteLine("Enqueued Message Payload      : "
                      + ByteArrayToString(enqMsg.Payload as byte[]));
                    Console.WriteLine("MessageId of Enqueued Message : "
                      + ByteArrayToString(enqMsg.MessageId));
                    Console.WriteLine();

                    // Enqueue txn commit
                    txn.Commit();

                    // Loop while waiting for notification
                    while (isNotified == false)
                    {
                        System.Threading.Thread.Sleep(2000);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Error: {0}", e.Message);
                }
                finally
                {
                    // Close/Dispose objects
                    queue.Dispose();
                    con.Close();
                    con.Dispose();
                }
            }

            static void MsgReceived(object src, OracleAQMessageAvailableEventArgs arg)
            {
                try
                {
                    Console.WriteLine("Notification Received...");
                    Console.WriteLine("QueueName : {0}", arg.QueueName);
                    Console.WriteLine("Notification Type : {0}", arg.NotificationType);

                    //following type-cast to "byte[]" is required only for .NET 1.x
                    byte[] notifiedMsgId = (byte[])arg.MessageId[0];
                    Console.WriteLine("MessageId of Notified Message : "
                      + ByteArrayToString(notifiedMsgId));
                    isNotified = true;
                }
                catch (Exception e)
                {
                    Console.WriteLine("Error: {0}", e.Message);
                }
            }

            // Function to convert byte[] to string
            static private string ByteArrayToString(byte[] byteArray)
            {
                StringBuilder sb = new StringBuilder();
                for (int n = 0; n < byteArray.Length; n++)
                {
                    sb.Append((int.Parse(byteArray[n].ToString())).ToString("X"));
                }
                return sb.ToString();
            }

        }
    }

    The ODP.Net Could able to notify our application that a messgae is inserted to oracle DB.

    Your immediate response will help us alot


    prabhukishan

    prabhukishan
    Tuesday, June 28, 2011 1:05 PM

Answers