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.

possible duplicate of [How do I edit BLOBs (containing JSON) in Oracle SQL Developer?](http://stackoverflow.com/questions/9218474/how-do-i-edit-blobs-containing-json-in-oracle-sql-developer) — Justin Cave, Mar 16 '12 at 19:21
I updated my answer in the other thread to include a somewhat more complicated approach that is more flexible that using the `utl_i18n.string_to_raw` function. — Justin Cave, Mar 16 '12 at 19:22

Justin Cave · Accepted Answer · 2012-03-16T20:11:46.000

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

This works, but why `rpad`? I need to set `rpad`'s length to the length of the string I am inserting. Is there a way around having to do this by not using `rpad`? — Xonatron, Mar 16 '12 at 19:35
@MatthewDoucette - You shouldn't need to use `RPAD` at all. I was just using it in my example to generate a 3200 character string because I'm too lazy to type in a string that long and it would be a pain to format such a string in a SO post. — Justin Cave, Mar 16 '12 at 19:37

score 0 · Answer 2 · answered Mar 16 '12 at 19:08

0

I guess what the issue is that the data that you are inserting to the column is too big for the datatype of the column. Please check the value you are trying to insert into the column and then update the column datatype.

answered Mar 16 '12 at 19:08

Sachin Kainth

45,256
81
201
304

Are you sure? The BLOB already contains text that is bigger than the text I am trying to insert. To me, that makes it a SQL Developer (interface) issue, not a database issue. – Xonatron Mar 16 '12 at 19:09
It might be related to `utl_i18n.string_to_raw`'s maximum limit, if there is one. – Xonatron Mar 16 '12 at 19:15

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

2 Answers2

Linked