Migrating the XE Database Management Application to a new version of APEX

I must confess to a weakness when it comes to throwing stuff away.
This is particularly true of techie stuff.
Whilst I have occasionally cannibalised an old machine for parts, there is a regrettably large part of the garage reserved for “vintage” hardware that I might just need at some point.

I’ve recently added to this hoard. I’ve finally gone and got a replacement for my ageing netbook.
As part of the configuration of the new machine, I’ve installed Oracle XE again.

I’m now poised to attempt an upgrade to a shiny new version of APEX.

First of all though, if you are similarly keen to upgrade from the venerable APEX 4.0, which XE ships with, to something more modern, your hoarding instincts may kick-in when it comes to the default Database Management Application.

Once you upgrade APEX 4 to any subsequent version, this application “disappears”.
The functionality it offers is readily available through SQLDeveloper (or indeed, any of the major Oracle Database IDE’s).
Alternatively, it’s a fairly simple matter to come up with your own, improved version.

Not convinced ? Oh well, I suppose we’d better save it for re-deployment into your new APEX environment.

What I’m going to cover here is :

• Backing up the default XE ADMIN application
• Tweaking the APEX export file
• Restoring the XE ADMIN application

I’ve tested this process against both APEX4.2 and APEX5.0 running on Oracle XE11g.
In the steps that follow, I’m assuming that you’re upgrading to APEX5.0.
The main difference here is the APEX owning schema.
For APEX4.2, the owner is APEX_040200, in APEX 5.0 it’s APEX_050000.
As the APEX upgrade takes place entirely within the database, the steps that follow are platform independent.

Incidentally, if you’re wondering exactly how you would upgrade XE11g to this APEX version, details will follow in my next post.

NOTE – I’m assuming here that you’re doing this on your own personal playground 11GXE database and have therefore not
worried too much about any security implications for some of the activities detailed below.

Right, let’s get started…

Backing up the XE ADMIN application

The script below uses the APEX4 PL/SQL API to create an export of the application. This is simply an SQL file that we’ll need to do some light hacking and then run it against the database once the APEX upgrade is completed.
The script ( saved as export_xe_app.sql) is :

declare
	-- Must be logged on as SYSTEM
	-- Need to grant execute on UTL_FILE to SYSTEM
	l_fh utl_file.file_type;
	l_buffer varchar2(32767);
	l_amount pls_integer := 32767;
	l_clob clob;
	l_length pls_integer;
	l_pos pls_integer := 1;
	
begin
	-- Get the source code for the XE Admin APEX application
	l_clob := wwv_flow_utilities.export_application_to_clob ( p_application_id   => 4950);
	
	l_length := dbms_lob.getlength(l_clob);
	
	-- Now write it to a file
	l_fh := utl_file.fopen( 'DATA_PUMP_DIR', 'f4950.sql', 'w');
	
	loop
		exit when l_pos > l_length;
		l_buffer := substr( l_clob, l_pos, 32767);
		utl_file.put(l_fh, l_buffer);
		utl_file.fflush(l_fh);
		l_pos := l_pos + length(l_buffer);
	end loop;
	utl_file.fclose(l_fh);
end;
/

There are a couple of points to note prior to running this script.
The first is that it must be run as SYSTEM as this is the parsing schema for this application.
The second is that you will need to grant execute on UTL_FILE to SYSTEM.
So connect as SYS as sysdba and..

grant execute on utl_file to system
/

Now that’s done, connect as system and execute the script.

Once this is done, you should have a file called f4950.sql in the DATA_PUMP_DIR.
I’ve used this directory as it’s created by default when XE is installed. If you don’t know where this maps to on disk, then you can find it by running the following query :

select directory_path
from dba_directories
where directory_name = 'DATA_PUMP_DIR'
/

In my case (running on Linux) , this returns :

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/XE/dpdump/

Tweaking the APEX Export

There are a couple of things that we need to change in the export script.
The first is the call to the USER_IS_DBA function.

This function was moved from the WWV_FLOW_LOGIN package to WWV_FLOW_SECURITY in APEX 4.2, where( as at APEX 5.0), it still resides.

