Pages

Wednesday, May 25, 2022

ORA-02291: integrity constraint (WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found - When Importing Workspace In APEX configured with ORDS & WebLogic

When creating users during importing workspace in APEX, got ORA-02291 error like following
ORA-02291: integrity constraint (APEX_200100.WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 2067
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_API", line 339
ORA-06512: at line 2
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.
This error occurs because pre-created user groups (SQL Developer, etc) do not have the same IDs in two APEX instances. It was reported as bug of APEX 4.2.1 when ORDS is used and deployed with WebLogic. But it also reported on APEX 18.1 and I got it on my APEX  20.1.

As a workaround, it can fixed by remove group assignment from the "create_fnd_user" statement in the workspace import SQL script as following,

Original SQL
begin
wwv_flow_fnd_user_api.create_fnd_user (
  p_user_id                      => '2471724602488540',
  p_user_name                    => 'DEVELOPER01',
  p_first_name                   => 'Tom',
  p_last_name                    => 'Scott',
  p_description                  => '',
  p_email_address                => 'user01@dbaplus.ca',
  p_web_password                 => '1CE93BEEE43FF616144AC66B076A57D7729B7F53E08230A1D344DF88CF52CC2B1D2D7383BB874B794EAB3841BA9FB466B759DC33E3C2C6E8C8C2A5D7C611F5D7',
  p_web_password_format          => '5;5;10000',
  p_group_ids                    => '1794918882609493:1795050459609497:1795182004609497:',
  p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
  p_default_schema               => 'APP_SCHEMA',
  p_account_locked               => 'N',
  p_account_expiry               => to_date('202002030936','YYYYMMDDHH24MI'),
  p_failed_access_attempts       => 0,
  p_change_password_on_first_use => 'Y',
  p_first_password_use_occurred  => 'Y',
  p_allow_app_building_yn        => 'Y',
  p_allow_sql_workshop_yn        => 'Y',
  p_allow_websheet_dev_yn        => 'Y',
  p_allow_team_development_yn    => 'Y',
  p_allow_access_to_schemas      => '');
end;
/
New statement
begin
wwv_flow_fnd_user_api.create_fnd_user (
  p_user_id                      => '2471724602488540',
  p_user_name                    => 'DEVELOPER01',
  p_first_name                   => 'Tom',
  p_last_name                    => 'Scott',
  p_description                  => '',
  p_email_address                => 'user01@dbaplus.ca',
  p_web_password                 => '1CE93BEEE43FF616144AC66B076A57D7729B7F53E08230A1D344DF88CF52CC2B1D2D7383BB874B794EAB3841BA9FB466B759DC33E3C2C6E8C8C2A5D7C611F5D7',
  p_web_password_format          => '5;5;10000',
  p_group_ids                    => '',
  p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
  p_default_schema               => 'APP_SCHEMA',
  p_account_locked               => 'N',
  p_account_expiry               => to_date('202002030936','YYYYMMDDHH24MI'),
  p_failed_access_attempts       => 0,
  p_change_password_on_first_use => 'Y',
  p_first_password_use_occurred  => 'Y',
  p_allow_app_building_yn        => 'Y',
  p_allow_sql_workshop_yn        => 'Y',
  p_allow_websheet_dev_yn        => 'Y',
  p_allow_team_development_yn    => 'Y',
  p_allow_access_to_schemas      => '');
end;
/

No comments: