插入大于2000或4000字节的BLOB测试字符串

Question

我在Oracle中有一张表，其中有一个 BLOB 列，可以存储XML和被压缩的XML。这是客户的要求，不能更改。表已经创建好，我需要读取并处理BLOBs中的某些信息。

经过研究，任何不清晰的解决方案都对我无效。

我面临的问题是，当使用DBeaver作为数据库管理器，并使用utl_raw.cast_to_raw插入大于2000字节的纯XML数据时，我收到了以下消息：

SQL Error [6502] [65000]: ORA-06502: PL/SQL: numeric or value error: raw variable length too long ORA-06512: at "SYS.UTL_RAW", line 224
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_RAW", line 224

问题

1. 我已经进行了研究，UTL_RAW 不能超过 2000 字节
2. 在 Oracle 中，BLOBs 还有一个 4000 字节 的限制

对于这些情况，我该怎么办？

- Weslor

---

你想做什么？将zip文件加载到blob列中吗？ - Arkadiusz Łukasiewicz

感谢您的关注。我面临的问题是使用utl_raw.cast_to_raw插入大于2000字节的XML纯数据。 - Weslor

你使用什么来插入数据？是SQPLUS还是Java应用程序？ - Arkadiusz Łukasiewicz

我使用DBeaver，但我敢打赌这个问题也会出现在SQLPlus和其他工具中。 - Weslor

不使用utl_file --> 插入到表中的值为(UTL_RAW.CAST_TO_RAW('.....')) - Weslor

Answer 1

首先，您需要了解LOB是什么。它们是“大型数据”，可能比Oracle中的任何其他数据类型都要大。它们就像文件系统上的常规文件。为了向文件系统中的文件写入内容，您需要：

1. 打开文件以进行写入
2. 如果您希望从头开始填充它，请截断文件
3. 在循环中以块的方式读取源数据
4. 在同一循环中逐个将数据块附加到文件中
5. 关闭文件

LOB也基本相同。在您的表中，LOB（CLOB / BLOB / NCLOB）列只是指向存储实际数据的磁盘存储的指针/引用。在标准的Oracle术语中，该指针称为“LOB定位器”。您需要：

1. 打开/初始化LOB定位器
2. 如果您想从头开始填充它，请截断LOB内容
3. 在循环中以块的方式将数据块附加到LOB内容中
4. 关闭LOB定位器

在PL / SQL中，代码如下：

-- create table blob_test(id number, b blob);

declare 
  v_b blob; 
  aaa raw(32767);
  longLine varchar2(32767);
begin 
  longLine :=  LPAD('aaaa', 32767,'x');
  aaa := UTL_RAW.CAST_TO_RAW(longLine);
  insert into blob_test values(1,empty_blob()) returning b into v_b;
  dbms_lob.open(v_b,dbms_lob.lob_readwrite);
  dbms_lob.writeappend(v_b,UTL_RAW.LENGTH (aaa) ,aaa);
  dbms_lob.close(LOB_LOC=>v_b);
  commit;
end;

解释：

1. 初始化LOB定位器 = insert into blob_test values(1,empty_blob()) returning b into v_b;
2. 打开LOB定位器以便写入 = dbms_lob.open(v_b,dbms_lob.lob_readwrite);
3. 如果您希望从头开始填充LOB，则截断LOB内容...这是通过insert中的empty_blob()调用完成的。
4. 循环追加您的数据块到LOB内容中，每次一个 = 这里仅迭代一次dbms_lob.writeappend()，将长度为utl_raw.length(aaa)（最大为32767）的单个块aaa追加到LOB v_b中
5. 关闭LOB定位器 = dbms_lob.close(LOB_LOC=>v_b);

- Arkadiusz Łukasiewicz

---

我不理解它，也不知道该如何使用它。您能否详细解释一下如何使用那个脚本，以便将其包含在我的脚本中，我的脚本有多个插入操作，例如如果我的表名为“MYTABLE”，并且我想插入一个5000个字符的字符串或XML，应该怎么做？ - Weslor

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Arkadiusz Łukasiewicz

LOB操作的使用情况太过依赖于操作本身的实际位置。这个答案提供了一个很好的通用示例，其余部分应该由OP自行学习能力来完成。 - peter.hrasko.sk

Answer 2

utl_raw.cast_to_raw 函数将 VARCHAR2 数据类型的值转换为原始值。显然，字符串的长度受到 VARCHAR2 数据类型的限制。如果您需要将大文本数据转换为 LOB，则可以使用 DBMS_LOB.CONVERTTOBLOB 过程。

例如，您可以创建一个函数来将大字符串值（以 clob 作为输入）转换为 blob。类似于这样 -

create or replace function ClobToBlob (p_clob in clob) return blob is
   l_dest_offset   integer := 1;
   l_source_offset integer := 1;
   p_csid          number  := 0;
   l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
   l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
   l_tmpblob blob;

  begin
   dbms_lob.createtemporary(l_tmpblob, true);
   DBMS_LOB.CONVERTTOBLOB
  (
   l_tmpblob,
   p_clob,
   DBMS_LOB.LOBMAXSIZE,
   l_dest_offset,
   l_source_offset,
   p_csid,
   l_lang_context,
   l_warning
  );
  return l_tmpblob;
end;

- Stawros