LPX-nnn Errors Caused By SQL Monitor Report When The Bind Value of Target SQL Include Ampersand Character '&' or Less-than Character '<' or Less-than-or-equal Character '<=' or Element Tag Character '<a>'
(Doc ID 2741178.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterInformation in this document applies to any platform.
Symptoms
- LPX-00242 and ORA-19213 and ORA-31011 errors were caused by DBMS_SQLTUNE.report_sql_monitor when the bind value of target SQL include ampersand ('&') character but does not be enclosed in single quotes.
For example:
SQL> drop table table1 purge;
Table dropped.
SQL> create table table1(data VARCHAR2(512));
Table created.
SQL> set escape on
SQL> var bind VARCHAR2(10)
SQL> print :bind
BIND
--------------------------------
SQL> begin
:bind := '1 \& 2';
end;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> print :bind
BIND
--------------------------------
1 & 2
SQL> select /*+ MONITOR */ * from table1 where data like :bind;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 59pqp81stcqca, child number 0
-------------------------------------
select /*+ MONITOR */ * from table1 where data like :bind
Plan hash value: 963482612
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TABLE1 | 1 | 258 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA" LIKE :BIND)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
22 rows selected.
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id=>'59pqp81stcqca') AS report FROM dual;
ERROR:
ORA-31011: XML parsing failed
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18334
ORA-19213: error occurred in XML processing at lines 1
LPX-00242: invalid use of ampersand ('&') character (use &)
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQLTUNE", line 13676
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18430
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18761
ORA-06512: at line 1
no rows selected
SQL> - LPX-00230 and ORA-19213 and ORA-31011 errors were caused by DBMS_SQLTUNE.report_sql_monitor when the bind value of target SQL include Less-than-or-equal '<='.
SQL> begin
:bind := '1 \<\= 2';
end;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> print :bind
BIND
--------------------------------
1 <= 2
SQL> select /*+ MONITOR */ * from table1 where data like :bind;
no rows selected
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id=>'59pqp81stcqca') AS report FROM dual;
ERROR:
ORA-31011: XML parsing failed
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18911
ORA-19213: error occurred in XML processing at lines 1
LPX-00230: invalid character 61 (U+003D) found in a Name or Nmtoken
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQLTUNE", line 14289
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19007
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19338
ORA-06512: at line 1
no rows selected - LPX-00225 and ORA-19213 and ORA-31011 errors were caused by DBMS_SQLTUNE.report_sql_monitor when the bind value of target SQL include element tag, e.g. '<a>'.
SQL> begin
:bind := '1 \<a\> 2';
end;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> print :bind
BIND
--------------------------------
1 <a> 2
SQL> select /*+ MONITOR */ * from table1 where data like :bind;
no rows selected
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id=>'59pqp81stcqca') AS report FROM dual
2 ;
ERROR:
ORA-31011: XML parsing failed
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18911
ORA-19213: error occurred in XML processing at lines 1
LPX-00225: end-element tag "bind" does not match start-element tag "a"
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQLTUNE", line 14289
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19007
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19338
ORA-06512: at line 1
no rows selected
SQL> - LPX-00230 and ORA-19213 and ORA-31011 errors were caused by DBMS_SQLTUNE.report_sql_monitor when the bind value of target SQL include Less-than Character '<'.
SQL> begin
:bind := '1 \< 2';
end;
/ 2 3 4PL/SQL procedure successfully completed.
SQL> print :bind
BIND
--------------------------------
1 < 2SQL> select /*+ MONITOR */ * from table1 where data like :bind;
no rows selected
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id=>'59pqp81stcqca') AS report FROM dual;
ERROR:
ORA-31011: XML parsing failed
ORA-06512: at "SYS.DBMS_SQLTUNE", line 18911
ORA-19213: error occurred in XML processing at lines 1
LPX-00230: invalid character 32 (U+0020) found in a Name or Nmtoken
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQLTUNE", line 14289
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19007
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19338
ORA-06512: at line 1no rows selected
SQL>
Changes
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 |
Changes |
Cause |
Solution |