This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 1:20 PM by spur230 RSS

PL/SQL notification / DBMS_AQ.REGISTER

41226 Newbie
Currently Being Moderated
There have been a number of discussions on this forum about PL/SQL notifications. I'm running
into the same problem:
- create the notification procedure
- register the procedure
- enqueue a message
- NOTHING HAPPENS
It seems that the notification procedure is not executed.
The discussions on this forum were all inconclusive. Did anybody get this to work? If yes, can
you post an example (i.e. a script that creates the queue, create the procedure, registers the
procedure and enqueues a message)?
Is there a table/view that contains the registrations?
What system parameters are relevant to this functionality?
Where are errors in calling the notification procedure logged?
  • 1. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    For Oracle9i and above DBMS_AQ.REGISTER can be used for PL/SQL callback notification.
    If running earlier version of Oracle, use DBMS_AQ.LISTEN to get a similar functionality.
    It depends upon the URL that you specify while registering, that decides if callback will be executed or
    not.
    If you post the code fragment(s) of how you created the notification procedure and how you registered it,
    maybe we can look to see if it is correct.
    There have been a number of discussions on this forum about PL/SQL notifications. I'm running
    into the same problem:
    - create the notification procedure
    - register the procedure
    - enqueue a message
    - NOTHING HAPPENS
    It seems that the notification procedure is not executed.
    The discussions on this forum were all inconclusive. Did anybody get this to work? If yes, can
    you post an example (i.e. a script that creates the queue, create the procedure, registers the
    procedure and enqueues a message)?
    Is there a table/view that contains the registrations?
    What system parameters are relevant to this functionality?
    Where are errors in calling the notification procedure logged?
  • 2. re:PL/SQL notification / DBMS_AQ.REGISTER
    253424 Newbie
    Currently Being Moderated
    Hallo,
    I have got the same problem as Erwin Groenendal. I wrote single user que and procedures to enque and deque as simple as I can do.
    pseudocode:
    1. create and start que - no error
    2. create proc for callback in pl/sql - no error
    3. register callback - no error
    4. enque message - noerror (no callback called)
    5. deque message - noerror

    in callback procedure I am writing into my own table with one varchar2 column to determine if the callback was processed.

    My register procedure looks like: (pseudocode point 3)
    procedure Register
    is
    XRegProc sys.aq$_reg_info;
    XRegList sys.aq$_reg_info_list;
    begin
    XRegProc := sys.aq$_reg_info('myprofile.Que1', dbms_aq.namespace_aq, 'plsql://myprofile.QCallBack1', hextoraw('FF'));
    XRegList := sys.aq$_reg_info_list(XRegProc);
    dbms_aq.register(XRegList, 1);
    end;

    in definition variable XRegProc I tryed all possibilities like:
    'plsql://myprofile.QCallBack1'
    'plsql://myprofile.QCallBack1?PR=0'
    'plsql://QCallBack1'
    'plsql://myprofile.QCallBack1'
    and so on.

    The Callback looks like:

    procedure QCallBack1(
    context out raw,
    reginfo out sys.aq$_reg_info,
    descr out sys.aq$_descriptor,
    payload out raw,
    payloadl out number)
    is
    begin
    insert into MyOwnTable (my_text) values ('Message notification callback called.');
    commit;
    end;

    with compilation i have not problems. I am running oracle 9i release 2.

    the enque procedure looks like:

    procedure put(
    APayload in raw,
    ACorrelation in varchar2)
    is
    XEnq_opts dbms_aq.enqueue_options_t;
    XMsg_props dbms_aq.message_options_t;
    XMsg_id raw(16);
    begin
    XMsg_props.correlation := ACorrelation;
    dbms_aq.enqueue('Que1', XEnq_opts, XMsg_props, APayload, XMsg_id);
    commit;
    end;

    All rights are corectly sets under SYSTEM user.
    Please help somebody why it does not work. I not see problem byt I need to call some procedure when new message arive into que. I need to have paralel process, I can not solve it with loops and so on.
    Thanks for every ideas. In next week I can show you source code. Today I can not.
    Regards, Zdenek.


    For Oracle9i and above DBMS_AQ.REGISTER can be used for PL/SQL callback notification.
    If running earlier version of Oracle, use DBMS_AQ.LISTEN to get a similar functionality.
    It depends upon the URL that you specify while registering, that decides if callback will be executed or
    not.
    If you post the code fragment(s) of how you created the notification procedure and how you registered it,
    maybe we can look to see if it is correct.
    There have been a number of discussions on this forum about PL/SQL notifications. I'm running
    into the same problem:
    - create the notification procedure
    - register the procedure
    - enqueue a message
    - NOTHING HAPPENS
    It seems that the notification procedure is not executed.
    The discussions on this forum were all inconclusive. Did anybody get this to work? If yes, can
    you post an example (i.e. a script that creates the queue, create the procedure, registers the
    procedure and enqueues a message)?
    Is there a table/view that contains the registrations?
    What system parameters are relevant to this functionality?
    Where are errors in calling the notification procedure logged?
  • 3. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    There are few problems that I can see with your code.
    Your callback procedure header should look like this:

    create or replace procedure QCallBack1(
    context RAW,
    reginfo SYS.AQ$_REG_INFO,
    descr SYS.AQ$_DESCRIPTOR,
    payload VARCHAR2,
    payloadl NUMBER) is
    BEGIN
    .
    .
    .
    END ;
    /

    And your callback notification does not include the name of the QUEUE subscriber. The first parameter
    to the AQ$_REG_INFO should be of this format: <QUEUE ADMIN>.<QUEUE_NAME>:<QUEUE SUBSCRIBER>
    When you say this:
    sys.aq$_reg_info('myprofile.Que1',dbms_aq.namespace_aq, 'plsql://myprofile.QCallBack1', hextoraw('FF'));

    You are not putting the name of the queue subscriber into the first parameter.
    Also, Your callback notification URL must be like this:

    'plsql://<user containing the callback proc>.<name of the callback proc>?PR=1'

    Note that if the callback procedure is in another schema, you must grant EXECUTE priv. to the user
    where the QUEUE has been setup.

    Hallo,
    I have got the same problem as Erwin Groenendal. I wrote single user que and procedures to enque and deque as simple as I can do.
    pseudocode:
    1. create and start que - no error
    2. create proc for callback in pl/sql - no error
    3. register callback - no error
    4. enque message - noerror (no callback called)
    5. deque message - noerror
    in callback procedure I am writing into my own table with one varchar2 column to determine if the callback was processed.
    My register procedure looks like: (pseudocode point 3)
    procedure Register
    is
    XRegProc sys.aq$_reg_info;
    XRegList sys.aq$_reg_info_list;
    begin
    XRegProc := sys.aq$_reg_info('myprofile.Que1', dbms_aq.namespace_aq, 'plsql://myprofile.QCallBack1', hextoraw('FF'));
    XRegList := sys.aq$_reg_info_list(XRegProc);
    dbms_aq.register(XRegList, 1);
    end;
    in definition variable XRegProc I tryed all possibilities like:
    'plsql://myprofile.QCallBack1'
    'plsql://myprofile.QCallBack1?PR=0'
    'plsql://QCallBack1'
    'plsql://myprofile.QCallBack1'
    and so on.
    The Callback looks like:
    procedure QCallBack1(
    context out raw,
    reginfo out sys.aq$_reg_info,
    descr out sys.aq$_descriptor,
    payload out raw,
    payloadl out number)
    is
    begin
    insert into MyOwnTable (my_text) values ('Message notification callback called.');
    commit;
    end;
    with compilation i have not problems. I am running oracle 9i release 2.
    the enque procedure looks like:
    procedure put(
    APayload in raw,
    ACorrelation in varchar2)
    is
    XEnq_opts dbms_aq.enqueue_options_t;
    XMsg_props dbms_aq.message_options_t;
    XMsg_id raw(16);
    begin
    XMsg_props.correlation := ACorrelation;
    dbms_aq.enqueue('Que1', XEnq_opts, XMsg_props, APayload, XMsg_id);
    commit;
    end;
    All rights are corectly sets under SYSTEM user.
    Please help somebody why it does not work. I not see problem byt I need to call some procedure when new message arive into que. I need to have paralel process, I can not solve it with loops and so on.
    Thanks for every ideas. In next week I can show you source code. Today I can not.
    Regards, Zdenek.
    For Oracle9i and above DBMS_AQ.REGISTER can be used for PL/SQL callback notification.
    If running earlier version of Oracle, use DBMS_AQ.LISTEN to get a similar functionality.
    It depends upon the URL that you specify while registering, that decides if callback will be executed or
    not.
    If you post the code fragment(s) of how you created the notification procedure and how you registered it,
    maybe we can look to see if it is correct.
    There have been a number of discussions on this forum about PL/SQL notifications. I'm running
    into the same problem:
    - create the notification procedure
    - register the procedure
    - enqueue a message
    - NOTHING HAPPENS
    It seems that the notification procedure is not executed.
    The discussions on this forum were all inconclusive. Did anybody get this to work? If yes, can
    you post an example (i.e. a script that creates the queue, create the procedure, registers the
    procedure and enqueues a message)?
    Is there a table/view that contains the registrations?
    What system parameters are relevant to this functionality?
    Where are errors in calling the notification procedure logged?
  • 4. re:PL/SQL notification / DBMS_AQ.REGISTER
    41226 Newbie
    Currently Being Moderated
    Kamal,

    Why should the payload parameter be of type VARCHAR2? The payload of the queue is RAW.
    Why should a subscriber be specified? The queue is a single consumer queue.

    Can you post a working example?

    Thanks, Erwin.
  • 5. re:PL/SQL notification / DBMS_AQ.REGISTER
    41226 Newbie
    Currently Being Moderated
    This is my code:

    declare
    l_reg_info_list sys.aq$_reg_info_list;
    begin
    l_reg_info_list := sys.aq$_reg_info_list(sys.aq$_reg_info('OAIAPP2.IN_QUEUE'
    , DBMS_AQ.NAMESPACE_AQ
    , 'pl/sql://oaiapp2.getstock'
    , hextoraw('FF')));
    dbms_aq.unregister(l_reg_info_list, 1);
    end;
    /

    create or replace
    procedure getstock
    ( context in raw
    , reginfo in sys.aq$_reg_info
    , descr in sys.aq$_descriptor
    , payload in raw
    , payloadl in number
    )
    is
    begin
    insert into log values ('notification received');
    commit;
    end getstock;
    /

    I also tried the registration with 'null' for the context (4th parameter) in the reg info
    object and '...?PR=0' in the call (2nd parameter). Same result: no record in log table.

    Erwin


  • 6. re:PL/SQL notification / DBMS_AQ.REGISTER
    41226 Newbie
    Currently Being Moderated
    Oops, copied the wrong script.
    It should ofcourse be 'dbms_aq.REGISTER' and not 'dbms_aq.UNregister'. Sorry.
    I still have the problem.

    Erwin
  • 7. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    Here is a complete working example. I personally ran this on my system and the LOG table gets the
    row inserted. I used the user AQADM to test this setup. You can change the user accordingly.

    begin
    dbms_aqadm.stop_queue(queue_name => 'IN_QUEUE') ;
    end ;
    /
    begin
    dbms_aqadm.drop_queue(queue_name => 'IN_QUEUE') ;
    end ;
    /
    begin
    dbms_aqadm.drop_queue_table(queue_table => 'IN_QUEUE_TABLE') ;
    end ;
    /
    begin
    dbms_aqadm.create_queue_table(queue_table => 'IN_QUEUE_TABLE',
    queue_payload_type => 'RAW',
    multiple_consumers => FALSE) ;
    end ;
    /
    begin
    dbms_aqadm.create_queue(queue_name => 'IN_QUEUE', queue_table => 'IN_QUEUE_TABLE') ;
    end ;
    /
    begin
    dbms_aqadm.start_queue(queue_name => 'IN_QUEUE', enqueue => TRUE, dequeue => TRUE) ;
    end ;
    /

    drop table log
    /
    create table log (text varchar2(2000))
    /

    create or replace procedure getstock
    ( context in raw
    , reginfo in sys.aq$_reg_info
    , descr in sys.aq$_descriptor
    , payload in RAW
    , payloadl in number
    )
    is
    begin
    insert into log values ('notification received');
    commit;
    end getstock;
    /

    declare
    l_reg_info_list sys.aq$_reg_info_list;
    begin
    l_reg_info_list := sys.aq$_reg_info_list(sys.aq$_reg_info('AQADM.IN_QUEUE'
    , DBMS_AQ.NAMESPACE_AQ
    , 'plsql://AQADM.GETSTOCK?PR=0'
    , hextoraw('FF')));
    dbms_aq.register(l_reg_info_list, 1);
    end;
    /
    declare
    msg RAW(500) ;
    enq_opts dbms_aq.enqueue_options_t ;
    msg_props dbms_aq.message_properties_t ;
    begin
    dbms_aq.enqueue(queue_name => 'IN_QUEUE', enqueue_options => enq_opts, message_properties => msg_props, payload => hextoraw('FF'), msgid => msg) ;
    commit ;
    end ;
    /
    exec dbms_lock.sleep(10) ;

    select * from log
    /

  • 8. re:PL/SQL notification / DBMS_AQ.REGISTER
    253424 Newbie
    Currently Being Moderated
    Hallo Kamal, Erwin.
    thanks very much for yours ideas and solutions. I have corrected my code (in discussion it wroten from hand) but no changes. Good, your code looks very simply, but on my system it does not work. No record in LOG table is stored.
    Messages are inserted into table "IN_QUEUE_TABLE". I can see them. It looks with out errors.
    I am reading manuals, documentations one more time, but no informations about setting the AQ I found.
    Everything what I did is:
    (our administrators instaled oracle with releases on the Sun), under the System user I grant to my profile all needed rights and roles like this (I am the User1):

    connect system/password@instance1;
    grant aq_administrator_role to user1;
    grant connect, resource to user1;
    grant execute on dbms_aqadm to user1;
    grant execute on dbms_aq to user1;

    After this steps I am creating que tables and ques from yur sample. I suppose, taht it is all what I need and can to do on Oracle to work with AQ with notifications.

    Maybe I have got incorrectly installed my oracle server on Sun.
    Every code on them works good. Only AQ does not call registered notifications.
    In this moment I do not know what is wrong.

    My version is: select * from v$version
    BANNER:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    PL/SQL Release 9.2.0.1.0 - Production
    CORE     9.2.0.1.0     Production
    TNS for Solaris: Version 9.2.0.1.0 - Production
    NLSRTL Version 9.2.0.1.0 - Production

    Do you have some idea if is needed to processing notifications out of registering notification procedures?
    I need to use then notifications for starting paralel processing. I can not do it in cycle.
    Thank you for your time and more ideas, what I can to do to success.
    Zdenek.
  • 9. re:PL/SQL notification / DBMS_AQ.REGISTER
    253424 Newbie
    Currently Being Moderated
    Hallo,
    yesterday I try to test our sample and my code on another Oracle server on WinXP. It WORKS perfectly. The notifications calls registered callbacks.
    It is very interesting. Now I will compare instalations on our Sun server with instalation on WinXP. Somethinh must be diferent.
    I will let you know what I will find.
    Regrads Zdenek.
  • 10. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    it might be worth checking the init.ora parameters required for Advanced Queuing to work properly.
    These might not have been set correctly.
  • 11. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    it might be worth checking init.ora parameters requred for Advanced Queuing to work properly.
    these might not have been set correctly.
  • 12. re:PL/SQL notification / DBMS_AQ.REGISTER
    253424 Newbie
    Currently Being Moderated
    Hi,
    in AQ manual I found following link on OTN:
    http://otn.oracle.com/doc/server.815/a68005/03_adq3c.htm#56429
    where is description of parameters in init.ora for AQ and it is:
    JOB_QUEUE_INTERVAL
    JOB_QUEUE_PROCESSES
    and
    AQ_TM_PROCESSES

    These params I have updated to JOB_QUEUE_INTERVAL=60, JOB_QUEUE_PROCESSES=2, AQ_TM_PROCESSES=2.
    Propagations between diferent instances works. Notifications do not work.
    I will let you know, what I will find.
    Thanks and regrads,
    Zdenek
  • 13. re:PL/SQL notification / DBMS_AQ.REGISTER
    27876 Newbie
    Currently Being Moderated
    On a side note, why don't you use DBMS_AQ.LISTEN procedure call to get a similar functionality?
    If your callback setup is not working, this could be an alternative to the callback setup.
  • 14. re:PL/SQL notification / DBMS_AQ.REGISTER
    253424 Newbie
    Currently Being Moderated
    Hallo,
    method LISTEN I can not use, because when I will get information that message arives into a que, I need to start another process like match of a jobs.
    When I will use the LISTEN method, serial processing of ques I will have. An application, for which I am developing the AQ support will be processing a lot of request.

    In this moment when me and our admins do not know what is wrong, my company is contacting oracle support to get help and solution.

    On which system you are runing the oracle?
    Regrads, Zdenek.
1 2 Previous Next