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

Insert BLOB test string bigger than 2000 or 4000 bytes

提问by Weslor

I have a table in oracle with a BLOBcolumn, that can store XMLs and as well XMLszipped. 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 并进行XMLs压缩。这些是客户的要求，无法更改。表格将被创建，我必须阅读和处理 .csv 文件中的一些信息BLOBs。

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

我已经研究过，任何不明确的解决方案都很清楚或对我有用。

The problem I am facing is that to INSERTXMLplain data bigger than 2000 byteswith utl_raw.cast_to_rawusing DBeaveras Database Manager. I received the message:

我现在面临的问题是，对INSERTXML普通的数据比我的大2000 bytes与utl_raw.cast_to_raw使用DBeaver数据库管理器。我收到消息：

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

问题

1. I have researched and UTL_RAWcan't be longer than 2000 bytes
2. it seems that there is another limitation of 4000 bytesfor BLOBsin Oracle

1. 我已经研究过并且UTL_RAW不能超过2000 bytes
2. 似乎在 Oracle中还有另一个限制4000 bytesforBLOBs

What could I do for those cases?

对于这些情况，我可以做什么？

采纳答案by Arkadiusz ?ukasiewicz

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 中的任何其他数据类型都大。它们就像文件系统上的常规文件。为了写入文件系统上的文件，您必须

1. open the file for writing
2. truncate the file if you wish to start filling it from scratch
3. read your source data in chunks in a loop
4. append your data chunks to the file in the same loop, one by one
5. close the file

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

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 定位器”。你需要

1. open/initialize the LOB locator
2. truncate the LOB contents, if you wish to start filling it from scratch
3. append your data chunks to the LOB contents in a loop, one by one
4. close the LOB locator

1. 打开/初始化 LOB 定位器
2. 截断 LOB 内容，如果你想从头开始填充它
3. 将您的数据块一个接一个地附加到循环中的 LOB 内容中
4. 关闭 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:

一个解释：

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 aaaof 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);

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的内容，如果你想从头开始加油吧...这是通过完成empty_blob()呼叫insert。
4. 将您的数据块附加到循环中的 LOB 内容，一个接一个 = 此处只有一次迭代dbms_lob.writeappend()，仅将一个aaa长度utl_raw.length(aaa)（最大 32767）的块附加到 LOB 中v_b
5. 关闭 LOB 定位器 = dbms_lob.close(LOB_LOC=>v_b);

回答by Stawros

Function utl_raw.cast_to_rawconverts 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.CONVERTTOBLOBprocedure.

函数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;