7 Replies Latest reply: Mar 6, 2013 4:01 AM by bencol RSS

    Messages not dequeuing - plsqlcallback procedure not being invoked

    bencol
      I'm having a problem with messages not dequeuing

      My code is quite complicated, so I tried this example: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8760267539329, except for:
      sys@ORA920> begin
        2     dbms_aqadm.grant_system_privilege
        3     ('ENQUEUE_ANY','AQ',FALSE);
        4     dbms_aqadm.grant_system_privilege
        5     ('DEQUEUE_ANY','AQ',FALSE);
        6  end;
        7  /
      My user, DQIDW has execute privileges on dbms_aq and the AQ_ADMINISTRATOR_ROLE role, and is the data/queue/code owner - no other databases or users are involved.

      I think this is sufficient according to the documentation (http://docs.oracle.com/cd/E11882_01/server.112/e11013/manage.htm)

      Once I've run Tom Kyte's example I have a row sitting in OBJMSGS80_QTAB and nothing in message_table - nothing seems to change over the next hour.

      What do I need to investigate this further.

      Many Thanks,

      Ben
      BANNER
      ________________________________________________________________________________
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      This is what I run:
      DWDQI> CREATE type dqidw.Message_typ as object
        2    ( subject VARCHAR2(30), text VARCHAR2(80));
        3  /
      
      Type created.
      
      DWDQI>
      DWDQI> begin
        2      DBMS_AQADM.CREATE_QUEUE_TABLE
        3      ( queue_table => 'dqidw.objmsgs80_qtab',
        4        queue_payload_type =>  'dqidw.Message_typ',
        5        multiple_consumers => TRUE );
        6
        7      DBMS_AQADM.CREATE_QUEUE
        8      ( queue_name => 'MSG_QUEUE',
        9        queue_table => 'dqidw.objmsgs80_qtab');
       10
       11      DBMS_AQADM.START_QUEUE
       12      (  queue_name => 'MSG_QUEUE');
       13  end;
       14  /
      
      PL/SQL procedure successfully completed.
      
      DWDQI>
      DWDQI> create procedure enqueue_msg( p_msg in varchar2 )
        2  as
        3   enqueue_options dbms_aq.enqueue_options_t;
        4   message_properties dbms_aq.message_properties_t;
        5   message_handle RAW(16);
        6   message dqidw.message_typ;
        7  BEGIN
        8     message := message_typ('NORMAL MESSAGE',  p_msg );
        9     dbms_aq.enqueue(queue_name => 'msg_queue',
       10                     enqueue_options => enqueue_options,
       11                     message_properties => message_properties,
       12                     payload => message,
       13                     msgid => message_handle);
       14  end;
       15  /
      
      Procedure created.
      
      DWDQI>
      DWDQI> create table message_table( msg varchar2(4000) );
      
      Table created.
      
      DWDQI>
      DWDQI> create or replace procedure notifyCB( context raw,
        2                                        reginfo sys.aq$_reg_info,
        3                                        descr sys.aq$_descriptor,
        4                                        payload raw,
        5                                        payloadl number)
        6  as
        7   dequeue_options dbms_aq.dequeue_options_t;
        8   message_properties dbms_aq.message_properties_t;
        9   message_handle RAW(16);
       10   message dqidw.message_typ;
       11  BEGIN
       12     dequeue_options.msgid := descr.msg_id;
       13     dequeue_options.consumer_name := descr.consumer_name;
       14     DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
       15                     dequeue_options => dequeue_options,
       16                     message_properties => message_properties,
       17                     payload => message,
       18                     msgid => message_handle);
       19     insert into message_table values
       20     ( 'Dequeued and processed "' || message.text || '"' );
       21     COMMIT;
       22  END;
       23  /
      
      SP2-0804: Procedure created with compilation warnings
      
      DWDQI> sho err
      Errors for PROCEDURE NOTIFYCB:
      
      LINE/COL ERROR
      ________ _________________________________________________________________
      1/21     PLW-06010: keyword "CONTEXT" used as a defined name
      DWDQI>
      DWDQI> begin
        2      dbms_aqadm.add_subscriber
        3      ( queue_name => 'dqidw.msg_queue',
        4        subscriber => sys.aq$_agent( 'recipient', null, null ) );
        5  end;
        6  /
      
      PL/SQL procedure successfully completed.
      
      DWDQI>
      DWDQI> BEGIN
        2      dbms_aq.register
        3      ( sys.aq$_reg_info_list(
        4          sys.aq$_reg_info('DQIDW.MSG_QUEUE:RECIPIENT',
        5                            DBMS_AQ.NAMESPACE_AQ,
        6                           'plsql://AQ.notifyCB',
        7                            HEXTORAW('FF')) ) ,
        8        1 );
        9  end;
       10  /
      
      PL/SQL procedure successfully completed.
      
      DWDQI>
      DWDQI>
      DWDQI>
      DWDQI> select * from message_table;
      
      no rows selected
      
      DWDQI>
      DWDQI> exec enqueue_msg( 'This is a test....' );
      
      PL/SQL procedure successfully completed.
      
      DWDQI>
      DWDQI> commit;
      
      Commit complete.
      
      DWDQI>
      DWDQI> select * from message_table;
      
      no rows selected
      and looking round the related tables
        1* select * from objmsgs80_qtab
      
      Q_NAME                         MSGID
      ______________________________ ________________________________
      CORRID
      ________________________________________________________________________________________________________________________
        PRIORITY      STATE DELAY                                                                       EXPIRATION
      __________ __________ ___________________________________________________________________________ __________
      TIME_MANAGER_INFO                                                           LOCAL_ORDER_NO   CHAIN_NO       CSCN
      ___________________________________________________________________________ ______________ __________ __________
            DSCN ENQ_TIME                                                                    ENQ_UID
      __________ ___________________________________________________________________________ ______________________________
      ENQ_TID                        DEQ_TIME
      ______________________________ ___________________________________________________________________________
      DEQ_UID                        DEQ_TID                        RETRY_COUNT EXCEPTION_QSCHEMA
      ______________________________ ______________________________ ___________ ______________________________
      EXCEPTION_QUEUE                   STEP_NO RECIPIENT_KEY DEQUEUE_MSGID                    SENDER_NAME
      ______________________________ __________ _____________ ________________________________ ______________________________
      SENDER_ADDRESS       SENDER_PROTOCOL
      ____________________ _______________
      USER_DATA(SUBJECT, TEXT)
      ________________________________________________________________________________________________________________________
      USER_PROP()
      ________________________________________________________________________________________________________________________
      MSG_QUEUE                      1F9BB7888DD9462BB4F7E026C92D8E5F
      
               1          0
                                                                                               0          0          0
               0 28-FEB-13 18.41.07.677000                                                   DQIDW
      77.12.1223030
                                                                              0
                                              0             0
      
      MESSAGE_TYP('NORMAL MESSAGE', 'This is a test....')
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_F;
      
        COUNT(*)
      __________
               1
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_G;
      
        COUNT(*)
      __________
               0
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_H;
      
        COUNT(*)
      __________
               1
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_I;
      
        COUNT(*)
      __________
               1
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_L;
      
        COUNT(*)
      __________
               0
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_S;
      
        COUNT(*)
      __________
               3
      
      DWDQI> select count(*) from AQ$_OBJMSGS80_QTAB_T;
      
        COUNT(*)
      __________
               0
        • 1. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
          spur230
          I just successfully did the example of tom in Oracle 11.2.0.3. Everything looks fine with your code too.

          Also look at the example give by kamal kishore:
          PL/SQL notification / DBMS_AQ.REGISTER
          • 2. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
            bencol
            Spur230,

            I was confident the code was OK, but it isn't working on my database. I want to get this example working before I look at anything different of more compilcated

            From the docs: http://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_trbl.htm#ADQUE0700

            1. I have no rows in DBA_QUEUE_SCHEDULES, and do not know if these are necessary or what process sets them up.
            2. There are no database links involved
            3. job_queue_processes = 10, so once I have schedules (if these are necessary) I should have enough
            4. OBJMSGS80_QTAB has the following:
            Q_NAME                        : MSG_QUEUE
            MSGID                         : 57F5DDE51B304B0DAB613DDA10F5276B
            CORRID                        :
            PRIORITY                      : 1
            STATE                         : 0
            DELAY                         :
            EXPIRATION                    :
            TIME_MANAGER_INFO             :
            LOCAL_ORDER_NO                : 0
            CHAIN_NO                      : 0
            CSCN                          : 0
            DSCN                          : 0
            ENQ_TIME                      : 01-MAR-13 15.59.02.904000
            ENQ_UID                       : DQIDW
            ENQ_TID                       : 77.27.1226989
            DEQ_TIME                      :
            DEQ_UID                       :
            DEQ_TID                       :
            RETRY_COUNT                   : 0
            EXCEPTION_QSCHEMA             :
            EXCEPTION_QUEUE               :
            STEP_NO                       : 0
            RECIPIENT_KEY                 : 0
            DEQUEUE_MSGID                 :
            SENDER_NAME                   :
            SENDER_ADDRESS                :
            SENDER_PROTOCOL               :
            and AQ$_OBJMSGS80_QTAB_F has:
            Q_NAME                        : MSG_QUEUE
            ROW_ID                        : AAAfRYAAFAAATRfAAA
            MSGID                         : 57F5DDE51B304B0DAB613DDA10F5276B
            CORRID                        :
            PRIORITY                      : 1
            STATE                         : 0
            DELAY                         :
            EXPIRATION                    :
            ENQ_TIME                      : 01-MAR-13 15.59.02.904000
            ENQ_UID                       : DQIDW
            ENQ_TID                       : 77.27.1226989
            DEQ_TIME                      :
            DEQ_UID                       :
            DEQ_TID                       :
            RETRY_COUNT                   : 0
            EXCEPTION_QSCHEMA             :
            EXCEPTION_QUEUE               :
            CSCN                          : 0
            DSCN                          : 0
            CHAIN_NO                      : 0
            LOCAL_ORDER_NO                : 0
            TIME_MANAGER_INFO             :
            STEP_NO                       : 0
            SENDER_NAME                   :
            SENDER_ADDRESS                :
            SENDER_PROTOCOL               :
            DEQUEUE_MSGID                 :
            DELIVERY_MODE                 : PERSISTENT
            SEQUENCE_NUM                  : 0
            MSG_NUM                       : 0
            QUEUE_ID                      : 128107
            SUBSCRIBER_ID                 : 1
            SUBSCRIBER_NAME               : 0
            QUEUE_EVTID                   : 0
            6. No jobs, so no jobs running
            7. This is what I have in dba_queue_tables for %PROP_TABLE%
            OWNER                         : SYS
            QUEUE_TABLE                   : AQ_PROP_TABLE
            TYPE                          : OBJECT
            OBJECT_TYPE                   : SYS.AQ$_NOTIFY_MSG
            SORT_ORDER                    : ENQUEUE_TIME
            RECIPIENTS                    : MULTIPLE
            MESSAGE_GROUPING              : NONE
            COMPATIBLE                    : 10.0.0
            PRIMARY_INSTANCE              : 0
            SECONDARY_INSTANCE            : 0
            OWNER_INSTANCE                : 1
            USER_COMMENT                  : Queue Table for Notification in AQ Prop. Schedulin
            SECURE                        : NO
            So nothing for sys.aq$_prop_table_instno
            And for dba_queues where name like '%NOTIFY%'
            OWNER                         : SYS
            NAME                          : AQ_PROP_NOTIFY
            QUEUE_TABLE                   : AQ_PROP_TABLE
            QID                           : 71982
            QUEUE_TYPE                    : NORMAL_QUEUE
            MAX_RETRIES                   : 5
            RETRY_DELAY                   : 0
            ENQUEUE_ENABLED               :   YES
            DEQUEUE_ENABLED               :   YES
            RETENTION                     : 0
            USER_COMMENT                  : Queue for Notifying events in AQ Prop. Scheduling
            NETWORK_NAME                  :
            8. this is from AQ$OBJMSGS80_QTAB
            CONSUMER_NAME                 : RECIPIENT
            DEQ_TXN_ID                    :
            DEQ_TIME                      :
            DEQ_USER_ID                   :
            PROPAGATED_MSGID              :
            ----------------------------
            9. Time to turn on 24040 tracing on ...
            • 3. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
              spur230
              bencol - Can you post the result for from DQIDW schema.

              select * from USER_SUBSCR_REGISTRATIONS

              Also , see if you have anything in SYS.AQ$AQ_SRVNTFN_TABLE_1
              • 4. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
                bencol
                Spur,

                Thank you for taking your time to look into this:

                USER_SUBSCR_REGISTRATIONS
                REG_ID                        : 1501
                SUBSCRIPTION_NAME             : "DQIDW"."MSG_QUEUE":"RECIPIENT"
                LOCATION_NAME                 : plsql://AQ.notifyCB
                USER#                         : 81
                USER_CONTEXT                  : FF
                CONTEXT_SIZE                  : 1
                NAMESPACE                     : AQ
                PRESENTATION                  : DEFAULT
                VERSION                       :
                STATUS                        : DB REG
                CONTEXT_TYPE                  : 0
                QOSFLAGS                      :
                PAYLOAD_CALLBACK              :
                TIMEOUT                       :
                REG_TIME                      : 01-MAR-13 17.52.00.516000 +00:00
                NTFN_GROUPING_CLASS           :
                NTFN_GROUPING_VALUE           :
                NTFN_GROUPING_TYPE            :
                NTFN_GROUPING_START_TIME      :
                NTFN_GROUPING_REPEAT_COUNT    :
                There is another row, but that it for another queue, that I'll look at once I get this simple one working.

                And AQ$AQ_SRVNTFN_TABLE_1:
                select msg_id from SYS.AQ$AQ_SRVNTFN_TABLE_1;
                
                MSG_ID
                ________________________________
                8D6639D666CA4A73BF0F58DBAFBBC34F
                146E63B644284A77A570DC40832F2C3C
                EC035A56BCD84B59A8D3644B0031412B
                F1FC473BD93847B9958F4722055C9439
                I don't know what type of object SYS.AQ$AQ_SRVNTFN_TABLE_1 is but:
                select * from SYS.AQ$AQ_SRVNTFN_TABLE_1
                                  *
                ERROR at line 1:
                ORA-22370: incorrect usage of method
                
                desc SYS.AQ$AQ_SRVNTFN_TABLE_1
                ERROR:
                ORA-04043: object SYS.AQ$AQ_SRVNTFN_TABLE_1 does not exist
                I only used msg_id because google told me it was there

                Ben
                • 5. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
                  spur230
                  LOCATION_NAME : plsql://AQ.notifyCB
                  Ben,

                  You created notifyCB in DQIDW but it is registered as AQ.notifyCB. Please unregister it and re-register as DQIDW.notifyCB.
                  BEGIN
                    2      dbms_aq.UNregister
                    3      ( sys.aq$_reg_info_list(
                    4          sys.aq$_reg_info('DQIDW.MSG_QUEUE:RECIPIENT',
                    5                            DBMS_AQ.NAMESPACE_AQ,
                    6                           'plsql://AQ.notifyCB',
                    7                            HEXTORAW('FF')) ) ,
                    8        1 );
                    9  end;
                  /
                  
                  BEGIN
                    2      dbms_aq.register
                    3      ( sys.aq$_reg_info_list(
                    4          sys.aq$_reg_info('DQIDW.MSG_QUEUE:RECIPIENT',
                    5                            DBMS_AQ.NAMESPACE_AQ,
                    6                           'plsql://DQIDW.notifyCB',
                    7                            HEXTORAW('FF')) ) ,
                    8        1 );
                    9  end;
                  /
                  Hope that helps.
                  • 6. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
                    bencol
                    Thank you very much. That fixed it. I must have checked that 10 times, and read over it. Must be all the other "dbms_aq. ..." I thought there would be something at a more fundamental level as I on;y created this test queue as my own one was failing - and they appeared to be failing in the same way.

                    I'll work on my actual queue, which has the schema specified correctly.

                    Ben
                    • 7. Re: Messages not dequeuing - plsqlcallback procedure not being invoked
                      bencol
                      Just to close this off. I was running this example as the queue I was developing was failing. The problem was the retry count was incrementing, but I wasn't seeing any errors. The errors were being logged in trace files on the db server, and my exception handler had a bug in it.

                      Thank you spur230 for you pointers and answer. MoS note 235397.1 is also useful.

                      Ben