[SOLVED] PLS-00201: identifier ‘DBMS_AQ’ must be declared

This is a solution for an exception occurred during enqueuing/dequeuing or managing Oracle AQ in PL/SQL.

Problem
Unable to execute statement like below.

DECLARE
    v_queue_options        dbms_aq.enqueue_options_t;
    v_message_properties   dbms_aq.message_properties_t;
    v_message_id           RAW(16);
    v_message              sys.xmltype;
BEGIN
    v_message := sys.xmltype.createxml('<foo>hello world</foo>');
    dbms_aq.enqueue(
        queue_name           => 'q_test_queue',
        enqueue_options      => v_queue_options,
        message_properties   => v_message_properties,
        payload              => v_message,
        msgid                => v_message_id
    );

    COMMIT;
END;

because of exception:

PLS-00201: identifier 'DBMS_AQ' must be declared
ORA-06550: line 2, column 28:

 

Cause
User has no permission to DBMS_AQ package.

 

Solution A
Grant one of roles to user and restart user connection.

GRANT aq_administrator_role TO <user_name>;
GRANT aq_user_role TO <user_name>;

You can revoke unnecessary roles by:

REVOKE aq_administrator_role FROM <user_name>;
REVOKE aq_user_role FROM <user_name>;

 

Solution B (Not recommended)
Grant execute on DBMS_AQ package and restart user connection.

GRANT execute on DBMS_AQ to <user_name>;

In some cases you have to add sys. prexix to packages

DECLARE
    v_queue_options        sys.dbms_aq.enqueue_options_t;
    v_message_properties   sys.dbms_aq.message_properties_t;
    v_message_id           RAW(16);
    v_message              sys.xmltype;
BEGIN
    v_message := sys.xmltype.createxml('<foo>hello world</foo>');
    sys.dbms_aq.enqueue(
        queue_name           => 'q_test_queue',
        enqueue_options      => v_queue_options,
        message_properties   => v_message_properties,
        payload              => v_message,
        msgid                => v_message_id
    );

    COMMIT;
END;

Leave a Reply

avatar
  Subscribe  
Notify of
Close Menu