Re-Enqueuing Oracle AQ Message

I recently had an issue with AQ messages which would not de-queue following a wierd Oracle error. The messages would only dequeue in Browse mode and therefore would never get processed or leave the queue.

To resolve this issue and to get my messages to the system that needed to processes them I wrote the following SQL to read the message and re-enqueue it as a new message which was then processed:

DECLARE
queueopts DBMS_AQ.DEQUEUE_OPTIONS_T;
msgprops DBMS_AQ.MESSAGE_PROPERTIES_T;
v_dequeue_options DBMS_AQ.dequeue_options_t;
v_enqueue_options DBMS_AQ.enqueue_options_t;
v_message_properties DBMS_AQ.message_properties_t;
v_message_prop_en DBMS_AQ.message_properties_t;
v_message_handle RAW(16);
v_message sys.aq$_jms_text_message ;
v_message_provider_id number(8,2);
v_message_provider varchar2(50);
v_alert_msg_id char(36);
v_alert_msg_type varchar2(50);
v_num_attach number(3);
v_alert_msg clob;
v_alert_msg_id_in char(36);
v_alert_msg_id_deq char(36);
v_corrmsg_id_in char(36);
V_QUEUE_MSG_NAME VARCHAR2(50);
V_QUEUE_MSG_ID NUMBER(8,2);
V_MESSAGE_EN SYS.AQ$_JMS_TEXT_MESSAGE;
V_ENQUEUE_OPTIONS_EN DBMS_AQ.ENQUEUE_OPTIONS_T;
V_MSGID RAW(16);
V_MESSAGE_SIZE NUMBER(5);
BEGIN
v_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
v_dequeue_options.wait := 2; --DBMS_AQ.NO_WAIT;
v_dequeue_options.correlation := v_alert_msg_id_deq ;
v_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
-- v_dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
v_dequeue_options.msgid := '93E04404404463F7E04400144FDCA7C6';

V_QUEUE_MSG_NAME := 'MYAPP.MYAPP_WORK_IN';
v_alert_msg_id := '5bc0352d-4578-4578-b938-bd457841763b';
v_corrmsg_id_in := '9af66884-d74c-af66-af66-479be7e7d772';

-- Dequeue message from the queue
DBMS_AQ.DEQUEUE(queue_name => V_QUEUE_MSG_NAME,
dequeue_options => v_dequeue_options,
message_properties => v_message_properties,
payload => v_message,
msgid => v_message_handle);

V_MESSAGE.GET_TEXT(v_alert_msg);

/* Now display some of the information. */
DBMS_OUTPUT.PUT_LINE('Dequeued msg id is ' || RAWTOHEX (v_message_handle));
DBMS_OUTPUT.PUT_LINE('MsgId: ' || v_message.get_string_property('msgId'));
DBMS_OUTPUT.PUT_LINE('MsgType: ' || v_message.get_string_property('msgType'));
DBMS_OUTPUT.PUT_LINE('numTags: ' || v_message.get_int_property('numTags'));

-- Output Message
DBMS_OUTPUT.PUT_LINE(v_alert_msg);

-- Create a new Message
V_MESSAGE_EN := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT;
v_message_prop_en.correlation := v_corrmsg_id_in;

-- Setting user defined message properties
V_MESSAGE_EN.SET_INT_PROPERTY('numTags', 0);
V_MESSAGE_EN.SET_STRING_PROPERTY('msgType' , v_message.get_string_property('msgType'));
V_MESSAGE_EN.SET_STRING_PROPERTY('msgProvider' , v_message.get_string_property('msgProvider'));
V_MESSAGE_EN.SET_STRING_PROPERTY('msgId' , v_alert_msg_id);

-- Set Message Payload
V_MESSAGE_EN.SET_TEXT(v_alert_msg);

-- Enqueue this message into AQ queue using DBMS_AQ package
DBMS_AQ.ENQUEUE(QUEUE_NAME => V_QUEUE_MSG_NAME,
ENQUEUE_OPTIONS => v_enqueue_options,
MESSAGE_PROPERTIES => v_message_prop_en,
PAYLOAD => V_MESSAGE_EN,
MSGID => v_alert_msg_id);

DBMS_OUTPUT.PUT_LINE('Message ' || v_alert_msg_id || ' sent');
END;

Granting access to multiple tables

Simple anonymous script to grant read access to a number of other user's tables

-- While as a DBA
DECLARE
BEGIN
FOR x in (SELECT owner, table_name FROM all_tables WHERE OWNER IN('SCOTT', 'MYAPP', 'HR'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.owner || '.' || x.table_name || ' TO myuser';
END LOOP;
END;

Using JRebel with sbt

At the time of writing, JRebel are offering a free year's license for Scala development see http://sales.zeroturnaround.com/, this is how to install it:

1) Download and install JRebel from http://www.zeroturnaround.com/jrebel/current/

2) Edit the sbt command

