HOWTO: Avoid Conversion of Escape Codes

Sometimes the OTN Forum site scrambles to much code. The answer given / demonstrated on this site here, was asked on the OTN Forum:

"Problem using extract function (PL/SQL) with """ "

but there is no way to show the proper result on the Forum (even here I had to enable some tricks to show it…)


SQL> show define
define "&" (hex 26)

SQL> set define @

SQL> set serveroutput on

SQL> declare
  2  
  3    xml xmltype := xmltype(
  4    '
  5       <message>Hi There!</message>
  6     ');
  7  
  8    partXML xmltype;
  9    partClob clob;
 10  
 11  begin
 12  
 13    partClob := xml.extract('//root/content/text()').getClobVal(); 
 14    dbms_output.put_line( 'Not Converted: '||partClob );
 15    partXML := xmltype(dbms_xmlgen.convert(xml.extract('//root/content/text()').getClobVal(), dbms_xmlgen.ENTITY_DECODE)); 
 16    dbms_output.put_line( 'Converted    : '||partXML.getClobVal() );
 17  
 18  end;
 19  /

Not Converted: <message>Hi There!</message>
Converted    : Hi There!

PL/SQL procedure successfully completed.


SQL> declare
  2  
  3    xml xmltype := xmltype(
  4    '
  5       <message>"</message>
  6     ');
  7  
  8    partXML xmltype;
  9    partClob clob;
 10  
 11  begin
 12  
 13    partClob := xml.extract('//root/content/text()').getClobVal(); 
 14    dbms_output.put_line( 'Not Converted: '||partClob );
 15    partXML := xmltype(dbms_xmlgen.convert(xml.extract('//root/content/text()').getClobVal(), dbms_xmlgen.ENTITY_DECODE)); 
 16    dbms_output.put_line( 'Converted:     '||partXML.getClobVal() );
 17  
 18  end;
 19  /

Not Converted: <message>"</message>
Converted:     "

PL/SQL procedure successfully completed.

SQL> set define &
SQL>

The answer given is based on an example of former colleague, Peter Boekelaar, while dealing with the same issue…

See also the manuals about:

HTH

M.

Marco Gralike Written by:

7 Comments

  1. Gary
    November 17

    The answer is a forum that allows file attachments (at least text). http://www.quest-pipelines.com allows that.
    But if you think its bad for Oracle developers, imagine how it must be for HTML Q&A forums.

  2. November 17

    “Quest-pipelines” as been eaten by the “Toad”(world). Burp. 😉

    Said that; Until now I haven’t found a decent code WP plugin that didn’t eat every XML Schema I fed it. At least that doesn’t happen on the OTN forum so that is not that bad.

Comments are closed.