I'm new at Oracle Advanced Queueing. I have a process that enqueues, and a callback procedure that subscried and registred for items in that queue. All these objects where created in user schema. But when item appears in the queue the callback procedure fires but not by the user of the schema but by the user SYS. What settings should by made to make Oracle to execute the callback procedure not by user SYS but by the user-the owner of the schema where the callback procedure was created
I used the example from here http://innerlife.io/ru/oracle-db-advanced-queuing-subscriber-2/
CREATE OR REPLACE TYPE tp_queue AS OBJECT (n NUMBER);
BEGIN
DBMS_AQADM.create_queue_table ('tq_test',
'tp_queue',
multiple_consumers => TRUE);
END;
BEGIN
DBMS_AQADM.create_queue ('q_test', 'tq_test');
END;
BEGIN
DBMS_AQADM.start_queue ('q_test');
END;
CREATE TABLE t_log
(
d_add TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
s_msg VARCHAR2 (4000)
);
CREATE OR REPLACE PACKAGE pkg_queue
IS
PROCEDURE addLog (pi_msg IN t_log.s_msg%TYPE);
PROCEDURE subscriber (context RAW,
reginfo SYS.AQ$_REG_INFO,
descr SYS.AQ$_DESCRIPTOR,
payload RAW,
payloadl NUMBER);
END pkg_queue;
CREATE OR REPLACE PACKAGE BODY pkg_queue
IS
PROCEDURE addLog (pi_msg IN t_log.s_msg%TYPE)
AS
BEGIN
INSERT INTO t_log (s_msg)
VALUES (pi_msg);
END;
PROCEDURE subscriber (context RAW,
reginfo SYS.AQ$_REG_INFO,
descr SYS.AQ$_DESCRIPTOR,
payload RAW,
payloadl NUMBER)
AS
l_tr_obj tp_queue;
l_msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
l_queue_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
l_msg_id RAW (16);
BEGIN
l_queue_opts.consumer_name := descr.consumer_name;
l_queue_opts.msgid := descr.msg_id;
DBMS_AQ.dequeue (descr.queue_name,
l_queue_opts,
l_msg_props,
l_tr_obj,
l_msg_id);
addLog (USER);
EXCEPTION
WHEN OTHERS
THEN
addLog (SQLERRM);
END;
END pkg_queue;
BEGIN
DBMS_AQADM.add_subscriber ('q_test',
sys.AQ$_AGENT ('test_subscriber', NULL, NULL));
DBMS_AQ.register (sys.AQ$_REG_INFO_LIST (sys.AQ$_REG_INFO (
'q_test:test_subscriber',
DBMS_AQ.namespace_aq,
'plsql://pkg_queue.subscriber',
HEXTORAW ('FF'))),
1);
END;
DECLARE
l_msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
l_queue_opts DBMS_AQ.ENQUEUE_OPTIONS_T;
l_msg_id RAW (16);
BEGIN
DBMS_AQ.enqueue ('q_test',
l_queue_opts,
l_msg_props,
tp_queue (1),
l_msg_id);
COMMIT;
END;
The query from t_log table returns "SYS". Thus, the callback procedure was fired by user SYS. But I expected to see the user - the owner of the schema where these objects where created. It is not SYS. How to achieve this?