Insert BLOB test string bigger than 2000 or 4000 bytes

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.

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

Problems

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

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

What could I do for those cases?

---

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

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

open/initialize the LOB locator

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

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

close the LOB locator

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;

An explanation:

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

open the LOB locator for writing = 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 .

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

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

---

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.

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

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;

链接地址: http://www.djcxy.com/p/88372.html

上一篇: 我如何在CSS中转义单引号或双引号？

下一篇: 插入大于2000或4000字节的BLOB测试字符串