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…

xe_admiin_apex5

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 :

storage_tab

sessions_tab

parameters_tab

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.

The Ping of Mild Annoyance Attack and other Linux Adventures

Sometimes, it’s the simple questions that are the most difficult to answer.
For example, how many votes does it take to get an MP elected to the UK Parliament ?
The answer actually ranges from around 20,000 to several million depending on which party said MP is standing for.
Yes, our singular electoral system has had another outing. As usual, one of the main parties has managed to win a majority of seats despite getting rather less than half of the votes cast ( in this case 37%).

Also, as has become traditional, they have claimed to have “a clear instruction from the British People”.
Whenever I hear this, can’t help feeling that the “instruction” is something along the lines of “don’t let the door hit you on the way out”.

Offering some respite from the mind-bending mathematics that is a UK General Election, I’ve recently had to ask a couple of – apparently – simple questions with regard to Linux… Continue reading

Oracle XE 11g – Getting APEX to start when your database does

They say patience is a virtue. It’s one that I often get to exercise, through no fault of my own.
Usually trains are involved. Well, I say involved, what I mean is…er…late.
I know, I do go on about trains. It’s a peculiarly British trait.
This may be because the highest train fares in Europe somehow don’t quite add up to the finest train service.
We can debate the benefits of British Trains later – let’s face it we’ll have plenty of time whilst we’re waiting for one to turn up. For now, I want to concentrate on avoiding any further drain on my badly tried patience by persuading APEX that it should be available as soon as my Oracle XE database is…
Continue reading

Getting a File Listing from a Directory in PL/SQL

It’s General Election time here in the UK.
Rather than the traditional two-way fight to form a government, this time around we seem to have a reasonably broad range of choice.
In addition to red and blue, we also have purple and – depending on where you live in the country, multiple shades of yellow and green.
The net effect is to leave the political landscape looking not so much like a rainbow as a nasty bruise.

The message coming across from the politicians is that everything that’s wrong in this country is down to foreigners – Eastern Europeans…or English (once again, depending on your location).
Strangely, the people who’ve been running our economy and public services for the last several years tend not to get much of a mention.
Whatever we end up choosing, our ancient electoral system is not set up to cater for so many parties attracting a significant share of support.

The resulting wrangling to cobble together a Coalition Government will be hampered somewhat by our – equally ancient – constitution.

That’s largely because, since Magna Carta, no-one’s bothered to write it down.

In olden times, if you wanted to find out what files were in a directory from inside the database, you’re options were pretty undocumented as well.
Fortunately, times have changed…

What I’m going to cover here is how to use an External Table pre-process to retrieve a file listing from a directory from inside the database.
Whilst this technique will work on any platform, I’m going to focus on Linux in the examples that follow…
Continue reading

SQLDeveloper XML Extensions and auto-navigation

It’s official, England are now the second best cricket team in the British Isles !
After all, Scotland were dispatched with ease and as for Wales…they didn’t even make it to the Cricket World Cup.
OK, technically they did because they’re part of England for the purposes of cricket…although you’d be hard pressed to get them to admit it.
Ireland are, of course, some way in front having actually managed to actually win the odd game against Test Playing Nations.
Whilst it takes quite some effort to find silver lining in the cloud of English Cricket’s latest debacle, the same cannot be said if SQLDeveloper is your Oracle Database IDE of choice … Continue reading