How to make Oracle execute callback procedure not by SYS but by the owner of the procedure

300 Views Asked by At

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?

0

There are 0 best solutions below