Reading BLOB as hexadecimal varchar string in Oracle DB

In a unnerving twist of fate, I had to check through the database for blobs matching a specific hex string "\u0020" aka the space character. After numerous searches, I concluded with the following that worked for me. Do note that there is still a 32KB limit on the final character string, so this works only if your blob is as small as what I'm expecting for mine.

SELECT recordid, DBMS_LOB.substr(blobunicode, length(blobunicode), 1) b64hex FROM tblblobjob WHERE length(blobunicode) BETWEEN 1 AND 10) AND DBMS_LOB.substr(blobunicode, length(blobunicode), 1) LIKE '20%'

In explanation, the DBMS_LOB package reads what it can grab from the blob column and produces a readable string from the base64 content. After reading several articles, I'd previously attempted to convert the BLOB to RAW, then to hexadecimal via the UTL_ENCODE package and whatnot. As it turns out, the solution was much simpler than visualised.

MQ configuration in JMS buried within Spring

As it turned out, the client needed a bunch of new message queues. We assumed it would be firmly tied down just like any other we'd done before. That's a frayed knot. The earlier implementations had robust request/reply channels that the initiating end would be responsible for checking on the replies based on the correlation identifier. For reasons undisclosed, here we have something a little different.

Instead of a correlation ID in the reply message that could be associated with the request message, the initiating end is expected to prepare a message listener on the reply queue to read in all messages, processing the contents accordingly.

Meanwhile, I'm still learning that Spring has a specific way of configuring for JMS that involves adding a few more lines into a project XML file. Bean configurations will be added for the MessageListener, JndiObjectFactoryBean, and a MessageListenerContainer. Double the dosage if you need a failover secondary. This is the tricky part I fell short on doing. Thus in short, for the message listener:

1.  Prepare a class that implements MessageListener 
2. Handle the event with a public void onMessage()
3. Configure the XML (my case was something like context-application-env-specific.xml) for the following
1. JndiObjectFactoryBean (pointing to the jndiName for the MQ connection factory)
2. JndiObjectFactoryBean (pointing to the jndifName for the MQ channel)
3. MessageListener
4. MessageListenerContainer (tying up all of the above)

The sample XML configuration (with failover) is as follows

<bean id="MQEventActionPrimaryListener"
    class="com.nec.asia.nric.domains.extinterface.mq.listener.MQEventActionListener">
    <property name="mqServer" value="MQ01" />
</bean>
<bean id="MQEventActionSecondaryListener"
    class="com.nec.asia.nric.domains.extinterface.mq.listener.MQEventActionListener">
    <property name="mqServer" value="MQ02" />
</bean>
<bean id="MQEventActionConnectionFactory1" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="resourceRef" value="false" />
    <property name="jndiName" value="${mq.event.action.cf.01}" />
</bean>
<bean id="MQEventActionConnectionFactory2" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="resourceRef" value="false" />
    <property name="jndiName" value="${mq.event.action.cf.02}" />
</bean>
<bean id="MQEventActionReplyQueue1" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="resourceRef" value="false" />
    <property name="jndiName" value="${mq.event.action.request.queue.01}" />
</bean>
<bean id="MQEventActionReplyQueue2" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="resourceRef" value="false" />
    <property name="jndiName" value="${mq.event.action.request.queue.02}" />
</bean>
<bean id="MQEventActionContainer1"
    class="org.springframework.jms.listener.DefaultMessageListenerContainer">
    <property name="connectionFactory" ref="MQEventActionConnectionFactory1" />
    <property name="destination" ref="MQEventActionReplyQueue1" />
    <property name="messageListener" ref="MQEventActionPrimaryListener" />
</bean>
<bean id="MQEventActionContainer2"
    class="org.springframework.jms.listener.DefaultMessageListenerContainer">
    <property name="connectionFactory" ref="MQEventActionConnectionFactory2" />
    <property name="destination" ref="MQEventActionReplyQueue2" />
    <property name="messageListener" ref="MQEventActionSecondaryListener" />
</bean>

 The values in braces "{ }" redirect to a properties file where the actual values are indicated. The assumption is that the MQ has been configured on the WebSphere server that the connection factories and channel names can be mapped into.

In addition, I was fortunate to locate the MQ Explorer. It allowed me peer into the message queues to browse messages and be certain they have not been consumed, among other things, such as creating test messages.