5 Replies Latest reply on Dec 18, 2006 9:29 PM by 439999

    identifier 'DBMS_AQ' must be declared

    458914
      Hi, Can somebody help me solving this problem. I am trying to create a procedure to enqueue the message, but gives the following error. Also aquser is unable to view the Types in DBMS_AQ package, but is able to view the procedures. Not sure if the privileges problem. Your help will be much appreciated.

      Cheers
      Naveen
      ------- -----------------------------------------------------------------
      4/19 PLS-00201: identifier 'DBMS_AQ' must be declared
      4/19 PL/SQL: Item ignored
      5/22 PLS-00201: identifier 'DBMS_AQ' must be declared
      5/22 PL/SQL: Item ignored
      7/11 PLS-00201: identifier 'PABSE_AQADMIN.MESSAGE_TYP' must be
      declared

      7/11 PL/SQL: Item ignored
      8/14 PLS-00201: identifier 'DBMS_AQ' must be declared
      8/14 PL/SQL: Item ignored
      2/3 PLS-00320: the declaration of the type of this expression is

      INE/COL ERROR
      ------- -----------------------------------------------------------------
      incomplete or malformed

      2/3 PL/SQL: Statement ignored
      3/3 PLS-00320: the declaration of the type of this expression is
      incomplete or malformed

      3/3 PL/SQL: Statement ignored
      4/3 PLS-00320: the declaration of the type of this expression is
      incomplete or malformed

      4/3 PL/SQL: Statement ignored

      INE/COL ERROR
      ------- -----------------------------------------------------------------
      5/3 PL/SQL: Statement ignored
      6/35 PLS-00320: the declaration of the type of this expression is
      incomplete or malformed
        • 1. Re: identifier 'DBMS_AQ' must be declared
          Avi Abrami
          Naveen,
          Maybe if you mentioned the Oracle version you are using, the platform you are running on, and the code that is causing the problems -- I may be able to help you.

          It seems that you are saying that you have access to part of the DBMS_AQ package. Pardon me, but I don't think that's possible. It really appears that the user that you are trying to run your PL/SQL as, does not have access to the DBMS_AQ package.

          Good Luck,
          Avi.
          • 2. Re: identifier 'DBMS_AQ' must be declared
            sanju2
            Hi Naveen,
            You should grant the privs. mentioned below.

            GRANT EXECUTE ON dbms_aq TO <UserName>;

            Note : <UserName> can be your Schema name.

            also you will require all these grants and privs. They are for AQ USER and AQ ADMIN.

            GRANT RESOURCE TO <UserName>;
            GRANT CONNECT TO <UserName>;
            GRANT EXECUTE ANY PROCEDURE TO <UserName>;
            GRANT aq_administrator_role TO <UserName>;
            GRANT aq_user_role TO <UserName>;
            GRANT EXECUTE ON dbms_aqadm TO <UserName>;
            GRANT EXECUTE ON dbms_aq TO <UserName>;
            GRANT EXECUTE ON dbms_aqin TO <UserName>;

            Cheers,
            Sanjeev.

            Message was edited by:
            user453374
            • 3. Re: identifier 'DBMS_AQ' must be declared
              439999
              Hello,

              I'm trying to create a queue table and had the same problem getting the error message:

              identifier 'DBMS_AQADM' must be declared

              I am following the above post to grant the privs needed to create the queue table.

              I am having a problem with the following three grants:

              GRANT EXECUTE ON dbms_aqadm TO <UserName>;
              GRANT EXECUTE ON dbms_aq TO <UserName>;
              GRANT EXECUTE ON dbms_aqin TO <UserName>;

              When try these grants I get the error message:

              ORA-01031: insufficient privileges. I'm logged into sqlplus using my admin account so I'm not sure what the problem is. Any ideas?

              Thanks!

              - Brian
              • 4. Re: identifier 'DBMS_AQ' must be declared
                439999
                Scratch the above post, just figured out what my problem was
                • 5. Re: identifier 'DBMS_AQ' must be declared
                  439999
                  OK, I granted all of the above privs successfully, but now

                  So now I am trying to run the following code:
                  BEGIN
                   DBMS_AQADM.CREATE_QUEUE_TABLE
                        (queue_table => 'test_queue',
                         queue_payload_type => 'RAW');
                  END;
                  and getting this error:

                  ORA-01950: no privileges on tablespace 'USERS'

                  This doesn't make sense since I ran the 'GRANT RESOURCE'. But just incase I ran:

                  alter user <user_name> quota unlimited on users;

                  I still get the same error and I'm not sure why.

                  Thanks

                  - Brian