Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

BLOB to VARCHAR2 conversion failing.

Atif IqbalApr 8 2020 — edited Apr 9 2020

I'm trying to fetch the BLOB field from sqlplus using below query but getting error

SQL Query ::

select

utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD,2000,1)) ,

utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD,4000,2000)) ,

utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD,6000,4000))

from BLOB_TABLE where id='342' ;

Error ::

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

ORA-06512: at line 1

length of field is :: 4844

If I comment below line and run then its working fine.

utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD,4000,2000)) ,

Could you please let me know why the sql is failing for above line.

I need to spool the file from one DB and upload it into another DB.

Added on Apr 8 2020

#general-database-discussions

10 comments

6,816 views