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

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.

我在oracle中有一个带有BLOB列的表,它可以存储XML以及压缩的XML。这些是客户的要求,无法更改。这些表将被创建,我必须阅读并处理BLOB中的一些信息。

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

我已经研究过,任何不明确的解决方案都是明确的或为我工作。

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:

我面临的问题是使用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

Problems

I have researched and UTL_RAW can't be longer than 2000 bytes

我研究过,UTL_RAW不能超过2000字节

it seems that there is another limitation of 4000 bytes for BLOBs in Oracle

对于Oracle中的BLOB,似乎还有4000字节的限制

What could I do for those cases?

我能为这些案件做些什么?

2 个解决方案

#1

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

对于初学者,您需要了解LOB是什么。它们是“大数据”,可能比Oracle中的任何其他数据类型都大。它们就像文件系统上的常规文件。要写入文件系统上的文件,您必须这样做

open the file for writing

打开文件进行写作

truncate the file if you wish to start filling it from scratch

如果您希望从头开始填充文件,请截断该文件

read your source data in chunks in a loop

在循环中以块的形式读取源数据

append your data chunks to the file in the same loop, one by one

将数据块一个接一个地附加到同一循环中的文件中

close the file

关闭文件

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

LOB或多或少都是如此。在您的表中,LOB(CLOB / BLOB / NCLOB)列只是指向磁盘存储上保存实际数据的另一个位置的指针/引用。在标准Oracle术语中,指针称为“LOB定位符”。你需要

open/initialize the LOB locator

打开/初始化LOB定位器

truncate the LOB contents, if you wish to start filling it from scratch

如果您希望从头开始填充它,请截断LOB内容

append your data chunks to the LOB contents in a loop, one by one

将数据块一个接一个地附加到循环中的LOB内容中

close the LOB locator

关闭LOB定位器

In PL/SQL it could look like this:

在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;

An explanation:

initialize the LOB locator = insert into blob_test values(1,empty_blob()) returning b into v_b;

初始化LOB locator = insert到blob_test值(1,empty_blob())将b返回到v_b;

open the LOB locator for writing = dbms_lob.open(v_b,dbms_lob.lob_readwrite);

打开LOB定位器以写入= dbms_lob.open(v_b,dbms_lob.lob_readwrite);

truncate the LOB contents, if you wish to start filling it from scratch ... This is done by the empty_blob() call in the insert.

截断LOB内容,如果你想从头开始填充...这是通过插入中的empty_blob()调用完成的。

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

将数据块一个接一个地附加到循环中的LOB内容中=这里只有一次dbms_lob.writeappend()的迭代,只将一个长度为utl_raw.length(aaa)的单块aaa(最大值为32767)附加到LOB v_b中

close the LOB locator = dbms_lob.close(LOB_LOC=>v_b);

关闭LOB locator = dbms_lob.close(LOB_LOC => v_b);

#2

-1

Function utl_raw.cast_to_raw converts a value of data type VARCHAR2 into a raw value. Obviously, the length of the string is limited by VARCHAR2 datatype. If you need convert large text data to LOB you can use DBMS_LOB.CONVERTTOBLOB procedure.

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

For example you can create function to convert large string value (clob as input) to blob. something like this -

例如,您可以创建函数将大字符串值(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;

#1

open the file for writing