Skip to Main Content
  • Questions
  • Advanced Queuing & PL/SQL Notification & Oracle User

Breadcrumb

Question and Answer

Chris Saxon

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

Comments

thank you

Bernd Rehbein, August 03, 2016 - 12:57 pm UTC

thank you for your answer.

Now i know that i can not Change the SYS User.

This is pity, because in my case the CURRENT_SCHEMA don't help because i call in the notification routine existing PL/SQL-Programs which i cannot change, and this programs use the USER - function.
Chris Saxon
August 03, 2016 - 1:09 pm UTC

Surely you can change the PL/SQL code?! If you have a requirement to record the CURRENT_SCHEMA, sure you should change the code?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library