need convert BLOB to varchar in oracle but need 400 bytes characters

Question

I have BLOB datatype data in table BUT as per my requirement need varchar2 data type convert this data therefore I using utl_raw.cast_to_varchar2(dbms_lob.substr(SC.SER_DATA,2000,1)

but when using utl_raw.cast_to_varchar2(dbms_lob.substr(SC.SER_DATA,2000,1) is working fine but need 4000 byte data when used utl_raw.cast_to_varchar2(dbms_lob.substr(SC.SER_DATA,4000) showing error

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

Answer 1

Since raw can only be 2000 characters, have you tried concatenating 2 chunks of 2000 characters ?

utl_raw.cast_to_varchar2(dbms_lob.substr(SC.SER_DATA,2000,1) ||
utl_raw.cast_to_varchar2(dbms_lob.substr(SC.SER_DATA, 4000, 2001))