My Oracle Support Banner

dbms_xmlgen.getXMLType works with VARCHAR2 and CLOB with an '&' but not with LONG (Doc ID 1091822.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Given the string "I like blue & green", dbms_xmlgen.getXMLType fails with errors when the string with '&' is stored in a LONG datatype.

SQL> -- Create the table
SQL>
SQL> create table temp_kws_strings
  2 (short_string    varchar2(2000),
  3  long_string     long,
  4  clob_string     clob);

Table created.

SQL>
SQL> -- Desc the table
SQL> --
SQL> desc temp_kws_strings
  Name          Null?    Type
  ------------- -------- ----------------------------
  SHORT_STRING           VARCHAR2(2000)
  LONG_STRING            LONG
  CLOB_STRING            CLOB

SQL>
SQL> set define ?
SQL>
SQL> -- Insert a row into the table
SQL> --
SQL> insert into temp_kws_strings( short_string, long_string, clob_string )
  2 values
  3 ('I like blue & green', 'I like blue & green', 'I like blue & green');

1 row created.

SQL>
SQL> -- Display row value
SQL> --
SQL> select * from temp_kws_strings;

SHORT_STRING              LONG_STRING               CLOB_STRING
------------------------- ------------------------- -------------------------
I like blue & green       I like blue & green       I like blue & green

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with VARCHAR2
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select short_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;

TEST
----------------------------------------------------------------------
<ROW><SHORT_STRING>I like blue &amp; green</SHORT_STRING></ROW>

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with CLOB
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select clob_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;

TEST
----------------------------------------------------------------------
<ROW><CLOB_STRING>I like blue &amp; green</CLOB_STRING></ROW>

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with LONG
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select long_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 3


no rows selected





Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.