Fully freeware APEX environment II: TomCat 6

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

This is part two of my series regarding a fully freeware APEX setup.

Post 1 coveres the installation of Oracle XE 11 beta on CentOS 5

Shut Down Apex-EPG

Log in as system into the XE database using SQL*plus. We must close the EPG within the database, because Tomcat is going to use the same port (8080)

execute dbms_xdb.sethttpport(0);

Tomcat installation

I took this from here.

su -
cd /etc/yum.repos.d
wget 'http://www.jpackage.org/jpackage50.repo' yum update
yum install tomcat6 tomcat6-webapps tomcat6-admin-webapps
service tomcat6 start

Tomcat is running!


That was easy enough. We need to do some configuration.

If you get dependency errors please have a look at the original post essentially it says to try this:

rpm -Uvh http://plone.lucidsolutions.co.nz/linux/centos/images/jpackage-utils-compat-el5-0.0.1-1.noarch.rpm

and then try again.

Firewall configuration

"Firewall configuration"
"Other ports" 8080:tcp
service tomcat6 restart

Downloads: APEX Listener and APEX itself

Even though XE 11g is shipped with Apex preconfigured, for this setup we do need to link to the images folder. We do this by downloading the full Apex archive. At the same time we can allready download the listener as well:

Unzip Apex:

unzip apex_4.0.2.zip
unzip apex_listener. -d apex_listener

We only need the images folder from the apex distribution. Nevertheless it can come in handy when the full apex installer is available.

The Tomcat htmlroot (or docroot) is located at /var/lib/tomcat6/webapps.

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

We copy the apex and the listener installer files into the Oracle tree and create a symbolic link in tomcats’ webapps folder:

su -
mkdir $ORACLE_BASE/product/4.0.2
cp -r apex $ORACLE_BASE/product/4.0.2/apex/
ln -s $ORACLE_BASE/product/4.0.2/apex/images /var/lib/tomcat6/webapps/i
mkdir $ORACLE_BASE/product/1.1.2
cp -r apex_listener $ORACLE_BASE/product/1.1.2/apex_listener

Tomcat configuration

Edit the /etc/tomcat6/tomcat-users.xml. We’re still root..

gedit /etc/tomcat6/tomcat-users.xml

replace the last line (having “</tomcat-users>”). Replace the username and password as desired.

  <role rolename="manager"/>
  <role rolename="admin"/>
  <role rolename="Manager"/>
  <role rolename="Admin"/>
  <user username="apex" password="verysecret" roles="manager,admin,Manager,Admin"/>

It’s a bit silly that the roles are mentioned twice, but apparently not all implementations use the same casing.
I’m using the “apex” user here with the password “verysecret”

Save the file (Duhh 😉 )

Now we must make sure that Tomcat gets started at boot-time:

chkconfig --add tomcat6
chkconfig tomcat6 on

That should do the trick. So far for Tomcat for now.

Installing the APEX listener

First we need to unlock the “apex_public_user” and set its password. Open up the SQL*plus commandline and enter:

alter user apex_public_user account unlock;
alter user apex_public_user identified by the_secret_password;

Now open Tomcat manager: http://localhost:8080/manager/html. Go to the section: WAR file to deploy.


Click the browse button and select the warfile from /u01/app/oracle/product/1.1.2/apex_listener/apex.war

Click the “deploy” button.

Now you’re ready to configure the listener

Fully freeware APEX environment I: CentOS & Oracle XE 11g

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

<EDIT sept 2, 2011>

Today oracle released the definitive version of Oracle XE 11.2. I haven’t had the opportunity to test that version against my blogs, but I’ll do that shortly. stay tuned


In my effort to create a fully free APEX environment this post is step 1: Install CentOS-5 and Oracle XE 11g.

This series will consist of a number of posts:

This is post 1, covering the basic installation.

Install CentOS-5

In my first attempts to get to this fulle freeware APEX environment I decided to use Ubuntu server with the GNOME desktop. However I found out that most software is delivered in RedHats RPM format. Especially the Oracle HTTP server preferes to be installed on a RedHat compliant system. CentOS is just that.

Just do a clean install CentOS downloaded from their site. I used version 5. But make sure you use the 64 bit version.

After the clean install I installed the VirtualBox extensions:

  1. Choose “Devices” – “Install Guest Additions”
  2. Open a terminal:
    su -
    cd /media/VBOXADDITIONS_4.0.6_71416/

    This installs the VBox additions. We do need a reboot:

    shutdown -r now

