Insert BLOB test string bigger than 2000 or 4000 bytes

Question

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

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?

Answer 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

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

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. 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

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:

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

Answer 2

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;