Created
October 26, 2012 11:26
-
-
Save wave2/3958276 to your computer and use it in GitHub Desktop.
Extracting content from JMS messages stored within OpenMQ HA DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- Here is a dissected view of one method to get at the JMS payload stored within a Blob column. | |
| -- | |
| -- 1. First look within the Blob to find the word MyIdentifier and obtain the start position: | |
| -- | |
| -- DBMS_LOB.INSTR(MESSAGE, UTL_RAW.CAST_TO_RAW('MyIdentifier'), 1, 1) | |
| -- | |
| -- 2. Next pass the position from INSTR to the SUBSTR function and return 120 characters from the word 'MyIdentifier' | |
| -- (needs to be less than the RAW limit of 2000 but enough to contain the info you want) | |
| -- | |
| -- DBMS_LOB.SUBSTR(MESSAGE, 120, DBMS_LOB.INSTR(MESSAGE, UTL_RAW.CAST_TO_RAW('MyIdentifier'), 1, 1)) | |
| -- | |
| -- 3. We can now convert the resulting 120 character string (which should contain the information we are looking for) into a VARCHAR2. | |
| -- | |
| -- UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(MESSAGE, 120, DBMS_LOB.INSTR(MESSAGE, UTL_RAW.CAST_TO_RAW('MyIdentifier'), 1, 1))) | |
| -- | |
| -- 4. Finally we can use the REGEXP function (or anything else you fancy) to get at the important information e.g MyIdentifier | |
| -- | |
| -- REGEXP_SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(MESSAGE, 120, DBMS_LOB.INSTR(MESSAGE, UTL_RAW.CAST_TO_RAW('MyIdentifier'), 1, 1))), '[[:digit:]]+') | |
| -- | |
| SELECT REGEXP_SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(MESSAGE, 120, DBMS_LOB.INSTR(MESSAGE, UTL_RAW.CAST_TO_RAW('ApplicationId'), 1, 1))),'[[:digit:]]+') AS MyIdentifier FROM MQMSG41CMYJMSDB |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment