Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: can anyone provide me script to know the pending notification in oracle wf mailer

  1. #1

    can anyone provide me script to know the pending notification in oracle wf mailer

    can anyone provide me script to know the pending notification in oracle wf mailer

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    To check for pending notifications in the OUT and the DEFERRED queues :

    1) Check for a Particular Notification in the wf_notification_out queue

    select notification_id,msg_state,msg_id,role,corrid,enq_t ime,deq_time
    from (select msg_id, o.enq_time, o.deq_time, msg_state
    ,(select str_value
    from table (o.user_data.header.properties)
    where name = 'NOTIFICATION_ID') notification_id
    , (select str_value
    from table (o.user_data.header.properties)
    where name = 'ROLE') role
    , (select str_value
    from table (o.user_data.header.properties)
    where name = 'Q_CORRELATION_ID') corrid
    from applsys.aq$wf_notification_out o)
    where notification_id=<notification_id>
    and rownum=1;


    2) Check for a Notifications in the wf_notification_out queue

    select name,enqueue_enabled,dequeue_enabled
    from dba_queues
    where name='WF_NOTIFICATION_OUT';

    3) The message is not in ‘WF_NOTIFICATION_OUT’. It can still be queued on the ‘WF_DEFERRED’ queue. Check this queue with:


    select v.msg_id,v.msg_state,v.enq_time,v.deq_time,v.corr_ id
    from applsys.aq$wf_deferred v
    , table(v.user_data.parameter_list) t
    where v.corr_id like 'APPS:%oracle.apps.wf.notification.%';

    For a particular notification , add these 2 clauses to the query :
    and name ='NOTIFICATION_ID'
    and value = <Notification_id>;

    4) Count of Notifications where mail is pending :

    select count(*) from wf_notifications
    where mail_status='MAIL'

  3. #3
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    Delhi, India, India
    Posts
    128
    Dev- i hope you get the correct information..
    Regards,
    -Amit

+ Reply to Thread

Similar Threads

  1. How to test SMTP and IMAP for the Workflow Notification Mailer
    By Hemant in forum Concurrent Processing Server / Workflow
    Replies: 0
    Last Post: 07-04-2012, 12:43 AM
  2. script to check / clear wf mailer queue
    By deviilm17 in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 1
    Last Post: 11-07-2011, 11:45 PM
  3. Check any particular Alert Message email has be pending by Mailer
    By Hemant in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 0
    Last Post: 11-01-2011, 09:48 AM
  4. Script to get all Concurrent Requests pending with CRM
    By Amit in forum SCRIPTS : Concurrent Requests & Concurrent Request Sessions
    Replies: 0
    Last Post: 11-01-2011, 09:47 AM
  5. Steps to start/stop Workflow Notification Mailer from sqlplus
    By Amit in forum Concurrent Processing Server / Workflow
    Replies: 1
    Last Post: 11-01-2011, 09:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics