Getting content from OX_CONTENT

Discussion created by Claus_Jambrich_5663 on Dec 19, 2017
Trying to get the text from OX_CONTENT is a bit tricky, as it is a BLOB with RAW data inside. For Oracle, this is a solution approach:
select ox_oh_idnr, OX_CONTENTLEN, utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(OX_CONTENT,OX_CONTENTLEN,1)) from ox where ox_oh_idnr = 1049;
The tricky part: If the content is longer then 2000 characters, you have to do this in chucks. So, above select only works for a OX_CONTENTLEN ≤ 2000. If it is bigger, something like this must be done:
select ox_oh_idnr, OX_CONTENTLEN, utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(OX_CONTENT,2000,1)) || utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(OX_CONTENT,2000,2001)) from ox;
I.e. you concatenate the chunks of 2000 bytes.

Of course this is only a simplified example that can be extended as you wish.