Installing Translated Versions of APEX on RDS for Oracle

We finally found the solution to install translated versions of Apex in an RDS for Oracle database as the Master account without error.

1. As documented, grant all the admin privileges to the Master account. As an example here the account is named ORACLE :

exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'ORACLE', 'EXECUTE', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOB', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_ASSERT', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_OUTPUT', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SCHEDULER', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('HTP', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('OWA', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('WPG_DOCLOAD', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_CRYPTO', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_METADATA', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SQL', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('UTL_SMTP', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_NETWORK_ACL_ADMIN', 'ORACLE', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('SESSION_PRIVS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_USERS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_NETWORK_ACL_PRIVILEGES', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_NETWORK_ACLS', 'ORACLE', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_REGISTRY', 'ORACLE', 'SELECT', true);

EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role;
grant APEX_ADMINISTRATOR_ROLE to ORACLE;

2. Before running the installation script load_xxxx.sql, edit the sql file and add the following anonymous block to activate the Apex administrator security group before the other scripts :

begin
   wwv_flow_security.g_security_group_id := 10;
end;
/

... as an example, the apex/builder/fr/load_fr.sql file must be edited like so with the anonymous block :

...
set define '^'
set concat on
set concat .
set verify off
set termout off
set termout on

declare
    l_schema sys.dba_registry.schema%type;
begin
    sys.dbms_registry.set_session_namespace ( namespace   => 'DBTOOLS' );
    l_schema := sys.dbms_registry.schema( 'APEX' );
    execute immediate 'alter session set current_schema='||sys.dbms_assert.enquote_name( l_schema );
end;
/

begin
    wwv_flow_security.g_security_group_id := 10;
end;
/

@@f4411_fr.sql
@@f4470_fr.sql
@@f4000_fr.sql
@@f4020_fr.sql
@@f4050_fr.sql
...

... then follow the usual Oracle Documentation instructions (https://docs.oracle.com/en/database/oracle/apex/23.2/htmig/installing-translated-versions-of-apex.html) to execute the installation script. This should fix the "ORA-20001: Package variable g_security_group_id must be set." errors you might get.

If you get the errors "ORA-02291: integrity constraint (APEX_230200.REGION_COL_LOV_FK) violated parent key not found" while running the installation script, you must be sure the APEX-DEV option is available in the option groups associated with the targeted RDS for Oracle database. So basically you must have the option APEX and APEX-DEV available in the option group.

Following these tips (wwv_flow_security.g_security_group_id := 10 and APEX-DEV option), you should be able to install without error the translated version of APEX of your choice using the Master account as if you were connected as SYS AS SYSDBA. These workaround will normally be documented in the official AWS documentation someday.

Thanks for your help Keerthi.

Documentation: Oracle Application Express (APEX) - Amazon Relational Database Service https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.APEX.html Database / Oracle / APEX / Release 23.2 / Installation Guide / Installing Translated Versions of APEX https://docs.oracle.com/en/database/oracle/apex/23.2/htmig/installing-translated-versions-of-apex.html