Tuesday, October 27, 2009

Windows Oracle Apex upgrade on XE from 2.2 to 3.2

I was tempted to upgrade my Apex installation to 3.2 from default (2.2) that comes along with Oracle XE database.
I followed the instructions on
http://www.oracle.com/technology/products/database/application_express/html/3.2_and_xe.html
and
http://blogs.oracle.com/SanthoshK/2008/09/oracle_xe_apex_3x_installation.html

But finally got struck during the step of loading images. I had kept the APEX install on D:

SQL> @apxldimg.sql D:\Downloads\Oracle_Tools\apex_3.2

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'D:\Downloads\Oracle_Tools\apex_3.2/apex/images'

Directory created.

declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
Access is denied.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 15


I tried several alternatives like changing the apxldimg.sql script and googled atleast 2 hours before trying a simple solution.

I copied the apex installation folder from D: drive to
C:\apex_32 and reran the step

SQL> @apxldimg.sql c:\apex_32

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:\apex_32/apex/images'

Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.

timing for: Load Images
Elapsed: 00:01:01.59

Directory dropped.

Now I can see all the images on http://localhost:8080/apex
Apparently, there are quirks involving any other drive than C: that need to be resolved by Oracle. But for the time this is the quickest way to resolve and enjoy the functionalities of Apex 3.2