17.3.07

APEX 3.0 on XE

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

Oracle Database Express Edition (XE) contains Application Express (APEX2.1) version 2.1 and yesterday Oracle released version 3.0 . While reading the installation guide regarding the Oracle Database requirement I noticed the following:

Do not install this version of Oracle Application Express on Oracle Database Express Edition (Oracle Database XE).

With the previous release of APEX2.2.1 some people installed this version into their XE database (see this OTN thread: Install APEX 2.2.1 on XE is possible! I think...). So why not try to upgrade my APEX2.1 to APEX3.0 on my XE database? It really sounds like a challenge!

However installing APEX3.0 on XE is an unsupported configuration by Oracle. This does not imply it will not work but if you run into problems there is no technical support available (see this OTN thread Install Apex 3.0 on Oracle XE). Also make a backup before you start the installation. My first attempt to upgrade ended with a database where I could not login into APEX, the images where missing and when trying to find out what went wrong, a BSOD appeared. You are warned...


The installation consists of two parts: running update scripts and copying the images file into the virtual path /i/. The first script to run is apexins.sql. The good news about this script is this remark: 02/15/2007 - Remove XE check. There is no check on the database edition anymore. And the bad news is apexins.sql calls coreins.sql which contains the following code:

Rem
Rem Set XE variable to '1' for XE installation, '0' for non-XE installation
Rem
define XE = '0'

Set the variable XE to 1 and save this script and run apexins.sql according to the installation guide.After completion of this script make sure there are no invalid objects. I have still one invalid package body WWV_FLOW_XE_CONFIG. There are no other objects calling this package, so errors can occur if this package is called with dynamic PL/SQL.

While the script is running you can copy the images into the virtual directory (see Where are the /images of Application Express in OracleXE). The total installation time was 1 hour.

Login and choose About Application Express, you should see something like this picture.


26 comments:

Anonymous said...

Thanks a lot for the hint! The installation is running right now on my laptop.... ;-)

Anonymous said...

Hello Jornica,

at first: Thanks !

And then:
Do you think it is necessary to upgrade from (built-in) 2.1 to 2.2 first and then to 3.0 ? Or can I do it with one step (giant one for mankind) from 2.1 to 3.0 ?
What is your experience ?
Thanks in advance!
Andree

Jornica said...

Andree,

If 2.1 is the Apex version included with Oracle XE, then I suggest take the big step. It's the same path I followed.

However you will end up with an unsupported configuration, perhaps some functionality does not work (at all?), do not blame me and did you make a backup first?

But if you like to pimp Oracle XE , I won't stop you.. ;-)

Anonymous said...

I have one question. After I completed installation of apex 3.0 in XE how can access apex_admin page because it seems that there is no such page in XE version.Thanks for answer in advance and thank you for having this blog cause it helped me a lot during my work with apex.

Adnan

Anonymous said...

I agree to Anonymous. My installation (made on a fresh installed XE) has no apex_admin too.
Andree

Anonymous said...

did anyone get an answer or a workaround for this?

Philipito said...

Hi,
i installed 3.0 on XE and with what user do you connect and in what workspace ?

Thx for your answer.

Regards Jornica

Jornica said...

The workspace is the same as the username i.e.:
Workspace: scott
Username: scott
Password: tiger

Anonymous said...

Anybody knows how to manage Workspaces & users if we don't have apex_admin at all?


I have upgraded from build-in Apex 2.1 with two applications and they run well,

But now I want to create a fresh new workspace for another application, without luck ...

Thanks in advance.

Fernand

Philipito said...

Hi jornica,

thx for your answer, but when i try to connect to HR/passwd with HR worspace, it say "Invalid credential"...

Any idea ?

Regards.

Barney Mattox said...

Just as a preliminary, to work around the access to "apex_admin" you can perform the following steps:

