Geek Answers Handbook

View blob data containing XML text

How to view BLOB data, is it possible to export it to a text file? 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 <fieldname>=...

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

BLOB contains text in XML format.

+5
source share
5 answers

To view XML data stored as a BLOB, follow these steps:

  • Open the table view by selecting the Data tab
  • Double-click the column field value and the pencil button appears in the field. Press the pencil button.Pencil button
  • The "Change value" window will open, check the box "View as: Text". From this window you can also save any required file data.Select the view type

PS: Oracle SQL Developer 3.1.05

+6

- . 1500 substr, .

select utl_raw.cast_to_varchar2(dbms_lob.substr(colname,1500))
from user_settings where <row_id>=...
+3

BLOB ... .

, , - ... , ?

jaganath: , , , .

+1

DBMS_LOB.CONVERTTOCLOB

XML, BLOB, XMLType ( CLOB)

+1

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

0
source

More articles:


All Articles