Apply These 5 Steps to Upgrade Oracle Apex from 5.0 to 5.1

Upgrade Oracle Application Express from 5.0 to 5.1
Below are the steps to upgrade Oracle Apex from 5.0 to 5.1 version. As previously I already posted to Install Oracle Applicaiton Express 5.0 on Oracle 12c PDB. After downloading the Oracle Apex from Oracle extract the zip file and change the current directory to the directory where you extracted and then connect to Oracle database using SYS credentials as shown below.

Run CMD command and then type the commands as shown below to change the directory and to connect to Oracle database.

CD D:SoftwareApex5_1apex_5.1_enapex

sqlplus /nolog

Connect as below if using Oracle Database 12c PDB:

conn sys/yourpsw@orcl12c as sysdba

Alter Session Set Container = pdb12c;
Alter Database Open;

Else connect as shown below:

conn sys@Oradb11g as sysdba
Enter Password: *****

Then follow these 5 steps to upgrade to Oracle Apex 5.1. But remember if your tablespaces are different from SYSAUX then change accordingly in below commands.

1. Run apexins1.sql script.

[code type=”SQL”]@apexins1.sql SYSAUX SYSAUX TEMP /i/[/code]

2. Run apexins2.sql script.

[code type=”SQL”]@apexins2.sql SYSAUX SYSAUX TEMP /i/[/code]

3. Run apexins3.sql script.

[code type=”SQL”]@apexins3.sql SYSAUX SYSAUX TEMP /i/[/code]

4. Run apex_epg_config.sql script and set the appropriate directory.

[code type=”SQL”]@apex_epg_config.sql D:SoftwareApex5_1apex_5.1_en[/code]

5. Run the below script.

[code type=”SQL”]BEGIN
   DBMS_EPG.
    set_dad_attribute (dad_name     => ‘APEX’,
                       attr_name    => ‘cgi-environment-list’,
                       attr_value   => ‘REQUEST_PROTOCOL=https’);
   DBMS_EPG.
    set_dad_attribute (
      dad_name     => ‘APEX’,
      attr_name    => ‘cgi-environment-list’,
      attr_value   => ‘HTTP_HOST=external-name:external-port’);
   COMMIT;
END;
/

DECLARE
   ACL_PATH   VARCHAR2 (4000);
BEGIN
   SELECT ACL
     INTO ACL_PATH
     FROM DBA_NETWORK_ACLS
    WHERE HOST = ‘*’ AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

   IF DBMS_NETWORK_ACL_ADMIN.
       CHECK_PRIVILEGE (ACL_PATH, ‘APEX_050100’, ‘connect’)
         IS NULL
   THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL_PATH,
                                            ‘APEX_050100’,
                                            TRUE,
                                            ‘connect’);
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_NETWORK_ACL_ADMIN.
       CREATE_ACL (‘power_users.xml’,
                   ‘ACL that lets power users to connect to everywhere’,
                   ‘APEX_050100’,
                   TRUE,
                   ‘connect’);
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (‘power_users.xml’, ‘*’);
END;
/

COMMIT;

DECLARE
   ACL_PATH   VARCHAR2 (4000);
BEGIN
   — Look for the ACL currently assigned to ‘localhost’ and give APEX_050100
   — the “connect” privilege if APEX_050100 does not have the privilege yet.
   SELECT ACL
     INTO ACL_PATH
     FROM DBA_NETWORK_ACLS
    WHERE HOST = ‘localhost’ AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

   IF DBMS_NETWORK_ACL_ADMIN.
       CHECK_PRIVILEGE (ACL_PATH, ‘APEX_050100’, ‘connect’)
         IS NULL
   THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL_PATH,
                                            ‘APEX_050100’,
                                            TRUE,
                                            ‘connect’);
   END IF;
EXCEPTION
   — When no ACL has been assigned to ‘localhost’.
   WHEN NO_DATA_FOUND
   THEN
      DBMS_NETWORK_ACL_ADMIN.
       CREATE_ACL (‘local-access-users.xml’,
                   ‘ACL that lets users to connect to localhost’,
                   ‘APEX_050100’,
                   TRUE,
                   ‘connect’);
      DBMS_NETWORK_ACL_ADMIN.
       ASSIGN_ACL (‘local-access-users.xml’, ‘localhost’);
END;
/

COMMIT;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.
    APPEND_HOST_ACE (
      HOST   => ‘*’,
      ace    => xs$ace_type (privilege_list   => xs$name_list (‘connect’),
                             principal_name   => ‘APEX_050100’,
                             principal_type   => xs_acl.ptype_db));
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.
    APPEND_HOST_ACE (
      HOST   => ‘localhost’,
      ace    => xs$ace_type (privilege_list   => xs$name_list (‘connect’),
                             principal_name   => ‘APEX_050100’,
                             principal_type   => xs_acl.ptype_db));
END;
/
[/code]
And now you successfully completed the up-gradation of Oracle Application Express from 5.0 to 5.1.

You can check and set the http port if not already set, as shown below:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8383);

If you have any issues during installation please comment below. Thanks.