Forum Stats

  • 3,825,176 Users
  • 2,260,477 Discussions
  • 7,896,431 Comments

Discussions

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

User_Q6JRK
User_Q6JRK Member Posts: 44 Blue Ribbon
edited Nov 3, 2021 12:01PM in XML DB

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

Answers