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 where 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.