Insert a BLOB test string greater than 2000 or 4000 bytes

Question

I have a table in oracle with a BLOB column that can store XML files as well as XMLs XMLs. These are customer requirements and cannot be changed. Tables will be created, and I have to read and work with some information inside BLOBs .

I researched, and any of the obscure solutions were clear or worked for me.

The problem I am facing is that for INSERT XML are more than 2000 bytes plain data with utl_raw.cast_to_raw using DBeaver as a database manager. I received a 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 researched and UTL_RAW cannot be longer than 2000 bytes
• there seems to be another 4000 bytes limitation for BLOBs in Oracle

What can I do for these cases?

Answer 1

First you need to understand what LOB is. This is "big data", perhaps more than any other data type in Oracle. They are similar to regular files in the file system. To write a file to a file you need

• open the file for writing
• crop file if you want to start filling it from scratch
• read source data in chunks in a loop
• add your pieces of data to the file in the same loop, one by one
• close file

More or less the same is true for LOB. In your table, the LOB column (CLOB / BLOB / NCLOB) is just a pointer / link to another place on your disk where the actual data is stored. In standard Oracle expressions, a pointer is called a Large Object Locator. You need

• open / initialize large object locator
• trim the contents of the LOB if you want to start filling it from scratch
• add your chunks of data to the contents of the LOB in a loop, one by one
• close the large object locator

In PL / SQL, it might 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; 

Explanation:

• initialize the LOB locator = insert into blob_test values(1,empty_blob()) returning b into v_b;
• open the LOB locator to write = dbms_lob.open(v_b,dbms_lob.lob_readwrite);
• trim the contents of the LOB if you want to start filling it from scratch ... This is done by calling empty_blob() on insert .
• add your pieces of data to the contents of the LOB in the loop, one after the other = there will be only one iteration of dbms_lob.writeappend() , adding only one aaa fragment of length utl_raw.length(aaa) (maximum 32767) in LOB v_b
• close the locator LOB = dbms_lob.close(LOB_LOC=>v_b);

Answer 2

The utl_raw.cast_to_raw function utl_raw.cast_to_raw data type value to its original value. Obviously, the string length is limited by the VARCHAR2 data type. If you need to convert large text data to LOB, you can use the DBMS_LOB.CONVERTTOBLOB procedure.

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

 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;