Category Archives: Database

Unexpected error from Oracle DBMS_XMLGEN package

Oracle databases provides an XML package to convert query results into XML in DBMS_XMLGEN. This is a very flexible extension in the way it executes within any SQL statements and can be used in conjunction with normal query to achieve results that is not easy to accomplish with ordinary query. For example, the following query retrieves count of rows for all tables with a column named “strike_price” in a database:

select table_name
, to_number(extractvalue(xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) cnt
from user_tables t
exists (select 'x' from user_tab_cols tc
where tc.table_name=t.table_name
and lower(column_name)='strike_price')

However, after an additional where clause to limit non-zero result, Oracle returned an error:

ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem

It turns out with Oracle database version 18 onward, the same query runs fine. The package that returned error is from a previous version of Oracle.