title: Oracle SQL - How to strip leading bytes from BLOB (and return as RAW)?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>This query is as close to working as I can get it, using DBMS_LOB.SUBSTR(dbfile.filedata,2000,12):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For images &lt;= 2000 bytes, this works perfectly, strips the 12 byte header and returns raw data like 
  FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001... etc</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>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"</p>
  </blockquote>
  
  <p>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?</p>