Last updated on AUGUST 31, 2010
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2Information 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
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms