Query to get Oracle Alerts email contents and Email Stuck

Query to get Oracle Alerts email contents and Email Stuck

This will help in testing alert email in test instance. This query provide XML generated by oracle - which can be extracted to review the contents to be send in email.




select corrid 
     , enq_time 
     , decode(wno.state
          , 0, '0 = Pending in mailer queue',
            1, '1 = Pending in mailer queue',
            2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
            3, '3 = Exception', 4,'4 = Wait', to_char(state)) State
      , to_char(DEQ_TIME)
      , wno.user_data.TEXT_VC
  from wf_notification_out wno
 where corrid like 'APPS:ALR%'
   and upper(wno.user_data.TEXT_VC) like upper('%Alert Subject%')
  order by enq_time desc