Wednesday, November 28, 2018

How to rebuild Work Flow  Queue in Oracle EBS



Work Flow  Queue Rebuild Activity
=========================
1)Stop the queue as below :
rem stop queue
exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

2)Drop the queue as below :
rem drop queue
exec dbms_aqadm.drop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

3)Drop the queue table as below :
rem drop queue table
exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true);

4)Create the queue table  as below :
rem create queue table
begin
dbms_aqadm.create_queue_table
(
queue_table => 'WF_NOTIFICATION_OUT',
queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
sort_list => 'PRIORITY,ENQ_TIME',
multiple_consumers => TRUE,
comment => 'Workflow JMS Topic',
compatible => '8.1'
);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr2= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/

5)Create the queue as below :

rem create queue
begin
dbms_aqadm.create_queue
(
queue_name => 'WF_NOTIFICATION_OUT',
queue_table => 'WF_NOTIFICATION_OUT',
max_retries => 5,
retry_delay => 3600,
retention_time => 86400,
comment => 'Workflow JMS Topics'
);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr4= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/

6)Start the queue as below :
rem start queue
begin
dbms_aqadm.start_queue(queue_name => 'WF_NOTIFICATION_OUT');
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr5= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/
rem start exception queue
begin
dbms_aqadm.start_queue(queue_name => 'AQ$_WF_NOTIFICATION_OUT_E',enqueue => FALSE);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr9= '
||to_char(sqlcode)||' - '||sqlerrm);
end;
/

7)Add subscriber as below :

rem add subscriber
declare
lagent sys.aq$_agent;
subscriber_exist exception;
pragma EXCEPTION_INIT(subscriber_exist, -24034);
begin
lagent := sys.aq$_agent('WF_NOTIFICATION_OUT',null,0);
dbms_aqadm.add_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',subscriber=>lagent, rule=>'1=1');
exception
when subscriber_exist then
dbms_aqadm.alter_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',subscriber=>lagent,rule=>'1=1');
null; -- ignore if we already added this subscriber.
end;
/
commit;

No comments: