Oracle Application Express (APEX) 4.1: a migration guide

In this post, I will describe how to migrate from Oracle APEX 3.0 to Oracle APEX 4.1. I will also focus on some encountered problems as well as the new features of Oracle Application Express (APEX) 4.1.

Oracle APEX 4.1 – new features

Let’s start with the new features of Oracle APEX 4.1:

• Improved error handling and user-defined exception processing
• Application Express now supports the use of ROWID for updates, inserts, and deletes as an alternative to specifying primary keys
• Developers can now easily add the capability for end-users to load spreadsheet data into existing tables within an application
• The calendar wizards have been enhanced to include the ability to create an edit page as part of creating the calendar
• Building on their initial introduction in Release 4.0, the look and feel of web sheets in release 4.1 has been substantially improved and the controls redesigned to make it more intuitive for users

A complete list of APEX’s new features can be found here: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-41-new-features-459652.html

Pre migration steps

As described in the introduction, the task is a migration from an apex 3.0 to an apex 4.1 environment. In order to prepare such a migration, it is best practice to start with a database backup and an applicative export as presented below. These steps can be really useful in case of problems or mistakes.

Connection as admin on the « apex_admin » page: http://server.domain:port/pls/apex/apex_admin

The first thing to do before doing any migration command is to export the workspace(s). According to the Oracle documentation, a workspace is “a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private.” In fact a workspace export contains user details such as username/password, default schema, rights, e-mail address, etc.

Then you have to select the workspace you want to export, in this specific case the schema name is « BSANET ».

Depending on your platform, you have to choose between DOS and UNIX file format. Then you simply have to click on “Save File” to dump your workspace.

Application(s) still have to be exported. An application is a collection of pages linked together using tabs, buttons, or hypertext links. To export the application(s) you have to connect to the following URL: http://server.domain:port/pls/apex/

Once connected, you have to click on the application you wish to export. You then need to click on the export/import tool and select “Export>Application” as demonstrated below:

Select the right “File Format” according to your infrastructure and the click “Export Application”.

Finally, before starting to install your new APEX version, it is best practice to also backup your apex directory.

oracle@myserver:/u00/app/ [DEV112] tar -cvf apexbck.tar apex

Apex installation and migration

Once each workspace and application are exported and the directory is backed up, you can start the APEX migration safely.

The first thing to check in order to migrate is your database version. Apex 4.1 is compatible with Oracle database starting from Oracle 10.2.0.3 as specified in the file apexins.sql

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Additionally, it is always good to know which is your current APEX version.

SQL> select version from dba_registry where comp_id='APEX';
VERSION
------------------------------
3.0.0.00.20

Now, you can download APEX from the following URL: http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Copy your Apex zip file you just downloaded in the path you want to install it in (e. g.: ORACLE_BASE/product/) and then unzip it.

oracle@myserver:/home/oracle/ [DEV112] mv apex_4.1.zip /u00/app/oracle/product/
oracle@myserver:/u00/app/oracle/product/ [DEV112] unzip apex_4.1.zip
oracle@myserver:/u00/app/oracle/product/ [DEV112] mv apex apex_4.1.0

Create your tablespace(s) where you want to install the APEX application and the APEX file. In our case we specified the same tablespace, APEX_41.

SQL> create tablespace APEX_41 datafile '/u01/oradata/DEV112/APEX_41.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.

Finally, install APEX with the following script which is located in your “APEX_HOME” directory which in our case is /u01/app/oracle/product/apex_4.1.0. The installer’s arguments are the following:

Arguments:
Position 1: Name of tablespace for Application Express application user
Position 2: Name of tablespace for Application Express files user
Position 3: Name of temporary tablespace
Position 4: Virtual directory for APEX images

SQL> @apexins.sql APEX_41 APEX_41 TEMP /i/
…
…
...Begin key object existence check 12:55:17
...Completed key object existence check 12:55:17
...Setting DBMS Registry 12:55:17
...Setting DBMS Registry Complete 12:55:17
...Exiting validate 12:55:17
timing for: Validate Installation
Elapsed: 00:03:09.62
timing for: Development Installation
Elapsed: 00:11:39.75
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Once the installation is finished, some post-installation tasks have to be performed. If you are using a Database Access Descriptor (DAD), you still have to specify where your images directory is located. If you miss this step, you will get a blank page even if you will probably see some HTML code in the page source:

