oracle - How can I avoid "raw variable length too long" errors in SQL Developer?

Question

I am updating a BLOB with large mount of text and I get this error:

SQL Error: ORA-06502: PL/SQL: numeric or value error: raw variable length too long

Is there any way around it?

The text is 2,670 characters long, being converted via utl_i18n.string_to_raw, as explained in How do I edit BLOBs (containing JSON) in Oracle SQL Developer?, and is all on one line in the query.

Update: The BLOB in question already contains text that is 2,686 characters long, which is longer than the text I am trying to insert.

Answer 1

A RAW is limited to 2000 bytes. If your data is longer than that, you'll need to store it in a CLOB and then convert the CLOB to a BLOB which is, unfortunately, a bit more complicated that the string_to_raw function. Something like this will work assuming you can assign the entire string to a CLOB variable which should work as long as the string is less than 32676 bytes in length. If it's longer than that, you'll need to write to the CLOB in pieces and then convert to a BLOB.

declare
  l_blob        blob;
  l_clob        clob := rpad('{"foo": {"id": "1", "value": "2", "name": "bob"}}',3200,'*');
  l_amt         integer := dbms_lob.lobmaxsize;
  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  l_csid        integer := dbms_lob.default_csid;
  l_ctx         integer := dbms_lob.default_lang_ctx;
  l_warn        integer;
begin
  dbms_lob.createTemporary( l_blob, false );
  dbms_lob.convertToBlob( l_blob,
                          l_clob,
                          l_amt,
                          l_dest_offset,
                          l_src_offset,
                          l_csid,
                          l_ctx,
                          l_warn );
  update json_data
     set data = l_blob;
end;
/