Actually, this error started to be produced in workflow , just after upgrading the database of this environment from 11gR2 to 12cR1.
The database upgrade (running dbua and other stuff) was done by a different company, so that we were not able to check if it is done properly..
However; we were the ones who needed to solve this issue when it appeared :)
Anyways, functional team encountered this error while checking the workflows in Workflow Administrator Web Applications -> Status Monitor, and reported it as follows;
ORA-24033 was basically saying us, there is a queue-subscriber problem in the environment, so we started working with the queues, subscribers and the rulesets.
The analysis showed that, we had 1 ruleset and 1 rule missing in this environment..
select * from
dba_objects
where object_name like 'WF_DEFERRED_QUEUE%'
The following output was produced in a reference environment, on which workflow REQAPPRV was running without any problems.
Fixing Invalid Workflow Rule Sets such as WF_DEFERRED_R and Related Errors on Workflow Queues:ORA-24033 (Doc ID 337294.1)
So what we executed in this context was as follows;
declare
l_wf_schema varchar2(200);
lagent sys.aq$_agent;
l_new_queue varchar2(30);
begin
l_wf_schema := wf_core.translate('WF_SCHEMA');
l_new_queue := l_wf_schema||'.WF_DEFERRED';
lagent := sys.aq$_agent('WF_DEFERRED',null,0);
dbms_aqadm.remove_subscriber(queue_name=>l_new_queue, subscriber=>lagent);
end;
/
commit;
declare
l_wf_schema varchar2(200);
lagent sys.aq$_agent;
l_new_queue varchar2(30);
begin
l_wf_schema := wf_core.translate('WF_SCHEMA');
l_new_queue := l_wf_schema||'.WF_DEFERRED';
lagent := sys.aq$_agent('WF_DEFERRED',null,0);
dbms_aqadm.add_subscriber(queue_name=>l_new_queue, subscriber=>lagent,rule=>'1=1');
end;
/
The following output, on the other hand; was produced in this problematic environment.
As seen above, we had 1 ruleset named WF_DEFERRED_QUEUE_M$1 and 1 rule named WF_DEFERRED_QUEUE_M$1 missing in this problematic environment..
In addition to that, WF_DEFERRED related rulesets were invalid in this problematic environment.
In order to create (validate) these ruleset , we followed 2 MOS documents and executed our action plan accordingly.
Contracts Clause Pending Approval with Error in Workflow ORA-25455 ORA-25447 ORA-00911 invalid character (Doc ID 1538730.1)
l_wf_schema varchar2(200);
lagent sys.aq$_agent;
l_new_queue varchar2(30);
begin
l_wf_schema := wf_core.translate('WF_SCHEMA');
l_new_queue := l_wf_schema||'.WF_DEFERRED';
lagent := sys.aq$_agent('WF_DEFERRED',null,0);
dbms_aqadm.remove_subscriber(queue_name=>l_new_queue, subscriber=>lagent);
end;
/
commit;
declare
l_wf_schema varchar2(200);
lagent sys.aq$_agent;
l_new_queue varchar2(30);
begin
l_wf_schema := wf_core.translate('WF_SCHEMA');
l_new_queue := l_wf_schema||'.WF_DEFERRED';
lagent := sys.aq$_agent('WF_DEFERRED',null,0);
dbms_aqadm.add_subscriber(queue_name=>l_new_queue, subscriber=>lagent,rule=>'1=1');
end;
/
commit;
declare
lagent sys.aq$_agent;
begin
lagent := sys.aq$_agent('APPS','',0);
dbms_aqadm.add_subscriber(queue_name=>'APPLSYS.WF_DEFERRED_QUEUE_M',
subscriber=>lagent,
rule=>'CORRID like '''||'APPS'||'%''');
end;
/
declare
lagent sys.aq$_agent;
begin
lagent := sys.aq$_agent('APPS','',0);
dbms_aqadm.add_subscriber(queue_name=>'APPLSYS.WF_DEFERRED_QUEUE_M',
subscriber=>lagent,
rule=>'CORRID like '''||'APPS'||'%''');
end;
/
So what we did was to;
Remove and add back the subscriber/rules to the WF_DEFERRED queue
+
Add the subscriber and rule back into the WF_DEFERRED_QUEUE_M queue. (if needed we could remove the subscribe before adding it)
By taking these actions; the ruleset named WF_DEFERRED_QUEUE_M$1 and the rule named WF_DEFERRED_QUEUE_M$ were automatically created and actually, this fixed the ORA-24033 error in REQAPPRV :)
