Thursday, June 24, 2010
Enqueue any Advanced Queues JMS in Oracle 10g
I have designed this script to Enqueue a XML message to any AQJMS. I have tested the script in Oracle 10g.
SCRIPT:
SET serveroutput ON
DECLARE
v_queue_table ALL_QUEUES.queue_table%TYPE;
v_enque_enabled ALL_QUEUES.enqueue_enabled%TYPE;
v_object_type all_queue_tables.object_type%TYPE;
v_queue_name ALL_QUEUES.name%TYPE;
v_msg CLOB := '';
v_enqueue_options dbms_aq.enqueue_options_t;
v_msg_props dbms_aq.message_properties_t;
v_msg_id RAW(16);
v_message SYS.AQ$_JMS_MESSAGE := SYS.AQ$_JMS_MESSAGE.construct(DBMS_AQ.JMS_TEXT_MESSAGE);
EX_END_OF_PROG EXCEPTION;
BEGIN
v_queue_name := '&Enter_the_Queue_name';
v_msg := '&Enter_the_message';
--Checking the availablity of the Queue
BEGIN
SELECT queue_table,enqueue_enabled
INTO v_queue_table,v_enque_enabled
FROM ALL_QUEUES
WHERE name = v_queue_name;
IF RTRIM(LTRIM(v_enque_enabled)) = 'NO' THEN
DBMS_OUTPUT.PUT_LINE('The Queue Found with the name '||v_queue_name||' is not enabled for Enqueing. Please enable and retry.');
RAISE EX_END_OF_PROG;
END IF;
SELECT object_type
INTO v_object_type
FROM all_queue_tables
WHERE queue_table = v_queue_table;
IF v_object_type <> 'SYS.AQ$_JMS_MESSAGE' THEN
DBMS_OUTPUT.PUT_LINE('The Queue Found with the name '||v_queue_name||' is not a JMS Queue.');
RAISE EX_END_OF_PROG;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Queue Found with the name '||v_queue_name);
RAISE EX_END_OF_PROG;
END;
DBMS_OUTPUT.PUT_LINE('Inside Enque Message Unit'||CHR(10));
DBMS_OUTPUT.PUT_LINE('Input Parameters'||CHR(10));
DBMS_OUTPUT.PUT_LINE(RPAD('Queue Name',30)||':'||v_queue_name||CHR(10));
DBMS_OUTPUT.PUT_LINE(RPAD('Enq MSG',30)||':'||CHR(10)||v_msg||CHR(10)||CHR(10));
v_message.set_text(xmltype(v_msg).getstringval());
DBMS_AQ.ENQUEUE( queue_name => v_queue_name,
enqueue_options => v_enqueue_options,
message_properties => v_msg_props,
payload => v_message,
msgid => v_msg_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Msg Id'||v_msg_id||CHR(10));
DBMS_OUTPUT.PUT_LINE('ENQUE SUCCESS '||CHR(10));
EXCEPTION
WHEN EX_END_OF_PROG THEN
DBMS_OUTPUT.PUT_LINE('ENQUE FAILED'||CHR(10));
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following error occured while enqueing the data '||CHR(10)||'CODE : '||SQLCODE|| CHR(10)||'ERROR MESSAGE :'||SQLERRM);
END;
/
SAMPLE OUTPUT:
Inside Enqueue Message Unit
Input Parameters
Queue Name : JMS_ABC_QUEUE
Enq MSG :
Msg Id 0BEE2728576041A2BF626E13D8AAA54D
ENQUE SUCCESS
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Enqueue any Advanced Queues JMS in Oracle 10g”
Post a Comment