5 Replies Latest reply: Dec 30, 2012 2:57 AM by ascheffer RSS

    blob to xml is there any other fast way without decpricated functions.

    947771
      Hi,

      1)please tel me is there any good way of getting xml from blob column or this is good? (function should not be deprecated or future it should not be discuntinue and it should be efficeant way of getting value)
      2) also tel me which csid is good defalult DBMS_LOB.DEFAULT_CSID or any other

      and storeage will be blob that i can not change.

      create or replace
      FUNCTION BLOB2CLOB(L_BLOB BLOB) RETURN CLOB IS
      L_CLOB CLOB;
      L_SRC_OFFSET NUMBER;
      L_DEST_OFFSET NUMBER;
      L_BLOB_CSID NUMBER := DBMS_LOB.DEFAULT_CSID;
      V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
      L_WARNING NUMBER;
      L_AMOUNT NUMBER;
      BEGIN
      DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
      L_SRC_OFFSET := 1;
      L_DEST_OFFSET := 1;
      L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
      DBMS_LOB.CONVERTTOCLOB(L_CLOB,
      L_BLOB,
      L_AMOUNT,
      L_SRC_OFFSET,
      L_DEST_OFFSET,
      1,
      V_LANG_CONTEXT,
      L_WARNING);
      RETURN L_CLOB;
      END;



      '<numbers>
      <number id= "1">one</number>
      <number id= "2">two</number>
      <number id="10">ten</number>
      <number id= "6">six</number>
      </numbers>'

      above xml is in abc table col blobcol (type is blob)

      select XMLCAST(XMLQuery ('for $num in /numbers/number
      where number($num/@id) > 5
      order by number($num/@id)
      return $num/@id
      passing by value XMLPARSE(CONTENT blob2clob(BLOBCOL) returning content)
      AS VARCHAR2(100))
      from abc;


      yours sincerly.

      Edited by: 944768 on Dec 29, 2012 1:21 AM