This discussion is archived
5 Replies Latest reply: Jan 24, 2013 10:49 AM by Doug Chase RSS

ORA-44004 on call to Callback Procedure

Doug Chase Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Hello,

    Could you post the code for the callback procedure?

    Thanks
    Peter
  • 4. Re: ORA-44004 on call to Callback Procedure
    Doug Chase Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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