4 Replies Latest reply: Dec 27, 2012 5:38 AM by 758358 RSS

    Error in procedure

    970843
      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
          Nadeem M
          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
            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
              After granting privilage also i am getting this error

              PLS-00201: identifier 'DBMS_AQ' must be declared
              • 4. Re: Error in procedure
                758358
                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