Re: Copy Blob data into Varchar2

From: Mladen Gogala <>
Date: Wed, 25 Mar 2020 13:11:38 -0400
Message-ID: <>

The only type that realistically can be converted to VARCHAR2 is CLOB. Converting things like JPEG or MP3 to VARCHAR2 would take OCR or even more complex speech recognition to do. Maybe Alexa or Siri could do it for you?  Personally, I would use pathologically eclectic rubbish lister to do that line by line. I would read CLOB field into $clob and then do something like this my _at_clob=split "\n",$clob;  to split it into lines. I would then re-insert all the lines from the _at_clob array as varchar2 columns.

On 3/24/20 11:14 PM, Sanjay Mishra (Redacted sender smishra_97 for DMARC) wrote:
> Can someone share any function which can allow to insert BLOB data
> into Varchar2 column? Most of the BLOB data is less than 32K but
> selecting the BLOB using dbms_lob.substr( l_cur.mylob, 32000, 1 ) or
> utl_raw.cast_to_varchar2 gave error
> ORA-06502: PL/SQL: numeric or value error: character string buffer too
> small
> Can someone share an example to convert data from BLOB to varchar ?
> Tx
> Sanjay

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Received on Wed Mar 25 2020 - 18:11:38 CET

Original text of this message