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

Reading Oracle AQ JMS Text XML

Placeholder link to a great and very useful article on Oracle AQ JMS

Listing today's files in a directory without using find

Had an issue today where I needed to get the today's log files from a Unix directory but couldn't use find. Normally I could use the time function in find to do this as follows where . is the directory to search from (. is the current directory):

find . -mtime +0

However the only way I could get the date for the files was by using ls -al and then removing all the extra data to leave just the filename.

The following script takes an argument of the directory you want to list all the files with the current date:

#!/bin/sh

die () {

echo >&2 "$@"

exit 1

}

[ "$#" -eq 1 ] || die "target directory argument required, $# provided"

target=$1

myvar=`date "+%h %d"`

for file in `ls -al $target | grep "$myvar" | sed 's/[-drwx]* *[0-9] *[a-zA-Z0-9]* *[a-zA-Z0-9]* *[0-9]* *[a-zA-Z]* [0-9]* *[0-9]*:[0-9]* *\.*//'`; do

echo $file

done

Testing my posterous account

I this message appears in twitter and on my blog then posterous has processed my email and put it both on my blog and on twitter

this is a test

Posted via email from Paul's posterous

How to fix the Glassfish Admin App

Recently we've been having intermittant issues with the GlassFish 2.1 (b.60e) Web Administration Console. The behaviour is that after successfully login you get a HTTP 500 error instead of the web app As follows (minus colours)

HTTP Status 500 - type Exception report

message
descriptionThe server encountered an internal error () that prevented it from fulfilling this request.

exception
javax.servlet.ServletException: java.lang.reflect.InvocationTargetException while attempting to process a 'beforeCreate' event for 'sun_frameSet4'.

root cause
java.lang.RuntimeException: java.lang.reflect.InvocationTargetException while attempting to process a 'beforeCreate' event for 'sun_frameSet4'.

root cause
java.lang.reflect.InvocationTargetException

root cause
javax.xml.transform.TransformerFactoryConfigurationError: Provider org.apache.xalan.processor.TransformerFactoryImpl not found

note The full stack traces of the exception and its root causes are available in the Sun GlassFish Enterprise Server v2.1 logs.
Sun GlassFish Enterprise Server v2.1

At first we were told it was a jar class but I was skeptical as the appserver should keep those isolated from one application to another, however there is something I overlooked that creeps across applications which is the System Properties which are across the JVM.

In the end the issue is down to how the TransformerFactory creates a new instance which uses system properties, more details follow:

Issue:
The Admin app breaks displaying the http 500 error after a successful logon when the javax.xml.transform.TransformerFactory system property is set to a class that is not in its classpath.

Rationale:
This is due to the way that the TransformerFactory is implemented which the Admin app calls to get an XML parser.

To get an XML parser javax.xml.transform.TransformerFactory.newInstance()is called and this uses the following ordered lookup procedure to determine the TransformerFactory implementation class to load:

• Use the javax.xml.transform.TransformerFactory system property.
• Use the properties file "lib/jaxp.properties" in the JRE directory. This configuration file is in standard java.util.Properties format and contains the fully qualified name of the implementation class with the key being the system property defined above. The jaxp.properties file is read only once by the JAXP implementation and it's values are then cached for future use. If the file does not exist when the first attempt is made to read from it, no further attempts are made to check for its existence. It is not possible to change the value of any property in jaxp.properties after it has been read for the first time.
• Use the Services API (as detailed in the JAR specification), if available, to determine the classname. The Services API will look for a classname in the file META-INF/services/javax.xml.transform.TransformerFactory in jars available to the runtime.
• Platform default TransformerFactory instance.

see http://java.sun.com/j2se/1.5.0/docs/api/javax/xml/transform/TransformerFactory.html#newInstance()

System Setting are across the JVM and by default the javax.xml.transform.TransformerFactory system property is not set.

Fix:
Remove the javax.xml.transform.TransformerFactory system property and use one of the other methods to determine the TransformerFactory implementation class