Error:-
IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)
SYMPTOMS:-
You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX:
Scoring Engine Harness or IEX: Delinquency Management processes run. You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.
On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -
ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated
CAUSE:-
The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.
Bug 21812472
SOLUTION:-
To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:
set serveroutput on;
declare
BEGIN
execute immediate ('drop sequence IEX.iex_del_buffers_s');
execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '
|| 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');
DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);
EXCEPTION
WHEN OTHERS then
DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||
sqlerrm);
end;
commit;
Output like below:-
New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion
PL/SQL procedure successfully completed.
Commit complete.