Install Oracle XE 11g beta

  1. Download the installer here.
  2. Open a terminal and go to the directory where you downloaded the ZIP
    mkdir OracleXE
    unzip linux.x64_11gR2_OracleXE.zip -d OracleXE
    su -
    cd /home/apex/Downloads/OracleXE
    rpm -ivh oracle-xe-11.2.0-0.5.x86_64.rpm
  3. We’re still Super so we can kick the configuration straight away:
    /etc/init.d/oracle-xe configure

    the installer will ask for a port to run Apex (8080), a port to run the database listener (1521) a password to be used for both SYS and SYSTEM users and whether you’d like to have the database start automatically at system start (y). As a famous dutch beer-commercial states: “now we wait”.

  4. When the installer finishes I put the apex user (the one I use to log in into the system) to the dba group. If we ommit this step we’ll not be able to use SQL*plus as the regular user (in my case this user is called “apex”).
    usermod -G dba apex
  5. Now we need to edit the bashrc file to have the paths and system variables set correctly for everyone on the system.
    gedit /etc/profile

    In the editor at the end of the file add these lines:

    #ORACLE XE 11g Beta
    export ORACLE_SID=XE
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
    export ORACLE_TERM=xterm
    export NLS_LANG=american_america.utf8
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
    export PATH=$ORACLE_HOME/bin:$PATH
  6. Now let’s reboot. We’re finished installing XE !
    shutdown -r now
  7. In the menu go to: Applications – Oracle Database 11g…. – Get Started
    This starts the browser and opens the APEX environment.When the page opens correctly we know that:

    1. The database starts automatically
    2. The EPG is running on port 8080

If you continue to use the EPG, you’re ready.


if you want to use a different HTTP server you should continue:

You can also use the APEX listener in a stand-alone mode.

Command Prompt here….

Nothing to do with APEX, but I really don’t want to forget this.

As you might know, I’m a big fan of the command prompt. I’ve been using 4DOS/4NT/Take Command for years.

These products are sold by a company called JP Software. But now they also have a free-ware product, which suffices for most of my work.

To get a “Command Prompt here” after a right-click on a folder in Windows Explorer I did this:

  1. Installed TCC/LE (downloaded here)
  2. Opened my reg-editor and went to:
  3. Created a key called “Command Prompt”
  4. Set the default string to “&Take Command LE”
  5. Created a new key within the “Command Prompt” key called “command”
  6. Set the default string to
    "C:Program FilesJPSoftTCCLE12tcc.exe" cdd "%1"
  7. Ready. Changes should take effect immediately

This information was taken from another blog and adjusted for TCC/LE

Using APEX Listener as Stand-alone HTTP server on Windows

Oracle provides a good alternative for using the EPG in single-developer projects. It is called the Oracle APEX Listener.

it is intended to run as some kind of plug-in in:

  • Oracle WebLogic Server.
  • Oracle GlassFish Server.
  • Oracle Containers for J2EE (OC4J).

But it also runs in a stand alone mode.

This time I’d like to discuss the latter, stand-alone mode.


Using the listener in stand-alone mode, for me, has several advantages.

  • super fast to set up
  • easy to automate
  • possibility to start more instances (ie for each APEX instance)
  • no WebDav/FTP access required to the /i/ folder
    (this also has a drawback when enrolling your app)


The main drawback is that it won’t install as a service on Windows.


Using the installation guide you’ll be up and running within minutes. Oracle did a good job in explaining this. I will not elaborate further on this.

In essence you need to start the Listener as follows:

java.exe -Dapex.home={PATH_A} -Dapex.images={PATH_B} -Dapex.port={PORTNUMBER} -jar {PATH_C}

The Listener now will try to find settings in the {PATH_A}. If it cannot find them, it will first ask for a username and password for the adminlistener and managerlistener users, after which it will start the browser with a configuration page.

Apex listener configuration page
Apex listener configuration page

Here you give the Listener the information it needs to connect to the database. It’s a kind of DAD… You only have to fill out the first tab. The rest you can leave as default. When you apply the Listener will save this information into the {PATH_A} location.

{PATH_B} points to the images directory, in my case that is d:apex4apeximages so I can edit and save CSS, images, HTML etc as I please.

{PORTNUMBER} is the portnumber the listener is listening to. In my case that is 81, but you can put it on any port you like (even 80!).

{PATH_C} points to the apex.war java program.

Once the Listener has saved the connect-information into the {PATH_A} location, we can easily switch the listener to another port without any re-configuration. Just use the same command and only replace the {PORTNUMBER} variable with the desired port-number. My batch-file will handle this.

Putting it all together

Now I’ve explained how to start the listener the next step is to do some automation.

I created a batch file that you can use to automate starting the Listener.

::@echo off

pushd D:Oracleapxlistener

set tmp=tmp.%1%
set java=c:Program FilesJavajdk1.6.0_24binjava.exe
set apx_user = RMARTENS
set apx_workspace = APEXDEMO
set apx_listener = D:Oracleapxlistenerapex.war
set browser = "c:Program Files (x86)Mozilla Firefoxfirefox.exe"

if not exist %tmp% mkdir %tmp%

start "ApexListener %2% %1%" /MIN "%java%" "-Dapex.home=%tmp%" -Dapex.images=D:apex4apeximages -Dapex.port=%2% -jar %apx_listener%


echo Listener started

