ORA-02291 on FLOWS_030100.WWV_FLOW_FND_GU_INT_U_FK

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following bizarre error message when invoking the Oracle Application Express API procedure: APEX_UTIL.CREATE_USER

ORA-02291: integrity constraint (FLOWS_030100.WWV_FLOW_FND_GU_INT_U_FK) violated
- parent key not found

For instance, suppose you have a registration page where the user can enter his/her data and the apex user is created with an after submit process as follows:

begin
apex_util.create_user(
p_user_name     => :P12_USER_NAME
,p_first_name    => :P12_NAME
,p_last_name     => :P12_SURNAME
,p_email_address => :P12_EMAIL
,p_web_password  => :P12_PASSWORD
,p_group_ids     => apex_util.get_group_id('web_users')
);
end;

Error ORA-02291 that we've got is caused by a combination of factors:

• the value returned by the expression apex_util.get_group_id('web_users') must be not null
• the user name must be already existing

If either condition above is false, APEX_UTIL.CREATE_USER won't return any errors, even if the user name is already existing. If the user is already existing, it seems that the current user attributes are preserved (that is they are not updated with the values supplied in the call, fortunately), but no error is raised whatsoever, which seems to me rather odd, in the end I'd like to know if a certain user name is already present in the same workspace, but it looks like you have to manually check this condition beforehand, by means of a NOT EXISTS validation or a query on the apex dictionary view APEX_WORKSPACE_APEX_USERS.

This behavior has been seen on Apex 3.1.2.

See message translations for ORA-02291 and search additional resources or read more Oracle Apex related articles.