4

I'm trying for the first time to dequeue from a queue on my company production environment by ODP.NET and C#.

I used the official ODP.NET example to develop my client got from the installation examples folder.

The queue is an Oracle AQ Topic (multi consumer) addressed by many other company services written with J2EE.

I wrote the UDT mapping classes following the ODP development guidelines ( http://docs.oracle.com/cd/E11882_01/win.112/e23174/featUDTs.htm#ODPNT0024 ).

The issue is: my client connects to the queue, by it never gets any message (others connected java clients get them).

Here is my code (without UDT classes, for keep short my post):

        string constr = "user id=<USER_ID>;password=<PASSWORD>;data source=<DATASOURCE>";
        OracleConnection conListen = new OracleConnection(constr);

        OracleAQQueue queueListen = new OracleAQQueue("<TOPIC_QUEUE_ID>", conListen);            

        try
        {
            conListen.Open();

            queueListen.MessageType = OracleAQMessageType.Udt;
            queueListen.DequeueOptions.ConsumerName = "Test_Subscriber_ID";
            queueListen.UdtTypeName = "SYS.AQ$_JMS_TEXT_MESSAGE";
            queueListen.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
            queueListen.DequeueOptions.Wait = 60;

            OracleTransaction txn = conListen.BeginTransaction();

            OracleAQMessage deqMsg = queueListen.Dequeue();                

            txn.Commit();
        }
        catch (Exception e)
        {
           Console.WriteLine("Error: {0}", e.Message);
        }
        finally
        {
            queueListen.Dispose();
            conListen.Close();
            conListen.Dispose();
        }

Another detail: after the connection the "all_queue_subscribers" view doesn't containt my subscriber "Test_Subscriber_ID".

Thank you all!

Antonio Petricca
  • 8,891
  • 5
  • 36
  • 74
  • Have a look at this article and checking out the source code that is provided with it: Article: http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69net-099734.html Code for article: http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69odt-131369.zip It is better than the samples you found. – Christian Shay Aug 20 '15 at 17:18
  • 1
    UDTs in ODP.NET are tricky. If you are going to use them, you should start out by using the Code Generation Wizard as part of the Oracle Developer Tools for Visual Studio. https://apex.oracle.com/pls/apex/f?p=44785:24:1323236364075:::24:P24_CONTENT_ID,P24_PROD_SECTION_GRP_ID,P24_PREV_PAGE:10199,,24 – Christian Shay Aug 20 '15 at 17:21
  • Please, the problem is not UDTs, but the fact that I never get any message from the queue. – Antonio Petricca Aug 20 '15 at 17:26
  • Ok, check out the other code I provided for ODP.NET and AQ as a sanity check. I see you are using 11.2. Make sure you are not hitting an old bug which requires patching both database and client. See my answer and workaround here: http://stackoverflow.com/questions/25629461/oraclecommand-with-oracledependency-waiting-forever (this is for OracleDependency but it also affects AQ, and I believe the bug did not impact jdbc) – Christian Shay Aug 20 '15 at 18:16
  • Thank you, I'm going to what you told me. Thank you. – Antonio Petricca Aug 21 '15 at 11:11
  • Hi @AntonioPetricca I'm struggling to get my UDT class working for .AQ$_JMS_TEXT_MESSAGE. Would you be able to post your class you made, or something like it? – Matthew Dec 11 '19 at 04:03
  • Hi @Matthew the code is exactly the one I posted here, there is nothing else in my class, sorry! – Antonio Petricca Dec 11 '19 at 09:43
  • @AntonioPetricca not to worry I worked out what the UDT class should be in the end by using Oracle Developer Tools for Visual Studio to inspect the schema for Queue Table > USER_DATA field > SYS.AQ$_JMS_TEXT_MESSAGE. Thanks Christian Shay – Matthew Dec 12 '19 at 03:32
  • ... also with help from this UDT class https://stackoverflow.com/a/10828307/8595398 and here https://stackoverflow.com/a/32412078/8595398 – Matthew Dec 12 '19 at 03:38

1 Answers1

4

Problem solved! I missed to create on my own the subscriber by the following script:

declare
  vQueue      varchar2(255) := '<TOPIC_QUEUE_ID>';
  vId         varchar2(255) := 'Test_Subscriber_ID';
  vSubscriber SYS.AQ$_AGENT;
begin 
  vSubscriber := SYS.AQ$_AGENT(vId, null, null);

  dbms_aqadm.add_subscriber(
    queue_name     => vQueue,
    subscriber     => vSubscriber,
    queue_to_queue => false,
    delivery_mode  => DBMS_AQADM.PERSISTENT
  );

  /*dbms_aqadm.enable_db_access(
    agent_name  => vId,
    db_username => '<USER_ID>'
  );*/
end;

With J2EE the subscriber creation is automatic!

Many thanks, Antonio

Antonio Petricca
  • 8,891
  • 5
  • 36
  • 74