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"
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