(1) Go to you ApEx installation folder (where apexins.sql is located.
(2) log into sqlplus as sys
(3) ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000
(4) @builder/f4050
*note, I also ran @builder/f4550 and @builder/f4700 by accident and these were probably not needed
(5) close sqlplus
(6) Open Browser
(7) Navigate to: http://yourserver:port/apex/f?p=4550:10
(8) Log in to "admin" account and your off

It's also useful to note that if you've misplaced your "admin" password, there is a script in this folder to change it.

Good luck,

Barney

Barney Mattox said...

Follow-up on earlier comment:

Once your into the apex_admin area, you can setup a "Site Specific Task" to place the link onto the workspace login screen for easy access in the future.

Barney

Jornica said...

Barney,

Have a look in coreins.sql:
f4550.sql is always installed. f4050.sql and f4700.sql are only installed in a non XE database.

Application 4550 calls 4050 in rumtime. My guess is You had to run f4050.sql instead of f4550.sql?

This workaround does not work for my APEX/XE configuration. Installation of f4050.sql fails.

Barney Mattox said...

Jornica,

The application 4050 *is* the apex_admin application. Without installing this, the administrative functionality is not available.

The application 4550 is the "login" application for both workspace and admin logins. Yes this is installed by default and that is why it was an accident that I installed it again.

Application 4700 is used for the workspace request process.

The reason these are not installed is because ApEx 2.1 included a custom workspace and administration application. However, this does not seem to be included in the 3.0 installation so it is necessary to install the full packages which are normally disabled.

There may be other disabled packages that are required, but so far I have had complete functionality from installing 4050 and 4700 in my environment.

If you have more information on the error you encountered then perhaps I can assist with your particular setup.

Thanks,

Barney

Jornica said...

Barney,

I reinstalled XE and APEX 3.0 and I got the following error
Illegal security group id value for this schema.

According to the comments:
f4050.sql can also be imported while connected as user FLOWS_030000. Connected as user FLOWS_030000 the import of f4050.sql and f4700.sql succeeded without any problems.

Thanks.

Barney Mattox said...

Jornica,

The applications must be installed using the FLOWS_030000 user. The statement from my original post:

ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000

...is specifically intended to switch from the SYS user to the FLOWS_030000. I'm not certain why you had a problem if you executed all of the steps of my original post as this should have accounted for the user change and the correct application install.

However, it seems like you've got the issue sorted out.

Another problem that you, or others, may run into. The WebDAV protocol as supported by ApEx and IE actually does an inline edit of the files when you copy them up. Some of the new files are actually shorter than the original and so javascript files will become corrupted.

It is important to delete the 2.1 versions of the files in /i/ before using WebDAV to write the 3.0 version files.

I ran into this when I started getting Javascript errors in the SQL Workshop and Utility programs.

I hope all of this information is useful to your readers and have fun developing.

Barney

Алексей said...

2 Barney Mattox
So i Use FTP instead of WEBDav

EXEC dbms_XDB.setftpptort(2100);

alter session register;

commit; -- without commit it can wortk, but i can't make it work :)
if something wrong just restart XE
shutdown; you can use shutdown options.
startup;
and try again;
all images,css-files works fine.

Алексей said...

sorry, for publishing twice comments.
I use both installations 2.1 and 3.0 on my XE.
just created via XDB/ftp new directory /i3/ and save All files from images there.
So to change application we can
switch it in sqlplus
turn 2.1 instead of 3.0
exec flows_020100.wwv_flow_upgrade.switch_schemas ('FLOWS_030000','FLOWS_020100');
revert back
exec flows_020100.wwv_flow_upgrade.switch_schemas ('FLOWS_020100','FLOWS_030000');

Bibi_50 said...

hello

I have now APEX 3.0 on my XE db, with APEX admin activated.

But with that I've lost DB management via HTMLDB.
( I can use TOAD for that )

Is it a mean to have DB management back?

Thanks.

Fernand

Hans Griep said...

Thanks for posting this instructions. Worked fine. Got 3.0 working on my XE instance

Anonymous said...

I seem to have lost the admin interface of XE since I followed the instructions.

Please help !

Anonymous said...

hy

i'm trying to install the f4050 and f4700 applications to get the workspace administrative.
i alter the session to FLOWS_030000 but still get errors when installing these applications:
"Illegal security group id value for this schema"
and few lines later "Package variable g_security_group_id must be set"

what can cause this problems?

Anonymous said...

Hello,
I have the same problem with APEX 3.0 on XE

"Illegal security group id value for this schema"
and few lines later "Package variable g_security_group_id must be set"

Please help, because I can't make any workspaces !

Best regards,
Ionut Oprea

Anonymous said...

Hi,

This may be a stupid question. I run the default installation parameters (well, I change password), but I get a bunch of errors about not having permissions to things. I'm logging in as SYSTEM to SQLPLUS, is that causing the problem?

@apexins password SYSAUX SYSAUX TEMP /i/

Errors for PACKAGE BODY WWV_FLOW_THEME_FILES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1386/19 PL/SQL: SQL Statement ignored
1386/41 PL/SQL: ORA-00942: table or view does not exist
1387/13 PL/SQL: Statement ignored
1387/19 PLS-00364: loop index variable 'C1' use is invalid
1713/19 PL/SQL: SQL Statement ignored
1713/41 PL/SQL: ORA-00942: table or view does not exist
1714/13 PL/SQL: Statement ignored
1714/19 PLS-00364: loop index variable 'C1' use is invalid
...wwv_flow_help

Jornica said...

Anonymous,

Run the apexins script as SYS.

Jornica

HESUFEL said...

Hello!

i did not make backup. i cannot set it up.how do a go back to version 2.1 after trying config v3.0? ;(

thanks

Santos