Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Read Oracle BLOB to XML String

 
Ritus Singh
Greenhorn
Posts: 4
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I am trying to read a blob from oracle database and need to convert it into xml string.
Howver it appears in a weird format, i did some googling around and found that the data stored in blob is in format UCS2 and is not getting converted into UTF-8.
I am not good at databse hence i am using java to convert the data.

below are few code snippets I worked on.
Snippet 1:
String sql = "SELECT BIN FROM TEST_PAYLOAD WHERE ID='123'";
PreparedStatement stmt = conn.prepareStatement(sql);

// fetch and display the results
ResultSet rs = stmt.executeQuery();

while( rs.next() ) {
String xmlText = new String(rs.getBytes("BIN"),"UTF-8");
System.out.println(xmlText);}
Output 1:?B? ? N? A DataElement? A A? Ahttp://www.example.org?N?? A?A? ? G? A bpmInstanceId? G?bpmn:30006?A? ? Z A sortTS? Z 2013-09-26T00:12:56.341+5:30?A? ?

Snippet 2:select utl_raw.cast_to_varchar2(dbms_lob.substr(BIN)) from MEDIATOR_PAYLOAD WHERE OWNER_ID='97EFECD027A411E3BFD0A9FED3F009B7' AND TYPE='payload';
�B� �
N�ADataElement� A A� ��A�� G� �G�.........


Please help..

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
BLOBs are binary data and aren't meant to be converted into text. You really should use a CLOB which is meant for character data.
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ritus Singh wrote:Howver it appears in a weird format, i did some googling around and found that the data stored in blob is in format UCS2 and is not getting converted into UTF-8.


So the data is text which has been encoded using UCS2?

String xmlText = new String(rs.getBytes("BIN"),"UTF-8");


Here you wrote code which says "Take the bytes and convert them to a String assuming that they were encoded using UTF-8." Which they weren't. So that could be why your data is being garbled (assuming that the data in your database was in fact encoded using UCS2). Where you say "not getting converted into UTF-8": you don't want to convert anything into any charset at all. You're converting from bytes to chars, which means you're converting from some charset.

The charset which you want to use is probably "UTF-16" or one of its variants; see Supported Encodings for other options.

Or you might be better off to follow Jeanne's advice and read the column as a Clob. First check your database and see whether the column is really text or binary.
 
Ritus Singh
Greenhorn
Posts: 4
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the quick reply...

It is a product database and due to some issues some imp client data is stuck there which we need to now retrieve.

I will try reading it as clob and post for furthur help..

Once again Thank you
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome, and by the way welcome to the Ranch too! Let us know how your new trials work out.
 
Ritus Singh
Greenhorn
Posts: 4
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello

finally I am able to achieve the xml format which i wanted.I am posting the code for others for furthur help and have a peaceful sleep.

Scenario:I had a Oracle DB where data was stored in BLOB format.I am using Java code to recover the dat and format it into xml format.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great to hear you got it working. Thanks for sharing the code. I didn't know BinXMLProcessor existed because I try not to use database specific APIs.
 
karthick sambanghi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ritus Singh

i have tried your code for converting blob to xml data type. Below is my output of blob data

SQL> SELECT * FROM system WHERE RECID = '100773';

RECID
-------------------------------------------------------------------
XMLRECORD
-------------------------------------------------------------------
100773
3232323333

After running your code i am getting with the below output

1.0
XML IN String format is:
<?xml version = '1.0' encoding = 'UTF-8'?>

Kindly assist me how to convert the above blob data to XML data type.

Thanks in Advance.

 
Ritus Singh
Greenhorn
Posts: 4
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Karthik,

I am not sure on your SQL statement.Does your table system has only one column?


Regards
 
kiran vurandala
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Ritu Singh,

Hope you are doing well. I tried the Java code which you have given. When i executed this i got the following xml only...

1.0
XML IN String format is:
<?xml version = '1.0' encoding = 'UTF-8'?>

It is not retrieving the BLOB data from the table.

Can you please help me on this?

Regards,
Kiran
 
kiran vurandala
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Karthik,

Hope you are doing well. I followed the same code, even i am facing the same issue. Did you got the solution for this. If, yes please let me know the solution?

Regards,
Kiran
 
sreenu valireddi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I hope following query works for you



select UTL_RAW.CAST_TO_VARCHAR2(BLOB_COL) from emp where id='4';

gives varchar2 as a result.



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic