This discussion is archived
4 Replies Latest reply: Dec 27, 2012 3:38 AM by 758358 RSS

Error in procedure

970843 Newbie
Currently Being Moderated
Hi,

Below is my procedure to enqueue data in a queue, while running the procedure i am getting compilation errors, i cant find where i went wrong.please help me with solution.

CREATE OR REPLACE PROCEDURE p_enqueue(msg IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
BEGIN
dbms_aq.enqueue( queue_name => 'example_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message_type(msg),
msgid => message_handle);
COMMIT;
END;





ERRORS:


PLS-00201: identifier 'DBMS_AQ' must be declared

PLS-00320: the declaration of the type of this expression is incomplete or malformed
  • 1. Re: Error in procedure
    881508 Journeyer
    Currently Being Moderated
    Tye using SYS.DBMS_AQ rather than just DBMS_AQ. If it still fails the user for which you are trying to execute the procedure does not have enough privileges to access DBMS_AQ package.

    As SYS try granting execute privilege to your user.

    GRANT EXECUTE ON dbms_aq TO <UserName>;
  • 2. Re: Error in procedure
    970843 Newbie
    Currently Being Moderated
    when i try to use grant privilage, i am getting following error

    ERROR
    ORA-01031: insufficient privileges

    If that is the problem i ran the below pl/sql block to enqueue message, the procedure got successfully created.How is it possible if the privilage is not there?

    DECLARE
    enqueue_options dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle RAW(16);
    message message_typ;

    BEGIN
    message := message_typ('NORMAL MESSAGE',
    'enqueued to msg_queue first.');

    dbms_aq.enqueue(queue_name => 'msg_queue',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);

    COMMIT;
    end;
  • 3. Re: Error in procedure
    970843 Newbie
    Currently Being Moderated
    After granting privilage also i am getting this error

    PLS-00201: identifier 'DBMS_AQ' must be declared
  • 4. Re: Error in procedure
    758358 Pro
    Currently Being Moderated
    Hi,

    The privileges required to compile a procedure which executes something in another schema are different to those required just to execute it in an anonymous block, the reason you can execute DBMS_AQ through an anonymous block (DECLARE;BEGIN;END;) is because you have the execute privilege through a role - this is no use for compiling stored programs - see MOS note: Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus [ID 168168.1]

    As the previous user states you need to do a direct grant as SYSDBA to the user who wants to compile the procedure.
    conn / as sysdba
    grant execute on dbms_aq to <user>;
    You must do this as the SYS user otherwise you will get:
    ERROR
    ORA-01031: insufficient privileges

    HTH
    Paul

Legend

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