How to Avoid "Raw Variable Length Too Long" Errors in SQL Developer

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

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

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

I solved issue by using below query -

SELECT ID,
utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE_VALUE, 2000, 1)) as First_Part,
utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE_VALUE, 4000, 2001)) as Second_Part,
utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE_VALUE, 6000, 4001)) as third_Part,
FROM XYZ t1 
LEFT OUTER JOIN ABC t2 ON 
t1.SPEC_ID = T2.SPEC_ID

Insert BLOB test string bigger than 2000 or 4000 bytes

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

ORA-12899 value too large for column despite of same length

You're seeing the difference between character and byte length semantics:

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. You can use the BYTE qualifier, for example VARCHAR2(10 BYTE), to explicitly give the maximum length in bytes. If no explicit qualifier is included in a column or attribute definition when a database object with this column or attribute is created, then the length semantics are determined by the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the object.

If your session is using byte semantics then the column in your table will default to that:

select value from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

VALUE                                  
----------------------------------------
BYTE                                    

create table t42(text varchar2(5));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
B

That is the same as explicitly doing:

create table t42(text varchar2(5 byte));

If your source data is five characters but includes any multibyte characters then the number of bytes will exceed five:

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

SQL Error: ORA-12899: value too large for column "SCHEMA"."T42"."TEXT" (actual: 6, maximum: 5)

Which is what you're seeing. When you insert the values from your other table you are filter on the length of the values, but length() counts characters rather than bytes. There is a lengthb() function that does count bytes. If you check the byte length of the 30-character value you're selecting you'll see it is in fact 31 bytes, so one of those characters is multibyte.

with t42 (text) as (
  select 'Hello' from dual
  union all select 'Señor' from dual
  union all select 'Testing  - HLC/TC Design Corre' from dual
)
select text, length(text) as chars, lengthb(text) as bytes, dump(text, 16) as hex
from t42;

TEXT                            CHARS BYTES HEX                                                                                                      
------------------------------- ----- ----- ----------------------------------------------------------------------------------------------------------
Hello                               5     5 Typ=1 Len=5: 48,65,6c,6c,6f                                                                               
Señor                               5     6 Typ=1 Len=6: 53,65,c3,b1,6f,72                                                                            
Testing  - HLC/TC Design Corre     30    31 Typ=1 Len=31: 54,65,73,74,69,6e,67,c2,a0,20,2d,20,48,4c,43,2f,54,43,20,44,65,73,69,67,6e,20,43,6f,72,72,65

From the dump() values you can see that after Testing (54,65,73,74,69,6e,67) and before the space and dash (20,2d) you have c2,a0, which is the UTF-8 multibyte non-breaking space character. (You often see that, along with curly quotes and other non-ASCII-range characters, in text that has been copied from,. say, a Word document).

You can either change your insert to filter on LENGTHB(column1)=30 (which will exclude the row you currently find), or change your column definition to 30 characters instead of 30 bytes:

drop table t42;

Table T42 dropped.

create table t42(text varchar2(5 char));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
C

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

1 row inserted.

Or replace any unexpected multibyte characters with single-byte equivalents, if that's possible and makes sense for your data; in this case a normal space might work, but with any substitution you are destroying information that might actually be important.

Extract BLOB with length greater than 4000 in Oracle SQL

I found a simple way to get the output by using to_clob function

select to_clob(BYTES_) from ACT

Error : ORA-01704: string literal too long

What are you using when operate with CLOB?

In all events you can do it with PL/SQL

DECLARE
  str varchar2(32767);
BEGIN
  str := 'Very-very-...-very-very-very-very-very-very long string value';
  update t1 set col1 = str;
END;
/

Proof link on SQLFiddle

---

Related Topics