Oracle SQL - How to strip leading bytes from BLOB (and return as RAW)?

Question

I'm writing a query for a reporting tool (Pentaho Report Designer) where I need to retrieve some jpgs that are stored in the Oracle 11 database. The trick is that the jogs (stored as BLOBs) include a 12 byte header (added by another application) that I need to remove. The reporting tool also requires the data to be returned as RAW data.

I am running into problems where it appears that the functions/procedures that I have found for manipulating BLOBs all have prohibitive size/length restrictions on them.

This query is as close to working as I can get it, using DBMS_LOB.SUBSTR(dbfile.filedata,2000,12):

select DBMS_LOB.SUBSTR(dbfile.filedata,2000,12) as filedata
from bms_0002005_251 safety
inner join bms_9999999_100 file02 on safety.bms_id = file02.bms_fk_0002005_839_ID
inner join bms_9999999_104 inc on safety.bms_fk_0002005_844_id = inc.bms_id
left join bms_dbfiles dbfile on file02.bms_9999999_40 = dbfile.uniqueid

For images <= 2000 bytes, this works perfectly, strips the 12 byte header and returns raw data like FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001... etc

But for larger images (most of the images) 2000 is not enough, but as soon as I increase the substring length to 2001, the query fails:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s"

This is the closest I have got, but long story short - is there any way in a single query I can strip the first 12 bytes from a large BLOB and return the data as RAW?

Mayor

I'm afraid you'll have to come up with a solution outside of Oracle DB. You know, RAW truly is limited to a maximum length of 4000 (in SQL context) or 32767 (in PLSQL context), so BLOB is what you should use for sending JPEGs from BLOBs to your calling app. Even if you redeclare your client-side variable to RAW(32767), you'll eventually run into problems with JPEGs over 32k in length. – Teratism

Thanks for the info @nop77svk - what about if I take the need to go to RAW out of the equation... is there a way to just return my entire BLOB minus the first 12 bytes? – Mayor

Yes, there is. The dbms_lob.substr() you came up with is the right way to go. – Teratism

But, as per my OP, that substr() function only seems to let me return 2,000 bytes which appears to be a hard limit, correct? And if I were able to write it as a PL/SQL script (which I am not able to, due to the reporting tool) there would still be a limit of 32,767, correct? – Mayor

Ah, sorry, you're right. I see no prebuilt function returning LOB from an offset from a LOB. Do you need the data exactly in one row or could your reporting tool cope with a BLOB returned in multiple, sequentially numbered chunks? Or could the tool cope with an anonymous PLSQL block executed instead of a query? Do you have the option of creating PLSQL functions in your DB? – Teratism

Answer 1

Generally speaking, it is not possible to modify a blob on-the-fly in a query, considering ORA-14553. Blob variable is a pointer to data, but not the data itself. That's why functions like DBMS_LOB.SUBSTR return not BLOB or CLOB, but RAW or CHAR - since data (variables) of the last types are directly available in RAM.

Thus to query modified blob (with 12 bytes cut off from the head) we need to create and store the modified blob before querying. Depending on business requirements it can be done in place or by creating new blobs, preserving originals. In the case of the question I suppose we cannot modify original blobs in place.

Obviously, the second way (preserving originals) is more resource-intensive way.

Big picture of solution:

1. Determine a list of blobs you are going to query
2. For each item in the list create and store a modified blob (e.g. with trimmed 12 bytes) with link by id to the original
3. Run a query returning modified blobs
4. Remove modified blobs (immediately after the query cursor is consumed OR by schedule)

This causes more problems than it solves:

• need for cleanup of modified blobs
• synchronization of original and modified
• doubling the amount of stored data
• other surprises

I guess there is a less painful solution - write a code in the outer party consuming the query results and modify blob data outside of the database.

Icehouse

Answer 2

Just create a user defined function to do this for you and use it in the select.

I whipped one simple example that does this by simply having the first offset at 13 instead of 1:

create or replace function strip12(p_blob in blob) return blob is
  pragma autonomous_transaction;
  l_length  pls_integer;
  l_loc     pls_integer;
  l_buffer  pls_integer := 2000;
  l_newblob blob;
begin
  dbms_lob.createtemporary(lob_loc => l_newblob, cache => true);
  l_length := dbms_lob.getlength(p_blob);

  l_loc := 13;
  while l_loc <= l_length loop
    dbms_lob.append(dest_lob => l_newblob, src_lob => dbms_lob.substr(lob_loc => p_blob, amount => l_buffer, offset => l_loc));
    l_loc := l_loc + l_buffer;
  end loop;
  return l_newblob;
end;

Then you simply use this in your select

select strip12(dbfile.filedata) as filedata
  from bms_0002005_251 safety
 inner join bms_9999999_100 file02
    on safety.bms_id = file02.bms_fk_0002005_839_ID
 inner join bms_9999999_104 inc
    on safety.bms_fk_0002005_844_id = inc.bms_id
  left join bms_dbfiles dbfile
    on file02.bms_9999999_40 = dbfile.uniqueid

Regards

Splashdown