ORA-02291 while attempting to install a workspace from an earlier Apex version

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

First of all many thanks to Patrick Wolf for explaining me the reason of this bug and contributing a quick workaround.

The story goes as follows:
if you install Apex 4.2 on a fresh database or upgrade an installation of Apex 4.1 where there are no existing workspaces and you plan to import them later from saved files exported from earlier versions, your plan is going to fail miserably showing an error page like this:

The failure may happen either when creating users or groups, if there are no groups defined, it will fail when attempting to create users.

The reason lies in a new API parameter introduced in Apex 4.2 that is obviously missing in the old export files.
As you can imagine, by manually adding the missing parameter to the old dump files, does the trick.
Locate the following API call in the workspace dump files (parameter values of course will be different for you):

and make sure to add the line as shown in the picture (don't forget to add the comma after the last parameter.

p_allow_restful_services_yn => 'Y'

Oracle also made available patch 14777994 downloadable from My Oracle Support to fix this problem.


See message translations for ORA-02291 and search additional resources.

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.