OraFAQ Forum: Streams & AQ » PLS-00201: identifier 'SYS.DBMS

Home » Server Options » Streams & AQ » PLS-00201: identifier 'SYS.DBMS_AQ' must be declared

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00201: identifier 'SYS.DBMS_AQ' must be declared [message #43018]

Mon, 02 June 2003 13:41

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

Report message to a moderator

Re: PLS-00201: identifier 'SYS.DBMS_AQ' must be declared [message #43021 is a reply to message #43018]

Mon, 02 June 2003 15:49

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?

Grants and PL/SQL stored code

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 02:02:40 CST 2025