oracle@atchoum:/home/oracle/ [DEV112] vi /u00/app/oracle/product/10.2_comp/Apache/modplsql/conf/dads.conf
…
…
#Alias /i/  “/u00/app/apex/images/” #Old image directory
Alias /i/  “/u00/app/oracle/product/apex_4.1.0/images/” # New image directory
…
…

If you want to change your old password, you can execute the script « apxchpwd.sql ».

SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.
Enter a password for the ADMIN user              []
Session altered.
...changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.

According to APEX documentation: “By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 or 2.” Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 or 2, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040100 database user. Failing to grant these privileges results in issues with:

• Sending outbound mail in Oracle Application Express. Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email
• Using Web services in Oracle Application Express
• PDF/report printing

In order to avoid those issues, you have to execute the script below:

DECLARE
ACL_PATH  VARCHAR2(4000);
ACL_ID    RAW(16);
BEGIN
— Look for the ACL currently assigned to ‘*’ and give APEX_040100
— the “connect” privilege if APEX_040100 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = ‘*’ AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
— Before checking the privilege, ensure that the ACL is valid
— (for example, does not contain stale references to dropped users).
— If it does, the following exception will be raised:
—
— ORA-44416: Invalid ACL: Unresolved principal ‘APEX_040100’
— ORA-06512: at “XDB.DBMS_XDBZ”, line …
—
SELECT SYS_OP_R2O(extractValue(P.RES, ‘/Resource/XMLRef’)) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, ‘/Resource/XMLRef’) = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, ‘APEX_040100’,
‘connect’) IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
‘APEX_040100’, TRUE, ‘connect’);
END IF;
EXCEPTION
— When no ACL has been assigned to ‘*’.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘power_users.xml’,
‘ACL that lets power users to connect to everywhere’,
‘APEX_040100’, TRUE, ‘connect’);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’*’);
END;
/
COMMIT;

If you want to grant connect privilege only to the localhost you can replace:

HOST=’* ‘ and DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’*’);

by

HOST=’localhost’ and DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’localhost’);

Importing APEX workspaces

To import a workspace, you have to log in as administrator (admin user) using the following URL: http://server.domain:port/pls/apex/apex_admin. Once logged in, you can import your workspace from the menu “Manage Workspaces”. You simply have to click on “Import Workspace”:

In the Import Workspace menu, you finally have to select your export/import file containing the workspace description – and follow the assistant:

Importing APEX applications

Of course, you can import applications in your new APEX 4.1 environment. In order to import a schema you have to login into your workspace with your username/password on the following URL: http://server.domain:port/pls/apex/

Once logged in, click on the “Application Builder” icon.

You should see all your applications on this screen and you have the possibility to import a new application.

Now, you simply have to follow the assistant in order to import your application. Do not forget to specify the right character set.

Click “Next” and once all parameters are validated, you can finish by clicking “Install”:

Errors & Problems – ORA-04042 and ORA-06512

In my example, I got the following errors the first time I started the apexins.sql script:

User altered.
User altered.
begin
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 3

After some investigations, I found the following piece of code in the coreins.sql script which is launched by apexins.sql. UFROM was set with the value FLOWS_0301000 which was obviously wrong according to the APEX version (3.0.0.00.20).

alter user ^APPUN account lock password expire;
alter user FLOWS_FILES account lock password expire;
--
-- In the event of an upgrade, grant execute on the prior versions wwv_flows_version
-- procedure to the current APEX schema
--
begin
if '^UPGRADE' = '2' then
 execute immediate 'grant execute on ^UFROM..wwv_flows_version to ^APPUN';
end if;
end;
/

Indeed, I saw that several « FLOWS_0XXXXX » users were present in my database. That was the root cause of the error specified above.

SQL> select username from dba_users where lower(username) like 'flows%';
USERNAME
------------------------------
FLOWS_FILES
FLOWS_030100
FLOWS_030000

In order to know which is the right schema, I tried to get more information, such as objects present in each schema:

SQL> select object_name from dba_objects where owner='FLOWS_030100';
no rows selected
Obviously the schema FLOWS_030100 was obsolete. I dropped it.
SQL> drop user FLOWS_030100 cascade;
User dropped.

Then you simply remove your installation with the script apxremov.sql and start the installation anew.

Conclusion

Oracle Application Express version 4.1 offers many possibilities and introduces a lot of new features, as described in the introduction. The new interface is easy and allows to develop professional applications quickly. I hope this documention will help you handle APEX migration projects. Note that if you want to test the new features and the interface, you can create a free account on http://apex.oracle.com.