RTE and JDE - what is stored in the BLOB?

I'm chasing down a missing RTE message.

RTE's are good, but confusing and the subject of many of my posts.

This is talking a little about converting the BLOB from and RTE into something slightly readable (or at least searching).

We all know that out of the box, RTE write to a single copy of the F90710 - that is the first thing that it does.

The following statement allowed me to find the owner for F90710 in PY - I remember that we spilt them and I could not remember where.  This is quicker than looking up OCM.

select owner from all_tables where table_name = 'F90710';

I then take a look at some messages, I have history enabled, so there are a heap of messages at status 5.

select * from py920.f90710  order by etevntseq desc;

I then start to rip apart the BLOB field to work out the contents, of course -we know [and are frustrated by] the fact that these are stored as BLOBs in some kinda weird and AS/400 proof format.

Using the UTL_RAW package I can view the contents of the ETEDATA field:

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where etevntseq > 17000 order by etevntseq desc;

So we can see from above that we can make out the characters in the BLOB using cast to varchar2.  This is neat...  We kinda think that because all of those spaces are there, we could use cast_to_nvarchar2, but this leaves us with a bunch of rubbish.  I think I have blogged on this before that JDE is flipping the double bytes into something that the standard NVARCHAR string functions do not like, so we need to do our comparisons (of strings) using S P A C E S between the characters.

There are a bunch of util functions that you can use over the results of the function, as it's basically a VARCHAR.  The other nice thing is that you can also see the DSTR that is storing the values in the BLOB:

D4202310A and D4202310B

So use this to reverse engineer what you are seeing above.

Once would also guess that the 1252 in the header was something related to https://en.wikipedia.org/wiki/Windows-1252

Anyway, back to what we want...  Tracking down events for certain sales orders.

I know that the SO number is 19274775

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where instr(utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)),'19274775') > 1 order by etevntseq desc;

Above shows how I can work through the data.  Note that this does not have spaces because the sales order number is a numeric [in the scheme of things].  Also, important to note that you can return the value of instr to then know the index of the sales order number and make your SQL much more efficient.

We only have a single row - which is a shame, we seem to be missing the status code of ADD, but we have the update.  So I need to go searching.

Hopefully that shows some basics of how you might be able to start debugging some RTE problems in JDE.