java -Xmx512M -noverify -javaagent:/usr/bin/jrebel/jrebel.jar -jar `dirname $0`/sbt-launch.jar "$@"

3) go to your projects root directory and run:

sbt
sbt> update
sbt> jetty-run
sbt> ~ prepare-webapp

Creating Intellij Projects for SBT Projects

I'm currently working on an Open-Source Scala project and we have switched our build process from Maven2 to SBT or Simple Build Tool http://code.google.com/p/simple-build-tool/. We've mainly done this as our project will be based on the Akka and the Lift framework which have also now switched to SBT.

SBT provides many of the features of Maven such as loading dependencies from remote repositories but is much simpler and the configuration is done in Scala rather than using messy xml files.

As part of this project I have found that JetBrains IntelliJ IDEA Community Edition seems to be the best Scala IDE I have tried and also has an SBT plug-in. As a bonus SBT has an action which will generate an Intellij IDEA project file configured to work with SBT.

To use this, go to the root of your SBT project and type sbt to start sbt, then enter:

*sbtIdeaRepo at http://mpeltonen.github.com/maven/
*idea is com.github.mpeltonen sbt-idea-processor 0.1-SNAPSHOT
update
idea

This will generate the .ipr file which you can then use

==============================================================

Update

Plug-ins are now stored globally in a $HOME/.sbt/plugins/build.sbt or C:\Documents and Settings\username\.sbt\plugins file which for this plugin the following contents should be added:

resolvers += "sbt-idea-repo" at "http://mpeltonen.github.com/maven/"

libraryDependencies += "com.github.mpeltonen" %% "sbt-idea" % "0.10.0"

Also the task is now called gen-idea

Note: In window use the command line to create a .sbt directory

See https://github.com/harrah/xsbt/wiki/Plugins for more details

Decoding VBE and Re-Coding VBS Files

Based on original article here

Since 2003 Microsoft has provided a command line script encoder available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e7877f67-c447-4873-b1b0-21f0626a6329&DisplayLang=en This tool encodes the script so it can't be read by a casual user but is no protection against a determined hacker.

Usage:

screnc ScriptFile

Note: You will need to install the download and also add the directory to your path to use it.

If you have a vbe script which you need to view/edit use create a new .vbs file and add the following in it:

option explicit
Dim oArgs, txtFileName

'Optional argument : the encoded filename
txtFileName=""
Set oArgs = WScript.Arguments

Select Case oArgs.Count
Case 0 'No Arg, popup a dialog box to choose the file
txtFileName=BrowseForFolder("Choose an encoded file", &H4031, &H0011)
Case 1
If Instr(oArgs(0),"?")=0 Then '-? ou /? => aide
txtFileName=oArgs(0)
End If
Case Else
WScript.Echo "Too many parameters"
End Select
Set oArgs = Nothing

If txtFileName<>"" Then
Dim fso
Set fso=WScript.CreateObject("Scripting.FileSystemObject")
If fso.FileExists(txtFileName) Then
Dim oFile,txtFileContents
Set oFile = fso.OpenTextFile(txtFileName, 1)
txtFileContents=oFile.readAll
oFile.close
Set oFile=Nothing

Const TagInit="#@~^" '#@~^awQAAA==
Const TagFin="==^#~@" '& chr(0)
Dim lStartCode, lExitCode
Do
lExitCode=0
lStartCode=Instr(txtFileContents,TagInit)
If lStartCode>0 Then
If (Instr(lStartCode,txtFileContents,"==")-lStartCode)=10 Then 'If "==" follows the tag
lExitCode=Instr(lStartCode,txtFileContents,TagFin)
If lExitCode>0 Then
txtFileContents=Left(txtFileContents,lStartCode-1) & _
Decode(Mid(txtFileContents,lStartCode+12,lExitCode-lStartCode-12-6)) & _
Mid(txtFileContents,lExitCode+6)
End If
End If
End If
Loop Until lExitCode=0
writeToFile txtFileName & ".vbs", txtFileContents
WScript.Echo txtFileContents
Else
WScript.Echo txtFileName & " not found"
End If
Set fso=Nothing
Else
WScript.Echo "Please give a filename"
WScript.Echo "Usage : " & wscript.fullname & " " & WScript.ScriptFullName & " "
End If

Sub writeToFile(txtFileName, txtFileContents)
Dim txtstr

Set txtstr = fso.CreateTextFile(txtFileName, True)
txtstr.write txtFileContents

txtstr.close
Set txtstr=Nothing
End Sub

Function Decode(txtBinChars)
Dim se,i,c,j,index,txtBinCharsTemp
Dim tDecode(127)
Const Combinaison="1231232332321323132311233213233211323231311231321323112331123132"

Set se=WSCript.CreateObject("Scripting.Encoder")

For i=9 to 127
tDecode(i)="JLA"
Next

For i=9 to 127
txtBinCharsTemp=Mid(se.EncodeScriptFile(".vbs",string(3,i),0,""),13,3)
For j=1 to 3
c=Asc(Mid(txtBinCharsTemp,j,1))
tDecode(c)=Left(tDecode(c),j-1) & chr(i) & Mid(tDecode(c),j+1)
Next
Next

'Next line we correct a bug, otherwise a ")" could be decoded to a ">"
tDecode(42)=Left(tDecode(42),1) & ")" & Right(tDecode(42),1)

Set se=Nothing

txtBinChars=Replace(Replace(txtBinChars,"@&",chr(10)),"@#",chr(13))
txtBinChars=Replace(Replace(txtBinChars,"@*",">"),"@!","<")
txtBinChars=Replace(txtBinChars,"@$","@")
index=-1

For i=1 to Len(txtBinChars)
c=asc(Mid(txtBinChars,i,1))
If c<128 Then index=index+1
If (c=9) or ((c>31) and (c<128)) Then
If (c<>60) and (c<>62) and (c<>64) Then
txtBinChars=Left(txtBinChars,i-1) & Mid(tDecode(c),Mid(Combinaison,(index mod 64)+1,1),1) & Mid(txtBinChars,i+1)
End If
End If
Next
Decode=txtBinChars
End Function

Function BrowseForFolder(ByVal pstrPrompt, ByVal pintBrowseType, ByVal pintLocation)
Dim ShellObject, pstrTempFolder, x
Set ShellObject=WScript.CreateObject("Shell.Application")
On Error Resume Next
Set pstrTempFolder=ShellObject.BrowseForFolder(&H0,pstrPrompt,pintBrowseType,pintLocation)
BrowseForFolder=pstrTempFolder.ParentFolder.ParseName(pstrTempFolder.Title).Path
If Err.Number<>0 Then BrowseForFolder=""
Set pstrTempFolder=Nothing
Set ShellObject=Nothing
End Function

Usage:

YourDecryptScriptName.vbs FileToDecrypt.vbe

The script will decode the vbe file, display it in a message box and also create/overwrite a new file called FileToDecrypt.vbe.vbs which you can edit

Starting & Stopping Services on Ubuntu

Get a list of services:

ls /etc/init.d

Then Stop/Start using servicename followed by action:

sudo invoke-rc.d servicename stop

sudo invoke-rc.d servicename stop

Oracle SQL to read a BLOB

Oracle SQL to read a BLOB

CREATE OR REPLACE PROCEDURE read_blob
IS
l_blob BLOB;
l_blob_length NUMBER;
l_amount BINARY_INTEGER := 10000; -- must be <= ~32765.
l_offset INTEGER := 1;
l_buffer RAW(20000);
l_text_buffer VARCHAR2(32767);
l_break_at PLS_INTEGER := 141; -- must be <= 255.
BEGIN

SELECT PZPVSTREAM INTO l_blob FROM PEGARULES.XCS_WORK
WHERE pxinsname = 'W-10332';

-- Figure out how long the BLOB is.
l_blob_length := DBMS_LOB.GETLENGTH(l_blob);

-- We'll loop through the BLOB as many times as necessary to
-- get all its data.
FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP

-- Read in the given chunk of the BLOB.
DBMS_LOB.READ(l_blob
, l_amount
, l_offset
, l_buffer);

-- The DBMS_LOB.READ procedure dictates that its output be RAW.
-- This next procedure converts that RAW data to character data.
l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);

-- Now that we have character data (up to l_amount characters long),
-- chunk it out so that we can call DBMS_OUTPUT (which only accepts
-- arguments less than or equal to 255 characters).
FOR j IN 1..CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(l_text_buffer
, (((j - 1) * l_break_at) + 1)
, LEAST(LENGTH(l_text_buffer)
-
((j - 1) * l_break_at)
, l_break_at)));
END LOOP;

-- For the next iteration through the BLOB, bump up your offset
-- location (i.e., where you start reading from).
l_offset := l_offset + l_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM,1,247));
END;
/

Fixing Michael Galpin's Comet Tutorial

I recently stumbled across the following tutorial on Comet:

Build Comet applications using Scala, Lift, and jQuery
http://www.ibm.com/developerworks/ajax/tutorials/wa-aj-comet/index.html

