This discussion is archived
7 Replies Latest reply: Mar 6, 2013 2:01 AM by bencol RSS

Messages not dequeuing - plsqlcallback procedure not being invoked

bencol Pro
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points