This discussion is archived
1 Reply Latest reply: Aug 23, 2013 3:07 AM by odie_63 RSS

a blob column in xml

HenkB Newbie
Currently Being Moderated


Hi,

 

I have a blob column in some table. It is for example an jpg image. The size is more than 32k (for example size:152287 with dbms_lob.getlength)

 

How can I get this column in a xml document because I want to use the xml file for importing in a different Oracle schema.

 

Thanks in advance,

 

Henk

  • 1. Re: a blob column in xml
    odie_63 Guru
    Currently Being Moderated

    Extracting the BLOB is not the hardest part, SQL/XML function such as XMLElement() can read the content natively and convert it to a text node in xs:hexBinary format :

    SQL> select xmlelement("Image", image)

      2  from test_jdbc_blob

      3  where img_id = 'Koala.jpg' ;

     

    XMLELEMENT("IMAGE",IMAGE)

    --------------------------------------------------------------------------------

    <Image>FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001F

    Other option is to convert the BLOB to base64 and feed it to an element similarly, though it requires a little more code.

    DBMS_XMLDOM has some methods to manipulate large binary streams too.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points