5 Replies Latest reply: Jan 24, 2013 12:49 PM by Doug Chase RSS

    ORA-44004 on call to Callback Procedure

    Doug Chase
      Hello, I'm looking for some help to which I've searched and can not find any answers.

      Database version is 11.2.0.3.3

      I have a AQ user who owns the message queue and another user who owns the enqueue and callback procedures. The set up needs to be this way. Enqueue works fine -- I can see the message in the queue table. However, the callback procedure never appears to execute and when I look at the trace file generated from the scheduler job, I see this:

      *** 2013-01-22 10:07:03.744
      *** SESSION ID:(879.13677) 2013-01-22 10:07:03.744
      *** CLIENT ID:() 2013-01-22 10:07:03.744
      *** SERVICE NAME:(SYS$USERS) 2013-01-22 10:07:03.744
      *** MODULE NAME:(DBMS_SCHEDULER) 2013-01-22 10:07:03.744
      *** ACTION NAME:(AQ$_PLSQL_NTFN_1358870823) 2013-01-22 10:07:03.744

      : Exception Occured, Error msg:
      ORA-44004: invalid qualified SQL name
      : Exception Occured, Error msg:
      ORA-44004: invalid qualified SQL name

      I have run everything through the dbms_assert.qualified_sql_name procedure without any errors so I'm not sure where this comes from. Oracle Support's knowledge base does not seem to be any help to me.

      As AQ user:

      BEGIN
      DBMS_AQADM.ADD_SUBSCRIBER (
      queue_name => 'gb_aq_msg_queue',
      subscriber => SYS.AQ$_AGENT(
      'queue_subscriber',
      NULL,
      NULL )
      );
      END;
      /


      declare
      reginfo sys.aq$_reg_info;
      reg_list sys.aq$_reg_info_list;
      begin
      reginfo := sys.aq$_reg_info(name => 'GB_AQ_MSG_QUEUE:QUEUE_SUBSCRIBER',
      namespace => dbms_aq.namespace_aq,
      callback => 'plsql://office2007.simgradebook.dequeueGradeColumn',
      context => HEXTORAW('FF'));
      reg_list := sys.aq$_reg_info_list(reginfo);
      dbms_aq.register(reg_list => reg_list,
      reg_count => 1);
      end;
      /

      I'm pulling my hair out on this one and I don't have much to start with

      Thanks!
      Doug

      ~
        • 1. Re: ORA-44004 on call to Callback Procedure
          phcullen-Oracle
          Hello,

          As per the documentation

          "For raw message payload for the PLSQLCALLBACK procedure, use plsql://schema.procedure?PR=0. For user-defined type message payload converted to XML for the PLSQLCALLBACK procedure, use plsql://schema.procedure?PR=1"

          So I would suspect the problem is

          plsql://office2007.simgradebook.dequeueGradeColumn

          So does what you have specified correspond with what should be supplied?

          Thanks
          Peter
          • 2. Re: ORA-44004 on call to Callback Procedure
            Doug Chase
            Hi, thanks for your reply...

            My looking into the matter prior to posting here was taking me down a different road, but per your advice, I added ?PR=0 to the end of the plsql string and re-ran it to no effect --- one way or the other. The results were exactly the same. I've left ?PR=0 in there as it should be.

            Doug
            • 3. Re: ORA-44004 on call to Callback Procedure
              phcullen-Oracle
              Hello,

              Could you post the code for the callback procedure?

              Thanks
              Peter
              • 4. Re: ORA-44004 on call to Callback Procedure
                Doug Chase
                I've scaled this way down and am just trying to make this as basic and simple as I can and I'm still getting the same errors.

                I updated my callback procedure below

                BEGIN
                DBMS_AQADM.ADD_SUBSCRIBER (
                queue_name => 'gb_aq_msg_queue',
                subscriber => SYS.AQ$_AGENT(
                'queue_subscriber',
                NULL,
                NULL )
                );
                END;
                /

                BEGIN
                DBMS_AQ.REGISTER (
                SYS.AQ$_REG_INFO_LIST(
                SYS.AQ$_REG_INFO(
                'GB_AQ_MSG_QUEUE:QUEUE_SUBSCRIBER',
                DBMS_AQ.NAMESPACE_AQ,
                'plsql://AQCallback?PR=0',
                HEXTORAW('FF')
                )
                ),
                1
                );
                END;
                /

                CREATE OR REPLACE PROCEDURE Enqueue_msg(pNewColumnID IN INTEGER, pColumnType IN INTEGER, pCourseID IN INTEGER, pCommit IN INTEGER)
                AS
                v_enqopt sys.dbms_aq.enqueue_options_t;
                v_msgprop sys.dbms_aq.message_properties_t;
                v_enq_msgid raw(16);
                v_enq_msg gb_aq_msg_otype;
                v_ct integer;
                begin
                v_enq_msg := gb_aq_msg_otype(pNewColumnID, pColumnType, pCourseID, pCommit);
                --
                sys.dbms_aq.enqueue (
                queue_name => 'gb_aq_msg_queue'
                ,enqueue_options => v_enqopt
                ,message_properties => v_msgprop
                ,payload => v_enq_msg
                ,msgid => v_enq_msgid);
                --
                commit;
                END Enqueue_msg;
                /


                create or replace PROCEDURE AQCallback(context RAW,
                reginfo sys.AQ$_REG_INFO,
                descr sys.AQ$_DESCRIPTOR,
                payload RAW,
                payloadl NUMBER)
                AS
                v_deqopt dbms_aq.dequeue_options_t;
                v_msgprop dbms_aq.message_properties_t;
                v_msg_handle raw(16);
                v_msg gb_aq_msg_otype;
                BEGIN
                v_deqopt.msgid := descr.msg_id;
                v_deqopt.consumer_name := descr.consumer_name;
                DBMS_AQ.DEQUEUE(
                queue_name => descr.queue_name,
                dequeue_options => v_deqopt,
                message_properties => v_msgprop,
                payload => v_msg,
                msgid => v_msg_handle
                );
                commit;
                END AQCallback;
                /

                Thanks!
                • 5. Re: ORA-44004 on call to Callback Procedure
                  Doug Chase
                  After a lot more testing -- it appears this problem only exists on our Staging database. It works fine in our QA and Production databases. So thanks to you who replied.