PL/SQL for deqeueu and enqueue in ORACLE

Question

im really new to coding (a few days).I would like to ask you for a help with my problem/idea.

im having .SQL script for re-enqueue of expired messages.

DECLARE
    v_en_options              DBMS_AQ.ENQUEUE_OPTIONS_T;
    v_en_message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
    v_en_message_handle       RAW (16);
    v_dq_options              DBMS_AQ.DEQUEUE_OPTIONS_T;
    v_dq_message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
    v_dq_message_handle       RAW (16);
    v_neeeding                MESSAGE;

    CURSOR x
    IS
        SELECT MSG_ID, corr_id
          FROM test.AQ$TEST_T
         WHERE msg_state = 'EXPIRED';
BEGIN
    FOR MESSAGE IN x
    LOOP
        v_dq_options.msgid := MESSAGE.msg_id;
        DBMS_AQ.dequeue (queue_name           => 'test.AQ$_test_T_E',
                         dequeue_options      => v_dq_options,
                         message_properties   => v_dq_message_properties,
                         payload              => v_needing,
                         msgid                => v_dq_message_handle);

        v_en_message_properties.correlation := MESSAGE.corr_id;
        DBMS_AQ.enqueue (queue_name           => 'test.test_Q',
                         enqueue_options      => v_en_options,
                         message_properties   => v_en_message_properties,
                         payload              => v_needing,
                         msgid                => v_en_message_handle);
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        ROLLBACK;
        RAISE;
END;

it´s working as i want (hope so :) ) but my idea is to put a another block of code that could do it for all QUEUES - it would start with a job a few times a day. It need help how to insert a varriable into queue_name and the select(table name).For instance another queue_name is test1,test2,test3 and coresponding tables.

I dont want to do severals scripts that will be run seperatly i prefer to do it by some select/config style where i can declare the queue.It will grow really fast in my project. I really looking for your answer and hoping to understand a bit more of coding.

Have a nice day !!!

Answer 1

If I understand your question correctly the way I would do this is to create a simple table with an ID and table name and active flag, you can then populate the table names into this table or remove them as needed.

create table my_queue_names (my_queue_id number default 0, my_queue_name varchar2(100), active_flag varchar2(1));

Then insert your queue names

insert into my_queue_names (my_queue_id, my_queue_name, active_flag) values (1, 'test.AQ$_test_T_E', 'Y');

Then in your declaration section declare a cursor eg

cursor c_queue_tables is
select ID, queue_name for my_queue_names
where aactive_flag = 'Y';

Then simply use a for loop (as you have for messages), and use cursor_variable.my_queue_name instead of hard coding queue_name in your enqueue/ dequeue statements.

Then whenever you have a new queue you simply insert into the config table you have created and it starts being processed. If you want to turn off just one you can simply set the active_flag to 'N' OR delete it from the config table.