How to overcome ORA-20002

Introduction

Oracle EBS DBAs often face frustration when seemingly simple tasks take an unexpectedly long time to resolve. One common example is assigning a responsibility, only to encounter a generic error. In this blog, we’ll explore this issue in detail and provide a solution to address it effectively.

Issue: –

When assigning responsibility to a user using the form (security user define), the following error gets displayed

Error :-
ORA-20002: [WF_NO_ROLE] NAME=FND_RESP|FND|ECC_DEVELOPER_RESP|NNN ORIG_SYSTEM=FND_RESP ORIG_SYSTEM_ID=51285 has been detected in FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT

Cause:-
SQL> select name, orig_system_id from wf_local_roles where display_name = ‘<Responsibility name>’;

NAME ORIG_SYSTEM_ID

————— —————

The NAME column in WF_LOCAL_ROLES is quite different than the DISPLAY_NAME in the same table for the same custom responsibility. The NAME is the internal name for a responsibility in the WF_LOCAL_ROLES table whereas the DISPLAY_NAME is the responsibility name shown on the home page upon users accessing it.

The NAME of this responsibility is truncated in the WF_LOCAL_ROLES table where the DISPLAY_NAME of this responsibility is in tact.

Solution:-

Back up the table WF_LOCAL_ROLES first for recovery if needed as direct application table updates from sqlplus are not supported.
Run the following update sql as APPS_user

update WF_LOCAL_ROLES set NAME=’FND_RESP|EPGL|EPS_STAFF_DEVELOPMENT_PROGRAMS|STANDARD.’where NAME=’the previously inconsistent custom responsibility name being truncated’;

Verify the update values by using below command

select name, orig_system_id from wf_local_roles where display_name = ‘<The custom Responsibility name>’;

Now SYSADMIN user is able to assign this responsibility (the DISPLAY_NAME in WF_LOCAL_ROLES) to users.

Conclusion:-
We hope this journey has provided valuable insights and helped you address the challenges outlined above. As you apply these learnings to your own technical endeavors, we invite you to share your thoughts and experiences. Here’s to building a future of efficient, integrated, and seamlessly managed Oracle environments!

Surendran Umapathy