插入大于2000或4000字节的BLOB测试字符串 [英] Insert BLOB test string bigger than 2000 or 4000 bytes

问题描述

我在oracle中有一个带有 BLOB 列的表，该表可以存储XML以及压缩的 XML .这些是客户的要求，不能更改.这些表将被创建，并且我必须阅读并使用 BLOBs 中的一些信息.

I have a table in oracle with a BLOB column, that can store XMLs and as well XMLs zipped. These are requirements from the customer and can't be changed. The tables will be created and I have to read and work with some information inside the BLOBs.

我已经研究过，任何不清楚的解决方案对我来说都是明确的或有效的.

I have researched and any of the unclear solutions were clear or worked for me.

我面临的问题是使用 utl_raw.cast_to_raw XML XML 普通数据大于 2000字节>使用 DBeaver 作为数据库管理器.我收到了消息:

The problem I am facing is that to INSERT XML plain data bigger than 2000 bytes with utl_raw.cast_to_raw using DBeaver as Database Manager. I received the message:

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中 BLOB 似乎还有 4000字节的另一个限制

1. I have researched and UTL_RAW can't be longer than 2000 bytes
2. it seems that there is another limitation of 4000 bytes for BLOBs in Oracle

在这些情况下我该怎么办?

What could I do for those cases?

推荐答案

对于初学者，您需要了解什么是LOB.它们是大数据"，可能比Oracle中的任何其他数据类型大.它们就像文件系统上的常规文件.为了在文件系统上写入文件，您必须

For starters, you need to understand what LOBs are. They are "large data", possibly larger than any other data types in Oracle. They are like regular files on a filesystem. In order to write to a file on a filesytem, you'll have to

1. 打开文件进行写入
2. 如果要从头开始填充文件，请截断文件
3. 循环读取块中的源数据
4. 将数据块一次又一次地添加到文件中
5. 关闭文件

LOB大致相同.在您的表中，LOB(CLOB/BLOB/NCLOB)列只是指向磁盘存储器上保存实际数据的其他位置的指针/引用.用标准的Oracle术语，该指针称为"LOB定位器".您需要

More or less the same is true for LOBs. In your table, a LOB (CLOB/BLOB/NCLOB) column is just a pointer/reference to another place on your disk storage holding the actual data. In standard Oracle terms, the pointer is called "LOB locator". You need to

1. 打开/初始化LOB定位器
2. 如果要从头开始填充LOB内容，请截短
3. 将数据块一个接一个地追加到LOB内容中
4. 关闭LOB定位器

在PL/SQL中，它可能看起来像这样:

In PL/SQL it could look like this:

-- 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定位器= 插入blob_test值(1，empty_blob())，将b返回到v_b;
2. 打开LOB定位器以进行写入= dbms_lob.open(v_b，dbms_lob.lob_readwrite);
3. 如果希望从头开始填充LOB内容，则将其截断...这是通过 insert 中的 empty_blob()调用完成的.
4. 在循环中将数据块附加到LOB内容中，一个接一个=这里仅 dbms_lob.writeappend()的一次迭代，仅附加一个块 aaa 长度 utl_raw.length(aaa)(最多32767)进入LOB v_b
5. 关闭LOB定位器= dbms_lob.close(LOB_LOC => v_b);

1. initialize the LOB locator = insert into blob_test values(1,empty_blob()) returning b into v_b;
2. open the LOB locator for writing = dbms_lob.open(v_b,dbms_lob.lob_readwrite);
3. truncate the LOB contents, if you wish to start filling it from scratch ... This is done by the empty_blob() call in the insert.
4. append your data chunks to the LOB contents in a loop, one by one = here only one iteration of dbms_lob.writeappend(), appending only a single chunk aaa of length utl_raw.length(aaa) (maximum of 32767) into the LOB v_b
5. close the LOB locator = dbms_lob.close(LOB_LOC=>v_b);

这篇关于插入大于2000或4000字节的BLOB测试字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！