Thanks for the question, NOT_FOUND.
Asked: August 02, 2016 - 1:06 pm UTC
Last updated: August 03, 2016 - 1:09 pm UTC
Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Viewed 1000+ times
You Asked
I have a queue and i register a PL/SQL - Routine fpr this QUEUE.
When the routine is executed it will run under user SYS.
I need that the procedure run under my user (e.g. ICIS_SERVICES).
How i can reach this?
Here my code example:
=================================================
-- create a test table
CREATE TABLE test_table (test VARCHAR2(4000))
/
-- create a test procedure
CREATE OR REPLACE PROCEDURE test_procedure(context RAW, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor, payload RAW,payloadl NUMBER)
AS
dequeue_options DBMS_AQ.dequeue_options_t;
message_prop DBMS_AQ.message_properties_t;
message_hdl raw(16);
v_payload SYS.AQ$_JMS_TEXT_MESSAGE;
v_param_in VARCHAR2(4000);
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(descr.queue_name, dequeue_options, message_prop, v_payload, message_hdl);
v_payload.get_text(v_param_in);
INSERT INTO test_table (test) VALUES (v_param_in || ' (insert by user = <' || USER || '>)');
COMMIT;
END test_procedure;
/
-- create Queue-Tab and Queue, start the queue, register procedure, enqueue an entrie
DECLARE
reginfo1 sys.aq$_reg_info;
reginfolist sys.aq$_reg_info_list;
payload SYS.AQ$_JMS_TEXT_MESSAGE;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid raw(16);
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(Queue_table => 'ICIS_SERVICES.TEST_QUEUT', Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE', multiple_consumers => FALSE);
DBMS_AQADM.CREATE_QUEUE(Queue_name => 'ICIS_SERVICES.TEST_QUEUE', Queue_table => 'ICIS_SERVICES.TEST_QUEUT');
DBMS_AQADM.START_QUEUE(queue_name => 'ICIS_SERVICES.TEST_QUEUE' );
reginfo1 := sys.aq$_reg_info('TEST_QUEUE', DBMS_AQ.NAMESPACE_AQ, 'plsql://test_procedure?PR=0', HEXTORAW('FF'));
reginfolist := sys.aq$_reg_info_list(reginfo1);
sys.dbms_aq.register(reginfolist, 1);
COMMIT;
dbms_lock.sleep(2);
payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
payload.set_text('Test Test Test');
dbms_aq.enqueue(queue_name => 'ICIS_SERVICES.TEST_QUEUE', enqueue_options => enqueue_options, message_properties => message_properties, payload => payload, msgid => msgid);
COMMIT;
dbms_lock.sleep(2);
END;
/
SELECT * from test_table
/
=================================================
The output of the select is:
TEST
--------------------------------------------------------------------------------
Test Test Test (insert by user = <SYS>)
but i need:
TEST
--------------------------------------------------------------------------------
Test Test Test (insert by user = <ICIS_SERVICES>)
and Chris said...
Oracle Database runs the AQ notification dequeue process as scheduler job under SYS. You can access the schema the dequeue registration is in with the CURRENT_SCHEMA context:
CREATE TABLE test_table (test VARCHAR2(4000))
/
-- create a test procedure
CREATE OR REPLACE PROCEDURE test_procedure(
context RAW, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor, payload RAW,payloadl NUMBER)
AS
dequeue_options DBMS_AQ.dequeue_options_t;
message_prop DBMS_AQ.message_properties_t;
message_hdl raw(16);
v_payload SYS.AQ$_JMS_TEXT_MESSAGE;
v_param_in VARCHAR2(4000);
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(descr.queue_name, dequeue_options, message_prop, v_payload, message_hdl);
v_payload.get_text(v_param_in);
INSERT INTO test_table (test) VALUES (v_param_in ||
' (insert by user = <' || USER || ',' || SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') || '>)');
COMMIT;
END test_procedure;
/
-- create Queue-Tab and Queue, start the queue, register procedure, enqueue an entrie
DECLARE
reginfo1 sys.aq$_reg_info;
reginfolist sys.aq$_reg_info_list;
payload SYS.AQ$_JMS_TEXT_MESSAGE;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid raw(16);
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(Queue_table => 'TEST_QUEUT', Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE', multiple_consumers => FALSE);
DBMS_AQADM.CREATE_QUEUE(Queue_name => 'TEST_QUEUE', Queue_table => 'TEST_QUEUT');
DBMS_AQADM.START_QUEUE(queue_name => 'TEST_QUEUE' );
reginfo1 := sys.aq$_reg_info('TEST_QUEUE', DBMS_AQ.NAMESPACE_AQ, 'plsql://test_procedure?PR=0', HEXTORAW('FF'));
reginfolist := sys.aq$_reg_info_list(reginfo1);
sys.dbms_aq.register(reginfolist, 1);
COMMIT;
dbms_lock.sleep(2);
payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
payload.set_text('Test Test Test');
dbms_aq.enqueue(queue_name => 'TEST_QUEUE', enqueue_options => enqueue_options, message_properties => message_properties, payload => payload, msgid => msgid);
COMMIT;
dbms_lock.sleep(2);
END;
/
SELECT * from test_table
/
TEST
Test Test Test (insert by user = <SYS,CHRIS>)
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment