1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 3:20 PM by spur230 RSS

    PL/SQL notification / DBMS_AQ.REGISTER

    41226
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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