Therefore, we need to amend line number 218 from …

  p_scheme=>'return nvl(wwv_flow.g_flow_step_id,-1) in (1,101)'||chr(10)||
'       or wwv_flow_login.user_is_dba(p_username => :APP_USER);',

…to…

  p_scheme=>'return nvl(wwv_flow.g_flow_step_id,-1) in (1,101)'||chr(10)||
'       or wwv_flow_security.user_is_dba(p_username => :APP_USER);',

The other thing to change is the name of the APEX owner.
In my case ( migrating to APEX5), this will change from APEX_040000 to APEX_050000.

So, line number 142 in the file changes from…

  p_owner => nvl(wwv_flow_application_install.get_schema,'APEX_040000'),

…to…

  p_owner => nvl(wwv_flow_application_install.get_schema,'APEX_050000'),

Deploying the application to the new APEX version

Before running our application export against the new APEX repository, we need to grant select on the relevant views directly to the APEX owner.
NOTE – I’m indebted to this article by Jeff Eberhard for this particular step:

grant select on dba_temp_files to APEX_050000;
grant select on v_$temp_extent_pool to APEX_050000;
grant select on v_$temp_space_header to APEX_050000;
grant select on v_$system_parameter to APEX_050000;
grant select on v_$session to APEX_050000;

Now that’s done, we can simply connect as SYSTEM and run our import.
The output should look like this (ignore the two spurious errors at the start) :

SQL> @f4950.sql
SP2-0734: unknown command beginning "Content-ty..." - rest of line ignored.
SP2-0734: unknown command beginning "Content-le..." - rest of line ignored.
APPLICATION 4950 - XE_ADMIN
Set Credentials...
Check Compatibility...
API Last Extended:20130101
Your Current Version:20130101
This import is compatible with version: 20100513
COMPATIBLE (You should be able to run this import without issues.)
Set Application ID...
...authorization schemes
...navigation bar entries
...application processes
...application items
...application level computations
...Application Tabs
...Application Parent Tabs
...Shared Lists of values
...Application Trees
...page groups
...PAGE 0: 0
...PAGE 1: Home
...PAGE 2: Storage
...PAGE 4: Sessions
...PAGE 5: Backups
...PAGE 6: Parameters
...PAGE 7: Application Express
...PAGE 9: Tablespace Storage Details
...PAGE 10: Session Details
...PAGE 101: Login
...lists
...breadcrumbs
...page templates for application: 4950
......Page template 936879405068865354
......Page template 936880509857865357
......Page template 936881728833865360
......Page template 936882328801865361
...button templates
......Button Template 936883817777865362
......Button Template 936884428414865363
...region templates
......region template 936886425092865365
......region template 936888203598865371
......region template 936889721148865373
......region template 936890925366865374
......region template 936891504925865375
......region template 936892120369865376
...List Templates
......list template 8548028083899041
......list template 936898727314865389
...report templates
......report template 936900209975865391
......report template 936902514655865394
...label templates
......label template 936904524832865485
......label template 936904703644865485
...breadcrumb templates
......template 936904805731865485
...popup list of values templates
......template 936905603379865499
...calendar templates
...application themes
......theme 936905710643865499
...build options used by application 4950
...messages used by application: 4950
...dynamic translations used by application: 4950
...Language Maps for Application 4950
...Shortcuts
...web services (9iR2 or better)
...shared queries
...report layouts
...authentication schemes
......scheme 936873424775859940
...plugins
...done
SQL> 

As we have imported the application with the same ID that it had originally ( 4950), the Getting Started with Oracle Database 11g Express Edition desktop icon should still work in exactly the same way…

NOTE – the first time you click on a tab that requires login, there is a bit of an issue.
Instead of prompting for login credentials, the text “Content-type:text/html; charset=utf8” appears in the top-left of the page.
If you click the tab a second time, you will get prompted for login credentials as expected.

Once you’ve connected, the tabs should work pretty much as usual :

As a word of caution, I’d be wary of using the Application Express tab for APEX admin on the database.
Instead, I’d use the APEX URL specific to the installed APEX version for this purpose.

Right, off to sort out the clutter in the garage.