I was interested in this tutorial as comet is an alternative architecture to Ajax. In Ajax the browser makes calls (polls) to the web server in the background and then updates the users web page. This issue with this approach is that there is a delay between a change and the polling call. Also when you have a large number of users there will be a lot of polling calls hitting the web server that will not update the users web page (as there was no change).

Comet takes an alternative approach where it keeps a connection open to the user's browser and uses this to push data to the use. The benefit of this approach is that it essentially uses the Observer Pattern an only has web traffic when an update has been made which reduces load on the web server and is more scalable.

a potential replacement for Comet is Web Sockets in HTML 5 but this isn't supported by all browsers at present.

Anyway, when I tried building the tutorial I found that Michael had used a snapshot build to get new features but as this is a snapshot it is removed later from the Maven repositories giving this error:

[INFO] Failed to resolve artifact.

Missing:
----------
1) net.liftweb:lift-core:jar:0.10-SNAPSHOT

This is easily fixed by moving to the released version by changing pom.xml from:

net.liftweb
lift-core
0.10-SNAPSHOT

to the release version:

net.liftweb
lift-core
0.10

But unfortunately there were changes between the snapshot and the release which threw the following errors:

[INFO] Compiling 7 source files to C:\dev\scala\lift\broken_auctionNet\target\cl
asses at 1269595709118
[ERROR] C:\dev\scala\lift\broken_auctionNet\src\main\scala\bootstrap\liftweb\Boo
t.scala:44: error: value appendEarly is not a member of object net.liftweb.http.
LiftRules
[INFO] LiftRules.appendEarly(makeUtf8)
[INFO] ^
[ERROR] C:\dev\scala\lift\broken_auctionNet\src\main\scala\bootstrap\liftweb\Boo
t.scala:66: error: not found: type Can
[INFO] private def createOne: Can[Connection] = try {
[INFO] ^
[ERROR] C:\dev\scala\lift\broken_auctionNet\src\main\scala\bootstrap\liftweb\Boo
t.scala:87: error: not found: type Can
[INFO] def newConnection(name: ConnectionIdentifier): Can[Connection] =
[INFO] ^
[ERROR] C:\dev\scala\lift\broken_auctionNet\src\main\scala\org\developerworks\co
met\AuctionActor.scala:18: error: type mismatch;
[INFO] found : java.lang.String("auction")
[INFO] required: net.liftweb.util.Box[String]
[INFO] def defaultPrefix = "auction"
[INFO] ^
[ERROR] four errors found

The Can errors are fixed by replacing Can with Box in the two places in boot.scala

To fix the final error replace:

def defaultPrefix = "auction"

by boxing it Full and make it an override as follows:

override def defaultPrefix = Full("auction")

This fixes the errors but when you compile you get the following error in boot.scala:

[INFO] Compiling 7 source files to C:\dev\scala\lift\broken_auctionNet\target\cl
asses at 1269597516624
[ERROR] C:\dev\scala\lift\broken_auctionNet\src\main\scala\bootstrap\liftweb\Boo
t.scala:44: error: value appendEarly is not a member of object net.liftweb.http.
LiftRules
[INFO] LiftRules.appendEarly(makeUtf8)
[INFO] ^
[ERROR] one error found
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR

This is fixed by changing the line from:

LiftRules.appendEarly(makeUtf8)

to:

LiftRules.early.append(makeUtf8)

The code now compiles but when you start it using mvn jetty:run you get the following

2010-03-26 10:06:39.757:WARN::failed org.mortbay.jetty.plugin.Jetty6PluginWebApp
Context@33c78b{/,C:\dev\scala\lift\broken_auctionNet\src\main\webapp}: java.lang
.VerifyError: (class: net/liftweb/util/EmptyBox, method: open_$bang signature: (
)Ljava/lang/Object;) Can only throw Throwable objects
2010-03-26 10:06:39.757:WARN::failed ContextHandlerCollection@171bc3f: java.lang
.VerifyError: (class: net/liftweb/util/EmptyBox, method: open_$bang signature: (
)Ljava/lang/Object;) Can only throw Throwable objects
2010-03-26 10:06:39.757:WARN::failed HandlerCollection@1fddb33: java.lang.Verify
Error: (class: net/liftweb/util/EmptyBox, method: open_$bang signature: ()Ljava/
lang/Object;) Can only throw Throwable objects
2010-03-26 10:06:39.757:WARN::Error starting handlers
java.lang.VerifyError: (class: net/liftweb/util/EmptyBox, method: open_$bang sig
nature: ()Ljava/lang/Object;) Can only throw Throwable objects
at net.liftweb.http.LiftRules$.(LiftRules.scala:171)

This is resolved by increasing the Scala version to 2.7.3 from:

2.7.2

to:

2.7.3

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