Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`"

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/ 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)



APEX 4.2.2 released!

Oracle released a new minor version of Application Express: 4.2.2, or to be complete:

Those who are already on version 4.2.x can download a patch from My Oracle Support (if you have a support contract). The patch number is 16277995. Other users can download the full version here.

Interesting documents to read are the known issues and the list of bugs fixed with this release. Unfortunately, the problem with APEX_LDAP.AUTHENTICATE and accented user names (see my earlier blog post) is not mentioned in the list of bugs.




ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_STEP_UI_FK) violated when exporting/importing single page on APEX 4.2.1

Environment: Application Express, Oracle database

We recently upgraded from APEX 4.1.1 to 4.2.1. The upgrade went fine, no problems were seen in the installation log files.

However, when I tried to export a single application page from one APEX 4.2.1 installation and import it into another APEX 4.2.1 installation, I received the following error:

ORA-20001: Error creating page name=”page01″ ORA-02291: integrity constraint APEX_040200.WWV_FLOW_STEP_UI_FK) violated – parent key not found

The application has the same application ID and workspace name. Individual page exports used to work fine on APEX versions prior to 4.2.

I searched the OTN forum and the Oracle support site and I found that this is a know issue with APEX 4.2.1.

I have you access to Oracle support, the problem is registered as bug 15922342.

This is from the bug report:

After upgrading the APEX instances on dev and prod to APEX 4.2 the single 
page export/import from dev into prod doesn’t work anymore. It fails with 
ORA-2291: integrity constraint (APEX_040200.WWV_FLOW_STEP_UI_FK) violated – 
parent key not found

Explain why this is not a bug:
Single component export/import is only supported for application which are 
identical on the source and target system. It relies on the fact that other 
referenced components (for example user interface, templates, …) use the 
same IDs on body systems.

Because of the APEX 4.2 upgrade, that’s not the case anymore. As part of the 
upgrade, several new components had to be created on the fly which do now 
have different IDs on dev and prod. That’s why the single component 
export/import doesn’t work anymore.

If the single component export/import is used to patch applications, it’s 
recommended to do a full export of the app on dev and import it into prod 
after upgrading to a newer version of APEX to make sure that body systems are 
using the same component IDs.”

There are 2 workarounds for this problem:

1) Find an existing ID in the table wwv_flow_user_interfaces on the database where you want to import. Update the export file, so you replace the ID in ‘p_user_interface_id => <id number>’ to the existing number in wwv_flow_user_interfaces.

2) Do a full export/import of the application first. After this, you will again be able to export/import individual pages.

I tried the second workaround, and this works fine! I don’t receive an error anymore when I export a single page to the other installation.






Possible bug when sharing public reports in Team Development TO DO list (APEX 4.2.1)

Environment: confirmed with both APEX 4.1.1 and APEX 4.2.1.

I believe I discovered a bug in the “Team Development” part of Application Express. When you create a report with filters in the “To Do” section, and then save that report as a public report, another user can see the public report in the drop down list, but the filters are not applied when the report is selected.

Let’s illustrate this with an example:

I created a workspace “myworkspace” with 2 users: “myuser” and “anotheruser”.

Now I logged in with “myuser”, I created 2 To-Dos and a public report with the name “TODO myuser”. As filter, I selected “Assigned To=’myuser'”.

To Dos


Now I log out and log in with the user called “anotheruser”, created in the same workspace.

I can see the public report created by user “myuser” in the drop down list, but when I select it, the filter is not applied (which means all to-dos are displayed instead of only the ones for user “myuser”).

To Dos_2


Is this a known bug? Public reports in the applications work fine, but the ones in Team Development seem to be broken…





Upgrade APEX 4.1.1 to 4.2.1 on Oracle Linux with GlassFish

Environment: Oracle Linux 6.3, Oracle database EE, APEX, GlassFish

This blog post explains how you can upgrade your Application Express installation from version 4.1.1 to version 4.2.1, with the APEX Listener deployed under GlassFish.


1) download the software

You can download APEX 4.2.1 here. I downloaded the “all languages” zip file. Please note that the documentation is no longer included with the software. You can download the documentation separately here.

2) copy and extract the software

Now copy and extract the file to your server. I use “unzip” to unzip the zip file. This will create a folder named “apex”. I usually keep this folder on my server in a permanent location, for example /u01/app/apex.

3) check the tablespace requirements

You need about 275MB of free space in the APEX tablespace (mine is called “APEX”) and 100MB of free space in the SYSTEM tablespace.

4) stop the APEX Listener

Before you start with the upgrade, you need to stop the APEX Listener to make sure no one is using the software during the upgrade.

To stop the APEX Listener under GlassFish, you can do it as follows:

– connect to the GlassFish administration console (by default this is port 4848 using SSL).

– log in with the “admin” user.

– click on Standalone Instances => Applications.

– select the “apex” application and select “Disable” from the “More Actions” drop down list.

The APEX Listener should now be stopped on your GlassFish instance.

5) start the upgrade

To upgrade the APEX software, you need to run the “apexins.sql” sql script from within the “apex” folder that you extracted above. You need to connect as SYS as SYSDBA with the oracle software owner (mine is called “oracle”). Make sure the “apex” folder is writeable!

apexins.sql needs 4 parameters: the name of the tablespace for the APEX user (in my case: APEX), the name of the tablespace for the FLOWS_FILES user (in my case: APEX), the name of the TEMP tablespace (in my case: TEMP) and the name of the virtual image directory (which is “/i/”).

$ su - oracle
$ cd /u01/app/apex
$ sqlplus /nolog
SQL*Plus: Release Production on Thu Jan 3 11:21:39 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect / as sysdba
SQL> @apexins.sql APEX APEX TEMP /i/

The upgrade will now start. This will create a new schema called “APEX_040200”. The upgrade can take some time, depending on the speed of your server. On my server (with fairly recent hardware), it took about 17 minutes. After the upgrade, check the installation log file (in the same folder where you exectuted the apexins.sql file) to make sure no errors were encountered during the process.

6) verify the installation

I always verify my APEX installation after a upgrade by querying dba_registry:

$ sqlplus /nolog

SQL*Plus: Release Production on Thu Jan 3 12:06:21 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
SQL> select status from dba_registry where comp_id='APEX';


7) copy the images

Next, copy the new and updated images from your installation folder to the GlassFish docroot (you might take a backup folder of the original docroot/i folder just in case):

$ cp -rf /u01/app/apex/images/* /u01/app/glassfish/glassfish/nodes/localhost-domain1/node01/docroot/i/

8) start the APEX Listener

Finally, start the APEX Listener again (see under step 4) and check if all your applications work fine :-) You should now have a nicely upgraded APEX installation!





If you are accessing network services from withing APEX, like LDAP authentication, print servers and such, you will need to grant the new APEX_040200 user network access or it won’t work. This is documented in the installation guide and is only needed for Oracle 11g. You can grant network access by running the following PL/SQL script with user SYS as SYSDBA:

-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
'connect') IS NULL THEN
'APEX_040200', TRUE, 'connect');
-- When no ACL has been assigned to '*'.
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');

APEX 4.2.1 and APEX Listener 2.0 released!

First of all: a happy New Year to all my blog readers! I wish you an exciting 2013 with lots of interesting projects, no budget restraints and no unexpected downtime ;-)

When I returned from the Christmas holidays, I noticed that both Application Express 4.2.1 and the APEX Listener 2.0 have been released!

APEX 4.2.1 is actually the first patch set release for APEX 4.2. You can download the full release here (for new installations or upgrades) or, if you have a support contract, you can download the patch only (with number 14732511) here (for upgrades from version 4.2). There are a lot of bugs that have been fixed with this patch set (check for a full list here),  and also some new features have been introduced, for example the inclusion of AnyChart version 6 (which has even more HTML5 support). I’m happy with this patch set release, since this means it’s time to upgrade for us. We had some issues in the past after upgrading to a minor APEX release, so I learned to have patience and wait for the first patch set ;-)

Application Express Listener 2.0 was available as a Early Adaptor version for a while, but now the first production release is available. There are a lot of new features, like the ability to define multiple database connections, and more support for RESTful services. There are now also 2 ways to configure it: using SQL Developer (you need at least version 3.2.2) and using a new command-line interface. The web interface however has been removed, which I find a bit sad because I liked it :( We are using the APEX listener deployed to GlassFish, and I’m not sure if anything has changed in that matter. This is something I need to look into. I also wonder how you would upgrade from a previous version of the APEX Listener to this one. I suppose this involves some redeployment, and so also some (planned) downtime.



Removal of APEX Listener cache folder causes hanging of APEX

Environment: APEX 4.1, GlassFish 3.1.1/3.1.2, APEX Listener 1.1.3, Oracle Linux 6.2 64-bit

In the past couple of months, we had several lock ups of our APEX environments. This all started after we moved to GlassFish with the APEX Listener. The symptoms were always the same: all database connections in the connection pool were in use and hanging, and we had to restart the GlassFish instance to release them.

Today, I was finally able to reproduce this problem at will and I found a solution!

The problem is caused by the removal of the file system folder that the APEX Listener uses to cache files.

We had the file caching enabled to increase the performance of a APEX application which downloads images from the database through a procedure.

This is how our caching is configured:

As you see, the caching folder is set to /tmp/apex/cache. I believe this is the default configuration.

Now, for some reasons, Linux sometimes removes all folders from the /tmp folder. And this not only happens after a server reboot! There must be some process scheduled to do this, but I haven’t found yet which one. When we had a recent hang of APEX I noticed some broken images in the applications. I checked the file system and behold, the file cache folder was gone! A restart of the GlassFish instance (and so also the APEX Listener) recreated the caching folder.

Maybe this issue has been fixed in the newer version 1.1.4 of the APEX Listener, I didn’t try it out yet…

In the mean time, I found a permanent solution for the problem by setting the Cache File Location to a folder outside the /tmp file system, for example /home/oracle/apex/cache.