Discussion:
DBMS_AQ
(too old to reply)
jyu
2005-02-10 15:00:05 UTC
Permalink
I am trying some sample about oracle AQ. But I run into some trouble.
When I try to create a procedure in SQLPLUS using the following script:

CREATE OR REPLACE PROCEDURE New_enqueue(
Queue_name IN VARCHAR2,
Payload IN RAW ,
Correlation IN VARCHAR2 := NULL,
Exception_queue IN VARCHAR2 := NULL)
AS

Enq_ct DBMS_AQ.Enqueue_options_t;
Msg_prop DBMS_AQ.Message_properties_t;
Enq_msgid RAW(16);
Userdata RAW(1000);

BEGIN
Msg_prop.Exception_queue := Exception_queue;
Msg_prop.Correlation := Correlation;
Userdata := Payload;

DBMS_AQ.ENQUEUE(Queue_name, Enq_ct, Msg_prop, Userdata, Enq_msgid);
END;
/

It gave me these errors:

8/12 PLS-00201: identifier 'DBMS_AQ' must be declared
8/12 PL/SQL: Item ignored
9/12 PLS-00201: identifier 'DBMS_AQ' must be declared
9/12 PL/SQL: Item ignored
14/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

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

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


How can I resolve it? Thanks in advance.
f***@cox.net
2005-02-10 15:47:27 UTC
Permalink
Post by jyu
I am trying some sample about oracle AQ. But I run into some trouble.
CREATE OR REPLACE PROCEDURE New_enqueue(
Queue_name IN VARCHAR2,
Payload IN RAW ,
Correlation IN VARCHAR2 := NULL,
Exception_queue IN VARCHAR2 := NULL)
AS
Enq_ct DBMS_AQ.Enqueue_options_t;
Msg_prop DBMS_AQ.Message_properties_t;
Enq_msgid RAW(16);
Userdata RAW(1000);
BEGIN
Msg_prop.Exception_queue := Exception_queue;
Msg_prop.Correlation := Correlation;
Userdata := Payload;
DBMS_AQ.ENQUEUE(Queue_name, Enq_ct, Msg_prop, Userdata, Enq_msgid);
END;
/
8/12 PLS-00201: identifier 'DBMS_AQ' must be declared
8/12 PL/SQL: Item ignored
9/12 PLS-00201: identifier 'DBMS_AQ' must be declared
9/12 PL/SQL: Item ignored
14/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
14/4 PL/SQL: Statement ignored
15/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
15/4 PL/SQL: Statement ignored
18/1 PL/SQL: Statement ignored
18/29 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
How can I resolve it? Thanks in advance.
The user account attempting to create this may not have execute
permission on the DBMS_AQ package, and likely there is no public
synonym in place.

I ran this on my system (having a public synonym and execute
permission) and created the procedure without error.


David Fitzjarrell
jyu
2005-02-10 16:35:07 UTC
Permalink
It works. Thanks.

Rauf Sarwar
2005-02-10 16:13:42 UTC
Permalink
Post by jyu
I am trying some sample about oracle AQ. But I run into some trouble.
CREATE OR REPLACE PROCEDURE New_enqueue(
Queue_name IN VARCHAR2,
Payload IN RAW ,
Correlation IN VARCHAR2 := NULL,
Exception_queue IN VARCHAR2 := NULL)
AS
Enq_ct DBMS_AQ.Enqueue_options_t;
Msg_prop DBMS_AQ.Message_properties_t;
Enq_msgid RAW(16);
Userdata RAW(1000);
BEGIN
Msg_prop.Exception_queue := Exception_queue;
Msg_prop.Correlation := Correlation;
Userdata := Payload;
DBMS_AQ.ENQUEUE(Queue_name, Enq_ct, Msg_prop, Userdata, Enq_msgid);
END;
/
8/12 PLS-00201: identifier 'DBMS_AQ' must be declared
8/12 PL/SQL: Item ignored
9/12 PLS-00201: identifier 'DBMS_AQ' must be declared
9/12 PL/SQL: Item ignored
14/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
14/4 PL/SQL: Statement ignored
15/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
15/4 PL/SQL: Statement ignored
18/1 PL/SQL: Statement ignored
18/29 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
How can I resolve it? Thanks in advance.
- What's your Oracle version?
- DBMS_AQ package is installed in the database during database creation
by running ORACLE_HOME/rdbms/admin/catproc.sql script. Oracle creates a
public synonym dbms_aq so you should be able to access it.
- Login as the user creating the proc from sqlplus and do,
SQL> desc dbms_aq
If you get "object dbms_aq does not exist" then contact your DBA.

Regards
/Rauf
Loading...