start "Oracle Apex" %browser% "http://localhost:%2%/apex/f?p=4550:1:::::F4550_P1_COMPANY,F4550_P1_USERNAME:%apx_workspace%,%apx_user%"


If you save this as a batch file and edit lines 7 to 11 to reflect your settings, you’ll be able to start your listener as:

apxlistener.bat apexdemo 81

Which starts the listener using the settings in the apexdemo folder on port 81.

The batch can be further extended to have different /i/ folders for each environment, but for me that was not needed.

— Good Luck ! —

Tabs within APEX (using jQuery)

The solution uses APEX 4 built-in jQuery libraries.

The idea is to create a “containing region”, that is a region that will contain the sub-regions.

Those sub-regions then will appear as a tab on your page.

Create a template for the containing region

Go to “Shared Components” – “Templates”, filter on Region Type templates:

Press the create button

choose Region

choose “From Scratch”

Give the template a meaningful name. In my example I use “jQuery Tabs”
Choose “Custom 1” for the template class
Press the “Create button”
Editing the template
You now have the “jQuery Tabs” template in your list.
Open it by clicking on the name:

We must place some code in the “Definition” section:

<div style="clear:both;"></div>
<link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.tabs.css" type="text/css" />
<script src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.tabs.min.js" type="text/javascript"></script>

Go to the “Sub Regions” section, this consists of three items:

  1. Header Template
    <ul style="height: auto;">#ENTRIES#</ul>
  2. Header Entry Template
    <li><a href="##REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION_TITLE#</a></li>
  3. Template
    <div id="#REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION#</div>

The template is now ready. Save the template.

Next step is creating a Containing Region on the page
Go to the page:

Create a HTML-region:

The Title doesn’t really differ that much, it’s only for you reference.
Choose the correct Region Template (“jQuery Tabs”)
Leave the rest as defaulted.

leave the presented HTML region source empty. Remember we’re going to use this region only as a container for sub regions
Open the region we just created:
Go to the “Header and Footer” section and place this code in the footer:

<script type="text/javascript">
 apex.jQuery(function() {

Now open each region that should be transferred into a tab and change the parent region (within the section “User Interface”) to the newly created container-region (“jQueryTabs”)

Repeat this step for each tab you need.
Ready.. Good luck!

Oracle APEX report refresh the easy way

I’ve seen numerous solutions for partial page refreshes. All using javascript, timers etc.

I just want a report to refresh itself after 5 seconds.

I add this to the region-footer:

<script type="text/javascript">
setInterval( "$a_report('##REGION_ID#'.substring(1), 1, 7, 7);", 5000);

On request of Patrick Wolf (in the remarks below) this also works:

<script type="text/javascript">
  setInterval("jQuery('##REGION_ID#').trigger('apexrefresh');", 5000);

No extra javascript functions, no dificult parameters, works for multiple regions..

Essentially it will do a region refresh using the $a_report apex-javascript function only once (after 5 seconds)..

But when the region gets refreshed…. this region-footer gets executed again…. hence: a refresh every 5 seconds.

$a_report takes these parameters:

  1. the region_id that needs to be refreshed. (apex puts an ‘R’ in front of it. the substring(1) removes the ‘R’ again)
  2. the 1st record to show
  3. the last record to show
  4. the number of records to show
  5. sorting

I hope this helps.

APEX built-in variables

For all you guys ‘n dolls here an overview of the built-in apex variables and how/when to use ’em:

  • APP_ID
  • Application Express SCHEMA OWNER

taken from this source

Variables can be referred to in three ways:

  • Bind variable ( :APP_ID )
    Used inside APEX in SQL and PL/SQL
  • V and NV functions ( NV(‘APP_ID’) )
    Used outside APEX ( in PL/SQL packages for example )
  • Substitution strings ( &APP_ID. )
    Used inside APEX in items for example


Oracle Apex 4 and the new Firefox 4 / Internet Explorer 9

Hi there,

Yesterday I installed Firefox 4 and Internet Explorer 9. Wow beautyfull browsers.. both of them.

However yesterday I allready found out that IE is not diaplaying the AnyChart charts in my Apex applications. So IE9 for now is a no go.. pitty.

Today I also found out that Firefox 4 has issues with Apex. for example getting into the application builder. So a no go there as well..

Luckily downgrading firefox is as easy as an install. So I did that and the problems immediately went away.

I will re-install both in a virtual machine to investigate further, but for now.. you are warned.

To be continued……


Apex listener v 1.1

I simply love the Apex Listener that Oracle sent out a couple of weeks ago.

I now allways use it to do my (one developer = me) development, for a number of reasons.

  1. It’s easy to set up.
  2. I can edit my CSS and Images on the filesystem (no WebDav needed).
  3. It saves me resources within the database

I also use it for one of the production systems, because APEX would break down when doing heavy database work. (It’s a 10g and EPG is officially not supported). With the apex-listener the system gets a bit sluggish, but at least continues to work.