Upgrade Oracle APEX ORA-22288 error resolved

Oracle APEX provides you a very nice and easy platform to build small (or even large) web-based applications within the Oracle APEX framework on top of an Oracle database. For developers who do want to work with Oracle APEX on their own laptop and who do not want to deploy this directly on their workstations operating system there is the option to download a complete Linux operating system with a working APEX installation. One of the things you see with downloading a virtual image is that they are not always up to the latest version and patch-level. In essence this is not an issue because you are using it as a local test and development system.

However, in some cases you might want to be on the latest version of APEX because you would like to work with some of the latest features available. Upgrading APEX is quite easy however there are some things you have to keep in mind to save you some time and some frustration.

The steps to upgrade to APEX 4.0 (and 4.*) are described by Oracle as below:

1) Download the latest version of Oracle APEX

2) Unzip the zip file, preferably in a location with a short path. For example /home/oracle

3) Change your working directory to the unzipped apex directory. For example /home/oracle/apex

4) Login to the database:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter Password:
SYS_Password

5) Execute the first part of the installation:
SQL> @apexins SYSAUX SYSAUX TEMP /i/

6) The previous step will log you out of the database, log back into the database as described above.

7) Execute the below command where APEX_HOME is the location of where you have unzipped the installation software (NOTE1)
SQL> @apxldimg.sql APEX_HOME

8) Execute the below command:
SQL> @apxchpwd

9) Open your browser and check if the installation was a success by opening http://localhost:8080/apex/apex_admin

In esscence these are all the steps you need to complete for your installation / upgrade of Oracle APEX to the latest version. If all is OK without any errors you could be done in a couple of minutes and ready to start developing and testing with the latest version of Oracle APEX. However, there is one small catch to it, refer to NOTE1 below which you need to keep in mind when executing step 7.



NOTE1:
The Oracle documentation states exactly the following:
SQL> @apxldimg.sql APEX_HOME
[Note: APEX_HOME is the directory you specified when unzipping the file. For example, with Windows 'C:\'.]

If you do exactly this you should be fine and everything should be running as expecting. However, you have to read the line carefully. You have to specify the location where you unzipped the file. For example /home/oracle issue is that a lot of people (me included) do not read this correctly and do think that the script will need some other scripts and for this reason you have to state the location where the installation software is located. This can be for example /home/oracle/apex. This is however incorrect.

The installation software will, at a certain point, start looking for the images it needs to load and will extend the given path with /apex/images. If you provide the wrong path (descending into the unzipped apex location) you might get the below error when executing one of the steps:

SQL> @apxldimg.sql /home/oracle
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Directory created.
Directory created.
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed 
The system cannot find the path specified. 
ORA-06512: at "SYS.DBMS_LOB", line 523 
ORA-06512: at "SYS.XMLTYPE", line 287 
ORA-06512: at line 17 
Commit complete.
timing for: Load Images
Elapsed: 00:00:00.03
Directory dropped.
Directory dropped.

While, if you do it correctly you will get the below output:
SQL> @apxldimg.sql /home/oracle
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
. Loading images directory: /home/oracle/apex/images
Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Directory dropped.
timing for: Load Images
Elapsed: 00:02:36.41
SQL>

Meaning, selecting the wrong path, and not following the instructions from Oracle to the letter, even though it is not described very clearly, might result in a situation where your upgrade/installation is not going as you might expect.