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
