Moving Exception Messages back onto the normal AQ queue

Just wrote some PL/SQL to restore messages on the exception queue back onto the normal queue.

DECLARE
dequeue_options dbms_aq.dequeue_options_t;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(32);
message SYS.AQ$_JMS_TEXT_MESSAGE;

deq_qname varchar2(50);
enq_qname varchar2(50);

ex_no_messages exception;
ex_dequeue exception;
pragma exception_init (ex_no_messages, -25263);
pragma exception_init (ex_dequeue, -25228);
msg_count number;
BEGIN
deq_qname := 'SCOTT.AQ$_TBL_DATAFEED_REQ_E';
enq_qname := 'SCOTT.DATAFEED_REQ';

dequeue_options.wait := DBMS_AQ.NO_WAIT;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.dequeue_mode := dbms_aq.remove_nodata;

msg_count := 0;

-- Enabling the exception queue for dequeue
dbms_aqadm.start_queue(deq_qname, false, true);

LOOP --Looping to find and remove the message from exception queue
dbms_aq.dequeue(queue_name => deq_qname,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

dbms_aq.enqueue(queue_name => enq_qname,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload =>message,
msgid => message_handle);

dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;

msg_count := msg_count + 1;

DBMS_OUTPUT.PUT_LINE ('Processed ' || msg_count || ' messages');

dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
END LOOP;

EXCEPTION -- exception handling
WHEN ex_no_messages THEN
DBMS_OUTPUT.PUT_LINE ('No of Messages Moved: '||msg_count);
COMMIT;
WHEN ex_dequeue THEN
DBMS_OUTPUT.PUT_LINE ('No of Messages Moved: '||msg_count);
COMMIT;
END;
/

Extracting data from a JMS Text message

Some useful SQL to extract data from a JMS Text message

SELECT SUBSTR(q.user_data.text_lob, INSTR(q.user_data.text_lob,'') + 20,7) "Special Reference",
SUBSTR(q.user_data.text_lob,
INSTR(q.user_data.text_lob,'') + 19,
(INSTR(q.user_data.text_lob,'')
- INSTR(q.user_data.text_lob,'') - 19)
) "Contact",
SUBSTR(q.user_data.text_lob,
INSTR(q.user_data.text_lob,'') + 5,
(INSTR(q.user_data.text_lob,'')
- INSTR(q.user_data.text_lob,'') - 5)
) "REF"
,q.MSGID, q.CORRID, q.ENQ_TIME, q.DEQ_TIME, q.RETRY_COUNT,
CASE WHEN q.Q_NAME = 'AQ$_TBL_XMLMSG_E' THEN 'FAIL' ELSE 'PASS' END as "successful",
q.Q_NAME, length(q.user_data.text_lob) as "MSG Size", q.user_data.text_lob, q.user_data.text_vc
FROM MSGUSER.TBL_XMLMSG q
WHERE q.ENQ_TIME > to_date('12-02-2010 00:00:00', 'dd-mm-yyyy hh24-mi-ss')
AND q.Q_NAME = 'AQ$_TBL_XMLMSG_E'
AND INSTR(q.user_data.text_lob,'Tester') = 0 -- Exclude Test records
ORDER BY q.DEQ_TIME desc, q.ENQ_TIME

Scala and Lift Maven Architypes

Maven Architype command line instructions to be used with Maven 2 to create new projects

# Basic Scala maven type
mvn org.apache.maven.plugins:maven-archetype-plugin:1.0-alpha-7:create -DarchetypeGroupId=org.scala-tools.archetypes -DarchetypeArtifactId=scala-archetype-simple -DarchetypeVersion=1.1 -DremoteRepositories=http://scala-tools.org/repo-releases -DgroupId=your.proj.gid -DartifactId=your-proj-id

# Lift type for LIFT projects
mvn archetype:generate -U -DarchetypeGroupId=net.liftweb -DarchetypeArtifactId=lift-archetype-blank -DarchetypeVersion=1.0 -DremoteRepositories=http://scala-tools.org/repo-releases -DgroupId=play -DartifactId=play -Dversion=1.0-SNAPSHOT