View BLOB data which contains text in XML format

Question

How to view a BLOB data, can i export it to textfile? . i am using Oracle SQL developer 5.1. When i tried select utl_raw.cast_to_varchar2(dbms_lob.substr(COLNAME)) from user_settings where =

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

the BLOB contains text in XML format

Answer 1

BLOB data is typically just... a binary blob of data.

Sure, you can export it to a text file by converting it to some kind of text representation... But what if it is an image?

jaganath: You need to sit down and figure out what it is you're dealing with, and then find out what it is you need to do.

Answer 2

From the error message it seems that the blob length is too long to fit into a varchar. You could do the conversion in your application code and write the XML into a String or file.

Answer 3

You could look at DBMS_LOB.CONVERTTOCLOB

But if it is XML, why store it in a BLOB rather than an XMLType (or CLOB)