I'm trying to generate a report with clause with and dbms_xmlgen but it gives me error:
ORA-19202
ORA-00942: table or view not exists
ORA-06512 at SYS.DBMS_XMLGEN
I do a complex query (for me) with clause with:
WITH PEPE AS
(
SELECT jobtot,
substr(avg_runtime, 1, 2) || ':' || substr(avg_runtime, 3, 2) AS "AVERAGE",
trunc((sysdate - to_date(begin_time, 'YYYYMMDDHH24MISS')) * 1440) AS minutos_ejecutados,
trim(to_char(trunc(trunc((sysdate -
to_date(begin_time, 'YYYYMMDDHH24MISS')) * 1440) / 60),
'0999')) || ':' ||
trim(to_char(trunc(mod(abs(trunc((sysdate -
to_date(begin_time,
'YYYYMMDDHH24MISS')) * 1440)),
60)),
'09')) AS HHMM,
substr(avg_runtime, 1, 2) * 60 + substr(avg_runtime, 3, 2) AS "AVGRUNTIME"
FROM EXAMPLE_TABLE
WHERE status = 'Executing'
AND (sysdate - to_date(begin_time, 'YYYYMMDDHH24MISS')) * 1440 > 15
AND task <> 'DOG'
AND (sysdate - to_date(begin_time, 'YYYYMMDDHH24MISS')) * 1440 >
2.5 * (substr(avg_runtime, 1, 2) * 60 + substr(avg_runtime, 3, 2))
ORDER BY jobtot DESC
)
SELECT dbms_xmlgen.getXML('SELECT * FROM PEPE') QUERY_XML
FROM DUAL;
Any help please? Thanks