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 & 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 & 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 &)
Error at line 3
no rows selected
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 & 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 & 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 &)
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 |