Error in creating procedure

Question

Possible Duplicate:
PL/SQL function in Oracle cannot see DBMS_AQ

Below is my procedure to enqueue data in a queue, while running the procedure I am getting compilation errors, I can't 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

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;

see if you've granted privileges as mentioned in the above comment — Sathyajith Bhat, Dec 26 '12 at 11:25

score 0 · Answer 1 · edited May 23 '17 at 12:18

0

The error message says that DBMS_AQ is not know. Since this is an Oracle package owned by SYS, it does exist. So you're missing the right to see and execute it.

Have you run

grant EXECUTE ON DBMS_AQ to appuser;

as explicitly shown in my answer to one of your earlier questions?

edited May 23 '17 at 12:18

Community

1
1

answered Dec 26 '12 at 11:25

Codo

75,595
17
168
206

I have edited my post, please check and give me the solution. – user1 Dec 27 '12 at 04:30
I am wondering after granting privilage also i am getting this error PLS-00201: identifier 'DBMS_AQ' must be declared – user1 Dec 27 '12 at 06:34
The GRANT command must be run as SYS, which has sufficient privileges. If you still get `identifier 'DBMS_AQ' must be declared`, you haven't granted the privilege yet (or to the wrong user). – Codo Dec 27 '12 at 06:36
Ok thankyou the problem got resolved, I have one more query, i am having an input parameter in the procedure i have created how to pass input to the arguments.I used "set_job_argument_value" but its throwing " invalid value 1 for attribute ARGUMENT_POSITION" – user1 Dec 27 '12 at 08:19

score 0 · Answer 2 · answered Dec 26 '12 at 12:16

check the DBMS_AQ pkg and find out which are the mandatory parameters for the procedure which u have used.

below queries 'll be useful for Queue's.

SELECT name, enqueue_enabled, dequeue_enabled
FROM user_queues;

SELECT owner, queue_name, queue_table, consumer_name
FROM dba_queue_subscribers;

SELECT queue_name, consumer_name, address, protocol, delivery_mode, queue_to_queue
FROM user_queue_subscribers;

SELECT qname, destination, start_date, start_time, propagation_window, next_time, latency
FROM user_queue_schedules;

SELECT qname, process_name, session_id, instance, last_run_date, last_run_time, current_start_date
FROM user_queue_schedules;

SELECT qname, current_start_time, next_run_date, next_run_time, total_time, total_number
FROM user_queue_schedules;

SELECT qname, total_bytes, max_number, max_bytes, avg_number, avg_size, avg_time
FROM user_queue_schedules;

Error in creating procedure

2 Answers2