Error using dbms_xmlgen with clause with. I can't generate xml

User_Q6JRK

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

cormaco

You cannot refer to the CTE PEPE in your call to getXML. It is not defined in this context.

You have to pass your original query to getXML.