Oracle SQL -如何从BLOB中删除前导字节(并作为RAW返回)？

Question

我正在为一个报告工具(宾得报告设计器)编写一个查询，在该工具中我需要检索存储在Oracle 11数据库中的一些jpgs。诀窍是慢跑(存储为BLOB)包含一个我需要删除的12字节头(由另一个应用程序添加)。报告工具还要求将数据作为原始数据返回。

我遇到的问题是，我发现的操作BLOB的函数/过程似乎对它们都有禁止的大小/长度限制。

使用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

对于图像( FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001... 2000字节)，它工作得很好，剥离了12字节头并返回原始数据(如FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001...)。等

但是对于较大的图像(大多数图像)来说，2000是不够的，但是一旦我将子字符串长度增加到2001年，查询就失败了：

ORA- 06502 : PL/SQL:数值或值错误:原始变量长度太长，ORA-06512:在第1行06502处。00000 - "PL/SQL:数值或值错误%s“

这是我所得到的最接近的数据，但长话短说--在一个查询中，有什么方法可以从一个大BLOB中删除前12个字节并以原始的方式返回数据吗？

Answer 1

一般来说，考虑到ORA-14553，在查询中不可能立即修改blob。Blob变量是数据的指针，而不是数据本身。这就是为什么像DBMS_LOB.SUBSTR这样的函数返回的不是BLOB或CLOB，而是RAW或CHAR --因为最后类型的数据(变量)在内存中是直接可用的。

因此，要查询修改后的blob (从头上截取12个字节)，我们需要在查询之前创建和存储修改过的blob。这取决于业务需求，可以在适当的地方完成，也可以通过创建新的blobs，保存原件。在这个问题上，我想我们不能修改原来的问题。

显然，第二种方式(保存原件)是更加资源密集型的方式.

解决方案的总体情况：

1. 确定要查询的blobs列表。
2. 对于列表中的每一项，创建并存储一个修改后的blob (例如，用剪裁的12个字节)，并将其链接到原始的
3. 运行一个返回修改后的blobs的查询
4. 删除修改过的blobs (在使用查询游标之后或按计划执行)

这导致的问题比它解决的问题更多：

• 需要清理修改过的气泡
• 原始同步和修改同步
• 将存储的数据量增加一倍
• 其他惊喜

我想有一个不那么痛苦的解决方案--在外部编写一段代码，使用查询结果，然后在数据库之外修改blob数据。

Answer 2

只需为您创建一个用户定义的函数，并在select中使用它。

我给出了一个简单的例子，通过将第一个偏移量设为13而不是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;

然后，您只需在选择中使用此方法即可。

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

问候