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

Question

Hello I am facing raw variable length too long issue when I select the BLOB tried some answer from stackoverflow but not helping.

Is there any way to get all data from BLOB field? I can adjust if we get data in two or more columns.

Here is my query -

SELECT ID,
utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE_VALUE))
FROM XYZ t1 
LEFT OUTER JOIN ABC t2 ON 
t1.SPEC_ID = T2.SPEC_ID

Here is Error -

ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Tried below answer but not working

numeric or value error: raw variable length too long ORA-06512: at "SYS.UTL_RAW"

Answer 1

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

Answer 2

In SQL, the maximum length for VARCHAR2 is 4000 bytes (unless your database has modified MAX_STRING_SIZE). The maximum length for RAW is 2000 bytes (again if MAX_STRING_SIZE hasn't been changed).

Since you are not specifying a length when calling DBMS_LOB.SUBSTR, it is defaulting to 32767. If you shorten the length to 2000, you should not get an error.

SELECT ID, UTL_RAW.cast_to_varchar2 (DBMS_LOB.SUBSTR (NOTE_VALUE, 2000))
  FROM XYZ t1 LEFT OUTER JOIN ABC t2 ON t1.SPEC_ID = T2.SPEC_ID

Another option is that instead of going from BLOB -> RAW -> VARCHAR2, you could try going from BLOB -> CLOB -> VARCHAR2 so that you can get 4000 characters instead of 2000 characters

SELECT ID, SUBSTR (TO_CLOB (NOTE_VALUE), 1, 4000)
  FROM XYZ t1 LEFT OUTER JOIN ABC t2 ON t1.SPEC_ID = T2.SPEC_ID;