Home » Server Options » Streams & AQ » PLS-00201: identifier 'SYS.DBMS_AQ' must be declared
PLS-00201: identifier 'SYS.DBMS_AQ' must be declared [message #43018] Mon, 02 June 2003 13:41 Go to next message
Einstein
Messages: 2
Registered: June 2003
Junior Member
Hi,
I am using Oracle Advanced Queues and planning to use a stored procedure to Enqueue and dequeue messages from a queue.
When I create the stored procedure,see code below:
CREATE OR REPLACE PROCEDURE TEST_ENQUEUE
AS
v_Message MessageObj;
v_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
v_dequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
v_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_MsgID RAW(16);
e_QTimeOut EXCEPTION;
PRAGMA EXCEPTION_INIT(e_QTimeOut,-25228);
BEGIN
NULL;
END TEST_ENQUEUE;

I get an Error message saying :
PLS-00201: identifier 'SYS.DBMS_AQ' must be declared

The Funny thing is, if I code using a simple DECLARE statement ( Not creating Stored procedure) it works fine....like this one works fine:

DECLARE
v_Message MessageObj;
v_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
v_dequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
v_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_MsgID RAW(16);
e_QTimeOut EXCEPTION;
PRAGMA EXCEPTION_INIT(e_QTimeOut,-25228);
BEGIN
NULL;
END TEST_ENQUEUE;

So I am not sure if this is related to having the right Privilege or not??

Thanks,
Einsu
Re: PLS-00201: identifier 'SYS.DBMS_AQ' must be declared [message #43021 is a reply to message #43018] Mon, 02 June 2003 15:49 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You have the execute privilege on DBMS_AQ through the AQ_USER_ROLE (which allows the anonymous block to run), but to compile stored procedures/functions, you need to have a direct grant.

SYS>grant execute on DBMS_AQ to my_username;


Why?

Previous Topic: How to configure Streams?!
Next Topic: Oracle Streams
Goto Forum:
  


Current Time: Tue Oct 20 21:08:32 CDT 2020