Thursday, June 24, 2010
Script to Resubmit a Message from Exception Queue to its Base Queue in Oracle 10g Advanced Queues
I have designed this script to resubmit a message from Exception Queue to its Base queue with necessary validations.
Prerequisites:
- Create a Payload type
- Create a Queue table
- Create a Queue
- Start the Queue
- Create Subscriptions
- Enqueue the Necessary Messages so that it enters into Exception Queue.
Basic scripts for doing the above steps are available in the link below
SCRIPT:
Below is the script for resubmission,
SET serveroutput ON
DECLARE
r_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload demo_queue_payload_type; -- The custom payload type
v_exception_msg_id RAW(16) := '&expection_msg_id';
v_base_queue VARCHAR2(100) := 'DEMO_QUEUE'; -- The Custom Queue
v_exception_queue VARCHAR2(100);
v_queue_table_name VARCHAR2(100);
v_msg_check VARCHAR2(1);
v_check_query VARCHAR2(1000);
EX_END_OF_PROG EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE(
'*** Resubmission Starting ***'
);
r_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
r_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
r_dequeue_options.visibility := DBMS_AQ.IMMEDIATE;
r_dequeue_options.msgid := v_exception_msg_id;
-- Find the exception Queue name --
BEGIN
SELECT 'AQ$_'||queue_table||'_E',queue_table
INTO v_exception_queue , v_queue_table_name
FROM ALL_QUEUES
WHERE name = v_base_queue
AND owner = 'SCOTT';
DBMS_OUTPUT.PUT_LINE('*** Queue Found with the name '||v_base_queue||' ***');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('*** No Queue Found with the name '||v_base_queue||' ***');
RAISE EX_END_OF_PROG;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'*** ERROR while finding the Exception Queue name [' || SQLERRM || '] ***'
);
RAISE EX_END_OF_PROG;
END;
-- Checking the Presence of Message in the Queue to be Dequeued --
BEGIN
v_msg_check := 'N';
v_check_query := 'SELECT ''Y'' FROM '
||v_queue_table_name||
' WHERE q_name = '''||v_exception_queue||'''
AND msgid = '''||v_exception_msg_id||'''';
EXECUTE IMMEDIATE v_check_query
INTO v_msg_check;
IF v_msg_check = 'Y' THEN
DBMS_OUTPUT.PUT_LINE(
'*** Message Found!! Proceeding with Dequeuing ***'
);
ELSE
DBMS_OUTPUT.PUT_LINE(
'*** ERROR the message not found with the queue '||v_exception_queue|| ' ***'
);
RAISE EX_END_OF_PROG;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'*** ERROR while checking the Msg existance [' || SQLERRM || '] ***'
);
RAISE EX_END_OF_PROG;
END;
-- Dequeue the Message from Exception Queue --
DBMS_AQ.DEQUEUE(
queue_name => v_exception_queue,
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
DBMS_OUTPUT.PUT_LINE(
'*** Browsed message is [' || o_payload.message || '] ***'
);
-- Enqueue the Message to Base Queue --
DBMS_AQ.ENQUEUE(
queue_name => 'demo_queue',
enqueue_options => r_enqueue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
DBMS_OUTPUT.PUT_LINE(
'*** Resubmitted message id is [' || v_message_handle || '] ***'
);
DBMS_OUTPUT.PUT_LINE(
'*** Resubmission Sucess ***'
);
COMMIT;
EXCEPTION
WHEN EX_END_OF_PROG THEN
DBMS_OUTPUT.PUT_LINE('*** Resubmission FAILED ***'||CHR(10));
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('*** The following error occured while Resubmitting the message '||CHR(10)||'CODE : '||SQLCODE|| CHR(10)||'ERROR MESSAGE :'||SQLERRM||'***');
END;
/
SAMPLE OUTPUT:
*** Resubmission Starting ***
*** Queue Found with the name DEMO_QUEUE ***
*** Message Found!! Proceeding with Dequeuing ***
*** Browsed message is [TEST7] ***
*** Resubmitted message id is [63EA09169A064893AE7F3A58D005EEF3] ***
*** Resubmission Success ***
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Script to Resubmit a Message from Exception Queue to its Base Queue in Oracle 10g Advanced Queues”
Post a Comment