Re: Copy Blob data into Varchar2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 26 Mar 2020 10:59:39 +0000
Message-ID: <LNXP265MB1562E37BD36B43A6CB89EF68A5CF0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

Someone needs to tell the third party application supplier that it's a bad idea to store character data in a BLOB. If they store it in a CLOB then it will translated into a two-byte fixed characterset so that the person who inserts the data and the person who queries the data could use different character sets and still see the correct data (Oracle will translate both ways between the two-byte fixed and whatever client character set is in use).

If they store the characters as a BLOB then Oracle doesn't do any translation- after all, it's not "character" data, it's raw data - so the person reading the data may find that they see garbage because they're trying to read the data using a different encoding than that used by the original writer.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org> Sent: 26 March 2020 00:54
To: oracle-l_at_freelists.org; Mladen Gogala Subject: Re: Copy Blob data into Varchar2

Mladen

Yes your point make sense as if data is real binary then select will display junk character. This current data is somehow stored by a third party application but has all text data. Not sure if this is kept blob due to some future requirement.

Tx
Sanjay

On Wednesday, March 25, 2020, 01:12:41 PM EDT, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 26 2020 - 11:59:39 CET