This discussion is archived
3 Replies Latest reply: Jul 23, 2012 8:13 AM by 948783 RSS

Unable to create a QUEUE in custom schema

user309530 - oracle Newbie
Currently Being Moderated
Hi,
I was able to create a Type & queue table in custom schema, but using them was unable to create a QUEUE in custom schema
I'm using the following piece

DBMS_AQADM.CREATE_QUEUE(queue_name => 'XXXXXX_QUEUE',
queue_table => 'XXXXXX_QUEUE_TABLE'
);

Whereas in APPS schema I'm able to create the same queue.

Pls advice ...
  • 1. Re: Unable to create a QUEUE in custom schema
    AlejandroSosa Journeyer
    Currently Being Moderated
    Are you sure you need/want to have this queue out of reach of the APPS schema? What for?

    Please tell what the error is as well as provide details of the queue table and type creation statements.

    Regards,

    Alejandro
  • 2. Re: Unable to create a QUEUE in custom schema
    user309530 - oracle Newbie
    Currently Being Moderated
    Hi,
    The only reason is that, we create all the custom objects (owner as custom schema), and a synonym in APPS.
    I did not get any error, but the queue, is not getting created

    Following are the scripts used

    ------
    CREATE OR REPLACE TYPE 'XXXXXX_QUEUE_TYPE
    AS OBJECT (TYPE VARCHAR2(15));
    ------

    BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'XXXXXX_QUEUE_TABLE',
    queue_payload_type => 'XXXXXX_QUEUE_TYPE'
    );
    END;
    ------


    BEGIN
    DBMS_AQADM.CREATE_QUEUE(queue_name => 'XXXXXX_QUEUE',
    queue_table => 'XXXXXX_QUEUE_TABLE'
    );
    END;
    ------


    BEGIN
    DBMS_AQADM.START_QUEUE(queue_name => 'XXXXXX_QUEUE'
    );
    END;
    ------


    Thanks.....
  • 3. Re: Unable to create a QUEUE in custom schema
    948783 Newbie
    Currently Being Moderated
    Hi,
    Main problem with custom schema is Synonyms & Grants. The custom schema should have all the grants & synonyms which are accessed by DBMS_AQADM defined.
    If one synonym is missing the package will error out.If the create_queue() procedure has any error message out parameter defined.Try to print that message and see what its displaying.

    If synonyms & grants is the cause of error then create trace file ,see what tables its accessing and try to create synonyms & grants in custom schema.It will work in some cases if package is not accessing too many tables or other packages

Legend

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