Oracle Application Express 4.0 with Ext JS

By Mark Lancaster
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Setting up an Oracle APEX and Ext JS Environment

About this book

Modern web-based applications are moving rapidly away from simple HTML pages, with users expecting desktop styled rich internet applications. Oracle Application Express includes multiple built-in interfaces especially designed for adding JavaScript libraries and components. Ext JS is a polished, high performance set of customizable UI widgets with a well designed and extensible Component model. Combining Ext JS components with the well engineered server side processing provided by Oracle APEX is a recipe for success.

Written by Oracle ACE, Mark Lancaster, this book is a complete practical guide to building robust desktop-styled web applications using Oracle Application Express and the powerful Ext JS JavaScript library

This book starts off by setting up a productive environment for Oracle APEX and Ext JS, preparing you to get ready to code, and then gradually introducing you to the Ext JS API. You then create a theme based on Ext JS into APEX from scratch, starting with integrating the Ext JS library into the page template, then covering all the template types.

You further enrich your interface by integrating Ext JS form components and Ext JS layout elements. You are shown how to integrate components including tab panels, toolbars and menus. Existing components are also enhanced, transforming select lists into auto-completing combo boxes and text-areas auto-sizing as you type.

Using exciting new Plug-ins feature, you will learn how to develop custom APEX components that can be used declaritively. This book extends native APEX functionality by integrating Ext JS widgets and components with integrated server-side JavaScript generation, AJAX processing and validation.

The book then covers integrating Plug-ins with APEX provided Dynamic Actions JavaScript. You proceed further to build advanced interactive components using AJAX enabled trees and grids.

Then you will see how to use the iFrames component along with page templates to build a multi-page interface and also deal with JavaScript communication between iFrames.

Finally, you will integrate Ext JS with jQuery using the Ext jQuery adaptor. This book also covers examples of jQuery functionality interacting with Ext JS. By the end of this book you will also learn to improve the performance of your JavaScripts.

Publication date:
March 2011


Chapter 1. Setting up an Oracle APEX and Ext JS Environment

In this chapter, we will go through the process of setting up a productive development environment for both Oracle Application Express (APEX) and Ext JS.

APEX applications are accessed by a web browser via an HTTP Server, which may use the Oracle APEX Listener, Oracle HTTP Server with the mod_plsql plug-in, or the Embedded PL/SQL Gateway to act as the web server.

Each of the web server options has advantages and disadvantages. We will examine the relevant merits of each option separately, before showing how to configure them to support development with Ext JS.

Setting up a development environment is more than just installing the relevant software. It's also about managing versioning and dependencies of your code, and configuration files and other project assets. Automating your backup and build processes ensures that you are able to deliver your software in a repeatable and consistent manner. Regular software releases should be a straightforward activity and not a major issue.

Making good choices setting up your development environment at the beginning of a project can be enormously beneficial for the entire lifecycle of the project. Getting it wrong can be equally as bad!

This chapter covers:

  • APEX installation considerations

  • Brief overview of the Ext JS SDK

  • Merits of each of the web tier options: Oracle APEX Listener, Oracle HTTP Server with the mod_plsql plug-in, and the Embedded PL/SQL Gateway

  • Loading the Ext JS SDK on each of the web tier options, and also onto hosted environments where you don't have direct access to the web server

  • Setting up a Subversion source code repository for project assets

  • Automating backup and build processes to simplify application deployments and reduce errors

By the end of the chapter, you will be fully set up and ready to code.


Setting up for success

One of the reasons for the outstanding success of Oracle APEX is that you can build applications really quickly. Within a couple of hours, you can have a development database set up, and using the built-in themes, you've started building an application.

This can be really dangerous for us as developers. At the beginning of a project, particularly when you're using a new or unfamiliar technology, there is a pressure to prove yourself—either as an individual starting a new role or as a team proving a technology to management.

Experienced programmers recognize this; the challenge is convincing everyone involved on what the ultimate goals of the project are, and not just take a short-term, short-sighted approach.

While not a dedicated practitioner of the methodology, some of the principles behind the Agile Manifesto ( are a great reminder of on what we should be focusing.

The ultimate goal of any project is to write valuable software, and by valuable I mean software that is going to be used and is useful. There is no point writing software unless it delivers real business outcomes—either tangibly in increasing business revenue, streamlining business processes, or less directly by reducing time spent on non-productive activities.

Working software is the primary measure of progress. The more time that we, as developers, can spend on regularly delivering valuable software in short time periods, the more successful our project is.

Regularly deploying working software implies that we need an efficient build process. This is the art of maximizing the amount of work not done! By taking a little extra time at the beginning to set up our development environment properly, it should be largely self sustaining and require almost no ongoing maintenance.


Installing Oracle APEX

Oracle APEX 4.0 requires a minimum database edition of, or Oracle XE, which, despite reporting as being, includes additional features that didn't make it into the supported versions of the database.


This book won't be going into the details on how to install Oracle APEX into the database, as that is very well covered by the documentation provided with the product and available online at

Oracle APEX now comes pre-installed on all editions of Oracle database 11.1 upwards, and is also pre-installed in Oracle XE the free edition of the database. In both cases, you will need to upgrade your Oracle APEX installation to Oracle APEX 4.0 the version covered in this book. Go to Oracle Application Express on the Oracle Technology Network (OTN) at, and download the latest production version of Oracle APEX.

Regardless of whether you're installing or upgrading Oracle APEX, there is one important decision you need to consider before you proceed. By default, Oracle APEX is installed into the SYSAUX tablespace.


You have the option when installing or upgrading to specify an alternative tablespace.

The SYSAUX tablespace is installed as an auxiliary tablespace to the SYSTEM tablespace when the database is created. It holds a number of database components that you may or may not use, depending on the nature of your applications, such as Oracle Text, Oracle Spatial, and Oracle interMedia.

It also contains components such as Enterprise Manager and Automatic Workload Repository which, depending on several factors, such as number of active sessions, data retention period, and snapshot intervals, can require significant storage volumes and contain highly volatile data, leading to disk I/O contention.

By installing Oracle APEX into its own tablespace, database administrators (DBAs) are able to manage it in isolation from other database components, allowing more flexibility in performing database operations. For example, you can reduce I/O contention by storing the underlying data files of the APEX tablespace on different disk drives to the SYSAUX tablespace.

Similarly, taking the individual Oracle APEX tablespace offline to perform a data recovery operation allows other applications to remain online, providing better overall availability. Or in another scenario, take advantage of transportable tablespaces to copy your Oracle APEX environment to another database quickly.


Downloading Ext JS

The Ext JS SDK (software development toolkit) can be downloaded as a single zipped file from the Sencha website at This book is based on version 3.3.1, the latest release at the time of writing. As the Ext framework is now quite mature, you should be safe to use later releases of version 3 with this book, should they be available.

Ext JS is released under both open source and commercial licenses to suit both implementations and provides support to developers through community support forums, as well as subscription-based support and maintenance.

The download of the Ext JS SDK is nearly 15MB, and once extracted, is roughly 105MB. The reason for the large size becomes apparent once we start examining the extracted files.

The screenshot shows the contents of the top directory within the Ext JS SDK zip file. It comprises everything you need to work with Ext JS, including documentation, resources, and examples.

Let's briefly go through the directories shown in the screenshot.




Contains the ext-base.js file used to provide browser-independent base-level DOM and AJAX functions for use by the main Ext JS library. It also contains adapter files that allow you to work with other JavaScript libraries, including jQuery, Dojo, and YUI.


Documentation for the library.


Individual component and combination examples. This is a veritable treasure trove of information and working solutions to be integrated into Oracle APEX.


Assembled subsets of the JavaScript library, designed to assist building customized versions of Ext JS.


Images, CSS files, and Flash objects used by Ext JS.


JavaScript source code for Ext JS.


Test cases used by Sencha for automated testing harness.


Control file used to merge JavaScript files from src into pkgs files, and also ext-all.js and ext-all-debug.js. Merged files have the comments stripped and code minified. Merged files with the -debug suffix are a non-compressed version, preferred during development so that debugging is easier.


Full Ext JS library, excluding the adapter, compressed and minified.

The uncompressed version without comments, ext-all-debug.js, is useful for debugging during development. The uncompressed with comments version is ext-all-debug-w-comments.js.


Other files not described: index.html, gpl-3.0.txt, INCLUDE_ORDER.txt, license.txt, and so on.

As you can see from the relative sizes of the folders, the Ext JS SDK has placed an emphasis on documentation and examples. This greatly assists in learning to use the library and is a real credit to the Ext JS developers.

The ext-all.js file and the adapter and resources folders are the only files you need to deploy to your production web server. While saying this, my preference is to deploy the entire SDK. That way all the documentation and examples are on hand.


Many of the examples need to be run from a web server and cannot be run directly from your computer. This is also true of the documentation. So if you're wondering why you just see a spinning image when you open the documentation file locally, now you know.


Which web server to use?

Oracle APEX is accessed through a browser via a HTTP server, which may be the Oracle HTTP Server (OHS) with the mod_plsql plug-in, the Embedded PL/SQL gateway (EPG), or most recently the Oracle APEX Listener certified against Oracle WebLogic Server, OC4J, and Oracle Glassfish Server. The APEX Listener can be installed on any modern J2EE Server, such as Tomcat.


The APEX 4.0 Installation Guide covering each of the web server options is available at

Here, I'm assuming you're working in a team environment, and we're setting up a dedicated development web server, only accessible within the intranet.

I'll go through the relevant merits of each option separately, before showing how to configure them to support development with Ext JS. Once again, rather than repeat the standard installation documentation provided by Oracle, I will simply assume that you have made your choice and installed your preferred web server into your development environment together with Oracle APEX.

Storing your web assets

The virtual path the web server uses to point to the images directory distributed with the Application Builder in Oracle APEX defaults to the alias /i/.

Regardless of which web server you use, it's a good idea to keep your web assets in a different location from where Oracle stores them. Later sections in this chapter for each of the web server options will cover storing assets in a different location and configuring the web server to reference your assets with the alias /ux/, which stands for user extensions.

Storing your web assets in a different location makes life a whole lot easier when it comes to upgrading Oracle APEX again. All you have to do is follow the upgrade notes, secure in the knowledge that you are not going to delete any of your application files accidentally.

It allows your server administrator to secure the Oracle APEX directories, preventing anyone from making changes to the standard Application Builder configuration. Similarly, your application directory can be accessed only by the necessary people.

Customizing application builder files

If you ever have the need to modify some of the CSS rules or JavaScript provided by Oracle, here is one way to do it. By copying the Oracle APEX directories from the /i/ location to your /ux/ location, you can customize the standard Application Builder files without impacting anyone else.

To use your customized version, you need to update the application preferences image prefix to point to your alias, as shown in the following screenshot. To edit application properties, log into Oracle Application Express, and then select the appropriate application from the Application Builder. Click the Edit Application Properties button, top right on the Application Definition page.


Oracle HTTP Server

The Oracle HTTP Server (OHS) is the most mature of the three web server options available, and is the typical choice for Oracle APEX production and development environments today. OHS is based on the proven Apache web server, with the Apache code base dependant on which version of the database you are using. Oracle HTTP Server, distributed with Oracle Database 11g, uses the Apache 2.2 code base; on the other hand, Oracle Application Server 10g is based on Apache 1.3 or Apache 2.0 for the standalone version.

Apart from the proven reliability and broad support available for the Apache web server software, the other main advantage cited for using OHS is the ability to separate the application server tier from the database tier, allowing the web server to be installed on a different server from the database.


For production environments, where factors such as security, performance, and load balancing have a much higher priority, the ability to separate the application server tier from the database tier is an important consideration.

However, as we are looking at a development environment, the restricted-use license for OHS will probably be a deciding factor. Included with the Oracle Database 10g and 11g releases is a restricted use licence for OHS for all editions except Oracle XE, which allows OHS to be used, provided it is on the same server as the database. Running OHS on another server requires the other server to be licensed to use OHS either through a database licence or an Oracle Application Server licence.

One of the most confusing aspects of OHS is which version to install, as Oracle has released over 10 different versions of OHS, (see My Oracle Support Note 260449.1 for the complete list).


Do not blindly install the version supplied with the database. You should carefully decide the version you'd like to install.

My Oracle Support Note 400010.1 - Steps to Maintain Oracle Database 10.2 Companion CD Home (for Oracle HTTP Server) states:

Something to think about...

The Oracle HTTP Server delivered with the Oracle Database 10.2 Companion CD is provided to initially get HTMLDB installed and running. However, it's an older version with limited functionality and support. Both the Oracle HTTP Server and HTMLDB from this CD would need to be upgraded at this time. The Companion CD also installs a mix of 10.2 and 10.1 products which is more difficult to maintain. Consider using a newer installation of the Oracle HTTP Server, and then configure APEX (formerly HTML-DB) accordingly.

The message here is Oracle doesn't recommend you to install the version that comes with the database. If you're going to install the standalone version of OHS, take the extra step of downloading the version that comes packaged with the Application Server. This is because the main versions of OHS are built for the Application Server releases. OHS can be downloaded from

Loading Ext JS onto the Oracle HTTP Server

Depending on the version of the Oracle HTTP Server you are running, the location for the Application Builder images directory is held in either the httpd.conf, marvel.conf or dads.conf files. Search for the text alias /i/.

For example:

Alias /i/ "ORACLE_HTTPSERVER_HOME/Apache/images/"
Alias /ux/ "ORACLE_HTTPSERVER_HOME/Apache/ux/"

Here, ORACLE_HTTPSERVER_HOME is the location where the HTTP Server is installed.

Edit the file, adding another Alias /ux/ as shown in the preceding snippet, pointing to the location where you will upload the Ext JS files. Having done this, upload the Ext JS files onto the web server at the location you specified. Remember, you can either deploy all the files in the Ext SDK, or just the minimal set comprising the ext-all.js file and the adapter and resources folders.

You will need to stop and restart the Oracle HTTP Server before your changes are detected, which is done using the opmnctl executable.

For Unix and Linux, execute the following:

ORACLE_HTTPSERVER_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
ORACLE_HTTPSERVER_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server

For Windows, execute the following:

ORACLE_HTTPSERVER_HOME\opmn\bin\opmnctl stopproc ias-component=HTTP_Server
ORACLE_HTTPSERVER_HOME\opmn\bin\opmnctl startproc ias-component=HTTP_Server

To verify that the Ext JS library is now accessible on the web server, just check that you can successfully fetch one of the files. Substituting the appropriate host and port values, use your browser to verify you can now see the Ext JS asset:

http://host:port/ux/ext-3.3.1/resources/images/default/tree/drop-yes.gif should show a tick, as seen in the following screenshot:

If you decide to do the full Ext JS SDK install, now is a good time to bookmark the documentation and samples:

  • http://host:port/ux/ext-3.3.1/docs/index.html

  • http://host:port/ux/ext-3.3.1/examples/index.html


Embedded PL/SQL Gateway

The Embedded PL/SQL Gateway (EPG) runs within the database as part of the XML DB HTTP Protocol listener and provides equivalent core features to the Oracle HTTP Server (OHS). The EPG works only in Oracle XE, and Oracle Database 11g and greater. If you are going to use Oracle APEX on 10g editions of the database, you will need to use the OHS or APEX Listener, as the EPG is not supported for any version below 11g.

Because the EPG runs entirely within the 11g database, and it comes pre-installed (but not pre-configured), it is simple to maintain. As it is not possible to separate the XML DB HTTP listener from the database, Oracle recommends not using it for internet-based applications, due to the potential security risk when exposing the database server directly to the Internet.

A number of other limitations exist for EPG when compared with Oracle HTTP Server, including features such as dynamic HTML caching, system monitoring, and logging in the Common Log Format.

The EPG is an appropriate solution for setting up APEX quickly for a proof of concept approach, development environments, or for low-volume intranet-based applications. EPG is an easy and convenient setup, but this comes at the price of flexibility and performance. It should not be considered for serious production environments.

Loading Ext JS onto the Embedded PL/SQL Gateway

Before loading Ext JS, the EPG needs to be configured and enabled. To check this step has been done, attempt to log into APEX as the admin user from a browser using http://machine.domain:port/apex and substituting the appropriate values for machine, domain, and port (default is 8080). If this is unsuccessful, review the database installation documentation on Application Express post-install configuration steps before proceeding.

When using the EPG, the Application Builder images referenced by the alias /i/ are stored in the database within the Oracle XML DB repository. You can access these images using either WebDAV or FTP. I've found FTP to be more reliable, especially when doing bulk file transfers, hence the instructions will be for FTP.


If you're interested in accessing the XML DB repository using WebDAV, Dietmar Aust provides instructions in his blog at

The first thing to do is check whether FTP has been enabled, which is done using the following SQL code:

SQL> select dbms_xdb.getftpport from dual;

If the FTP port is set to 0, FTP is currently disabled.

To enable it, connect to SQL*PLUS as XDB or SYSTEM, or any account with DBA or XDBADMIN privileges, and issue the following commands:

SQL> exec dbms_xdb.setftpport('2100'); -- 1
PL/SQL procedure successfully completed.
SQL> alter system register; -- 2
System altered.
SQL> select dbms_xdb.getftpport from dual; -- 3
  • Statement 1 sets the FTP port to 2100.

  • Statement 2 forces the database to reregister with the listener immediately.

  • Statement 3 verifies the port has been changed successfully.

You should now be able to log in via FTP. For the time being, it's easier to log in as SYSTEM. There are many FTP tools available, so it's just a matter of choosing one based on personal preference. In my case, I'm using the free FileZilla client, available from, in both Windows and Linux versions.

If you're using XE, you should see something similar to what's shown in the following screenshot:

Create a new folder named /ux/ in the XML DB repository, and then upload the Ext JS files into this folder. Remember, you can either deploy all the files in the Ext SDK, or just the minimal set comprising the ext-all.js file and adapter and resources folders.

To verify the Ext JS library is now accessible on the web server, check whether you can successfully fetch one of the files. Substituting the appropriate host and port values, use your browser to verify you can now see the Ext JS assets:

http://host:port/ux/ext-3.3.1/resources/images/default/tree/drop-yes.gif should show a tick, as seen in the following screenshot:

If you decided to do the full Ext SDK install, now is a good time to bookmark the documentation and samples:

  • http://host:port/ux/ext-3.3.1/docs/index.html

  • http://host:port/ux/ext-3.3.1/examples/index.html


Oracle APEX listener

The Oracle APEX listener is a Java-based replacement for the OHS mod_plsql plugin for all Oracle APEX releases. It provides a number of advantages over mod_plsql, including file system caching, native Excel uploads, generating PDF documents using Apache FOP (Formatting Objects Processor), and improved file uploading to support multiple file uploads for the first time. The APEX listener has been designed to be extensible, allowing developers to customize pre-and post-processing of form submissions, file uploads, among other things. The APEX listener is another key feature certain to increase adoption of the technology.

The APEX Listener is a Java servlet, capable of running on just about any application server that follows the Java Enterprise Edition (JEE) standard. Oracle provides instructions for deployment to Oracle WebLogic, OC4J, and Oracle Glassfish.


The Oracle APEX listener and installation guide is available at

Opening up the choice to a variety of web servers allows us to take advantage of features such as HTTP compression, which is not installed on the Oracle HTTP Server. (It can be configured, but is not supported by Oracle.)

HTTP compression makes better use of network bandwidth by compressing data on the server before sending it to the client. This allows content to be sent over the network in a more compact form and can result in a dramatic reduction in download time, reducing latency in your application and an improved user experience.

Given the enhanced functionality it offers over mod_plsql, the Oracle APEX listener will eventually become the preferred listener for Oracle APEX. However, in the short term, most production systems will continue to use Oracle HTTP Server with mod_plsql, until the new listener has been proven by early adopter sites.

Loading Ext JS for the Oracle APEX listener

Once you have installed your choice of web server, the Oracle APEX Listener, and uploaded the APEX images using the Oracle Installation Guide, you can load Ext JS.

The process for loading Ext JS is similar for each of the referenced web server options (Oracle WebLogic, and OC4J, and Oracle Glassfish). The instructions here are for Oracle Glassfish.

You can deploy directly to a physical directory on the web server:

  1. 1. Create a folder named ux in GLASSFISH_DIRECTORY/domains/DOMAIN_NAME/docroot.

  2. 2. Copy the Ext JS files to GLASSFISH_DIRECTORY/domains/DOMAIN_NAME/docroot/ux.

Or to a virtual directory on the web server:

  1. 1. Copy the Ext JS files to the web server, for example C:\playpen\web\ux.

  2. 2. In the GlassFish Admin Console, expand Configuration | Virtual Servers. Select server, then scroll to the bottom of the page and click the Add Property button. Enter alternatedocroot_1 in the Name field, and from=/ux/* dir=C:/playpen/web/ in the Value field, as shown in the next screenshot. This will map the URL http://hostname:port/ux/ to the physical directory C:/playpen/web/ux/.

Remember that you can either deploy all the files in the Ext SDK, or just the minimal set comprising the ext-all.js file and the adapter and resources folders. When adding a virtual directory alias, you may need to restart the web server before the alias is recognized.

To verify that the Ext JS library is now accessible on the web server, just check that you can successfully fetch one of the files. Substituting the appropriate host and port values, use your browser to verify you can now see the Ext JS assets:

http://host:port/ux/ext-3.3.1/resources/images/default/tree/drop-yes.gif should show a tick, as seen in the preceding screenshot.


Overviewing the production setup

Consider the architecture diagram in the next screenshot:

The diagram is a well-known and generally accepted Internet-Firewall-DMZ-Firewall-Intranet architecture and shows the following zones:

  • External internet, outside the DMZ firewall

  • External web server tier acting as a reverse proxy between the DMZ firewall and the Intranet firewall

  • Corporate intranet behind the Intranet firewall

If your Oracle APEX instance is going to be used only for Intranet applications, we need to consider only the corporate intranet component on the right-hand side of the diagram. This is the basic configuration documented earlier for the Oracle HTTP server.

For Internet-accessible applications, security becomes a much more important factor. Various high-profile hacking attacks have proven that web security is one of the most critical issues facing any business that conducts its operations online. Compared to intranet-only applications, internet-accessible applications have far larger numbers of potential hackers.

Firewalls are configured to allow only specific types of access (HTTP/HTTPS). In DMZ architectures, firewalls are used to restrict the flow of network data so that all inbound traffic from the internet and outbound traffic from the intranet must be processed by web servers acting as proxy servers in the DMZ zone. By using a reverse proxy server, such as Oracle Web Cache or HTTP Server in tandem with internal and external firewalls, you can greatly reduce the risk of exposing your backend data resources.

So what exactly does a reverse proxy do? When a client sends a request to your website, the request goes to the proxy server. The proxy forwards the client's request through a specific path in the intranet firewall to the content web server. The content web server processes the request, passing the result back through the path to the proxy. The proxy server sends the information to the client, rewriting any URLs as though it was the actual content server.

Reverse proxies can be additionally configured to perform extra tasks such as compressing files to optimize network traffic, or facilitating secure transmission of information utilizing Secure Socket Layers (SSL), to provide an encrypted connection between the proxy server and the client.


Using Ext JS in a hosted APEX environment

Oracle APEX is designed to support hosted development, where the only access you have to your workspace is via a browser. The Application Builder and SQL Workshop contain all the necessary functionality to build an application from scratch without any other tools.

Typically in a hosted environment such as, you don't have access to the web server to upload the Ext JS files. In this situation, you can take advantage of Ext partnering with CacheFly, a global content network, to provide free CDN hosting for the Ext JS framework.

A Content Delivery Network (CDN) is a collection of web servers distributed across multiple locations to deliver content more efficiently to users. The server selected for delivering content to a specific user is typically based on a measure of network proximity.

For example, the server with the fewest network hops or the server with the quickest response time is chosen; that is, using a CDN to deliver static content, such as Ext JavaScript, CSS, and images will result in your pages getting downloaded significantly faster.

In the hosted environment, you don't load the Ext files onto the server, instead simply reference the Ext content in your Oracle APEX page templates from the CacheFly site. The following code will be added to Oracle APEX page templates:

<link rel="stylesheet" type="text/css" href="" />
<script type="text/javascript" src=""></script>
<script type="text/javascript" src=""></script>

We will look at page templates and how to integrate Ext JS content in greater detail in Chapter 3, Building an Ext Theme into APEX.


Installing a source code repository

One of the very first tasks you should do in any software project, even before you write a single line of code, is to install a source code repository. This is where the development team keeps all of its code in a centralized location, using version control software to track and manage changes to files over time.

Version control systems are appealing to developers because they back up source code and keep track of changes. So when a new code version introduces a bug, it's easy to compare with earlier versions using text differencing tools to highlight what's changed and identify the problem.

Managers like version control systems because it helps prevent loss of data, and provides tagging capabilities for releases making it very easy to check a production version for error correction without disrupting the current development version.

I'll be discussing Subversion ( in this book, so if you're already using another version control system, the same principles apply, although the solution might be a little different.

Subversion (SVN) has rapidly become the de facto standard free/open source version control system today. Binary versions of SVN are available for all major operation systems. Installation of the SVN server is very much dependent on the operating system, so refer to the installation instructions for your operating system.

VisualSVN Server ( is an excellent free solution for the Windows systems, and with a one-click installation, you really can't get an any simpler setup. There are a number of third-party clients available; TortoiseSVN is a popular choice on Windows, as it is integrated directly into Windows Explorer.

For batch programming, you will need to use a SVN command-line client, such as CollabNets' version ( Subversion's integrations into various IDEs are also common, including JDeveloper, SQL Developer, TOAD, and Apanta.

The previous screenshot shows the VisualSVN Server, containing a single repository named apex-solutions, with two projects named jquery and playpen respectively. The playpen project is used for this book, and contains a standard recommended layout: the trunk directory to hold the "main line" of development, a branches directory to contain branch copies, and a tags directory to contain tag copies.

The question of what to store in SVN is also partly answered in the screenshot we just saw. The short and simple answer is "everything to do with the project". You can use the example of a new developer starting on the project as a litmus test. Given a virgin machine, they should be able to do a single checkout, and be able to do a full build of the system. You may make exceptions for things that are large, or complicated to install and stable, for example, the database and IDE tools, such as JDeveloper or SQL Developer.

Here is a basic list of some of the types of files you would typically want to store in your code repository for an Oracle APEX application:

  • Database object scripts: Everything you use to define your application in the database. This includes a current Oracle Initialization Parameter (init.ora) file, scripts to create your users, tables, views, packages, procedures, functions, and so on. If your application isn't too large, you could also include database schema exports to ensure you include absolutely everything.

  • APEX application and related files: You can export and import your application definitions, including workspace users, application, CSS, images, files, themes, and user interface defaults stored in Oracle APEX. These exports provide a complete snapshot of your APEX application, allowing you to deploy it to another environment, or restore your application to an earlier state. The export files also allow you to recover individual components.

  • Web server assets: Your Ext JS files, application JavaScript, CSS, images, and so on, and all configuration files for your web server.

  • Documentation: Project management documentation, as well as the user help and administration documentation.

  • Utilities and command scripts: This is a catch-all for scripts that don't fit into any of the previous categories. Examples include scripts used to export and import the application, to stop and start processes, FTP files to web servers, deploying the application to other environments, and so on.


Automating the build process

Version control systems commonly provide command line interfaces, providing you the opportunity to automate source control tasks you regularly perform using batch files.

One of the tasks you'll want to do on a regular basis is to back up and check in your APEX application into Subversion.

Oracle has provided a Java utility named APEXExport that allows you to export Oracle APEX applications from the command line, without requiring a manual export via the web interface. We'll go through how to set up your Subversion repository to support a fully automated backup process.

Once again, I will provide instructions for a Windows environment, but because APEXExport is a Java utility, minor adaptations to the instructions will allow you to run it in a Linux/Unix environment.

Configuring and using APEXExport

In the root of the Oracle APEX installation files, you will find a utilities folder containing a readme.txt file. The file provides detailed instructions on how to set up and use the APEXExport utility.

Pre-requites for the utility include installation of the Java Development Kit (JDK) of version 1.5 or greater, and the inclusion of classes12.jar in the CLASSPATH.

I wrote earlier that we should store "everything to do with the project" in our SVN repository, so that with a single checkout we could do a full build of the system. Also raised was the possibility of exceptions to this rule for things that are large, or complicated to install and stable, for example, the database and IDE tools such as JDeveloper.

Let's make an exception by assuming the JDK is already installed on your computer—either as a standalone installation, or as part of an IDE for example in SQL Developer or JDeveloper. If you didn't want to make this exception, add SQL Developer (which includes the JDK) into your SVN repository.

The classes12.jar file is the Oracle JDBC library for Oracle database 10g, found in the %ORACLE_HOME%\jdbc\lib directory. For Oracle database 11g, the equivalent file is ojdbc5.jar for Java 5 or ojdbc6.jar for Java 6. Generally for Oracle database 11g, you should use ojdbc6.jar.

Because developers may have very different setups on their computers, even within a small team, the easiest way to manage the location of the JDBC library is by using the Oracle Instant Client. The Instant Client allows you to run your applications without the standard Oracle client, and includes additional libraries for SQL*Plus.

Instant Client comes in two versions—Basic and Basic Lite. Both versions are suitable for Oracle APEX: Basic Lite is a smaller version of the Basic, with only English error messages and supporting only specific character sets, including AL32UTF8, used by Oracle APEX. Installation is simply extracting the Instant Client files into a directory. Download a copy of Instant Client for your database version and operating system from

Also download the SQL*Plus Instant Client, which is installed by extracting it into the same directory as the Instant Client.

Before we go any further, let's take a look at the intended layout of the SVN repository.

The preceding screenshot shows the layout of my SVN repository. You can see that within the trunk folder of the apex-solutions/playpen project, I've created a series of folders to partition my application logically. The bin folder holds my batch scripts, including the backup_apex.bat script, detailed shortly. Also, note the oracle folder, which contains Oracle Instant Client software for database releases 10.2 and 11.1. And finally, the utilities folder to which I've copied the APEXExport.class and APEXExportSplitter.class from the Oracle APEX installation.

Let's look at the code for backup_apex.bat:

@echo off
REM Set BASE to parent directory of this scripts location.
set HOME=%CD%
cd /d %~dp0
cd ..
set BASE=%CD%
cd /d %HOME%

The setlocal command ensures any environment changes are localized to the batch script. Setting the HOME variable allows the script to return to the execution start directory.

Next, navigate to the script location using the convoluted expression cd /d %~dp0, and in turn to its parent directory to set our BASE variable to be the "root" folder of our repository. For example, my repository path to the backup_apex.bat script is C:\playpen\bin\backup_apex.bat, so my BASE variable becomes C:\playpen. Your repository path could be a completely different, but provided your script finishes with \bin\backup_apex.bat, the BASE variable will be set correctly.


Knowing the path to our BASE folder is an important step, as we now have our bearings to reference our java libraries and navigate to other folders.

REM ----------------------------------------------------------
REM Database 11g specific
REM ----------------------------------------------------------
set CLASSPATH=%CLASSPATH%;%BASE%\oracle\instantclient_11_1\ojdbc6.jar
set CLASSPATH=%CLASSPATH%;%BASE%\oracle\utilities
REM ----------------------------------------------------------
REM Database 10g specific
REM ----------------------------------------------------------
REM set CLASSPATH=%CLASSPATH%;%BASE%\oracle\instantclient_10_2\classes12.jar
REM set CLASSPATH=%CLASSPATH%;%BASE%\oracle\utilities

Next, we set the CLASS_PATH to our JDBC drivers and export utilities. I'm using the Oracle JDBC library for Oracle database 11g, with the equivalent Oracle 10g version commented out.

cd /d %BASE%\database\apex
REM Make sure our local copy is up to date
if not ("%SVN_HOME%") == () "%SVN_HOME%"\svn update

We change folders, as the APEXExport utility exports files into the current working directory.

Before running the export, we update our repository, refreshing the current directory and subdirectories using the command-line version of SVN. In this case, I'm referencing an externally defined environment variable SVN_HOME to identify the home directory of the SVN command line client. SVN_HOME is usually defined as a Windows environment variable when the SVN command line client is installed.

Using conditional logic, allows this step to be skipped if the variable has not been set for your computer.

java oracle.apex.APEXExport -db mark-pc:1521:XE -user playpen -password playpen -workspaceid 1038420889063720 -skipExportDate

APEXExport allows you to either export an individual application, a workspace, or the entire Oracle APEX instance. The utility is run through Java using a JDBC connection to the database; for complete syntax details refer to the readme.txt file included in the utilities folder.

In this batch script here, I am exporting a workspace. To find out the workspace ID for your environment, you can run the following query in SQL Workshop within Oracle APEX:

select v('WORKSPACE_ID') from dual

Exporting a workspace will create a separate script for each application in your workspace. So for application 150, a file named f150.sql will be created.

REM Check if SVN_HOME has been set
if ("%SVN_HOME%") == () goto :no_svn_home
"%SVN_HOME%"\svn add *.sql --force
"%SVN_HOME%"\svn commit -m "Automated backup and check in."
goto exit
echo ERROR
echo ERROR: SVN_HOME environment variable is not set, no automated SVN check in.
echo ERROR
goto exit

Once the APEX application exports have completed, we check the export files into SVN. We start by seeing if the SVN_HOME environment variable is set; if not, skipping the check-in and raising an error message. If set, the svn add command is used to ensure that when any new applications are added to the repository, then the svn commit command is executed, with a required check-in message.

SVN detects whether or not files have been modified as part of the check-in process, so if no changes have been made to an application, it won't be checked in. The following screenshot shows the output of the batch script, where five applications are exported from the database, but only one had been modified, so only that application was transmitted to the SVN repository.

Now that we have our batch script set up, the last step is to schedule the script to be run automatically every day. Ideally, this would be configured to run on the SVN server; however, you could run the script from team members' computers, because as we've just seen in the previous screenshot, only modified applications are committed to the SVN repository.

Scheduling the batch file to run automatically each day is simply a matter of calling the batch script, using the Windows built-in scheduler to define when and how often you want the batch file to run, as shown in the following screenshot:


For additional security, it would be preferable to pass the password to the batch file using the scheduled task, rather than having it directly in the batch file.

More ideas for automating the build process

As developers, we work to automate processes for end-users; yet, many of us overlook opportunities to automate our own development processes. Using a single source repository for all your application assets and using simple scripts as we have here to schedule repetitive tasks are the first steps in a journey towards continuous integration.

Version control systems, including Subversion, include hooks, so that the act of checking a file into the repository triggers an event to execute your hook program or batch file. So in an APEX application context, you could automatically FTP your web assets to your web-server. Or checking in a JavaScript file may trigger a process to minify and combine your JavaScript files into a single application JavaScript file, something we will cover in Chapter 11, Performance tuning your JavaScript.

Oracle SQL Developer 2.1 now includes a unit testing framework that allows you to build a set of sequential steps to create test cases for testing your PL/SQL code. Along with providing a GUI interface within SQL Developer to run unit tests and suites, a command line interface is provided for both Windows and Linux. Here, you could schedule a process to run your unit tests overnight, and the next morning check a unit testing report to verify the results.

Other opportunities for automation include generating documentation, website pages, statistics, and distribution files.

Automating your build process can use sophisticated Continuous Integration tools, which may require significant initial setup, or grow organically starting with small and humble beginnings using command scripts as we've done here.


Either way, the most important point is to keep looking for opportunities to improve your software quality and streamline development and deployment processes.


Setting up a local web server

It's very worthwhile having a local web server on your computer, in addition to the team's web server. This allows you to modify and test "application" JavaScript in isolation from the rest of the development team. For some reason, people get upset when you're making a change to a core JavaScript function and suddenly every page stops working!

Setting up a local web server is much the same as we've outlined previously for the Oracle HTTP Server or APEX Listener. The only real change is to set your /ux/ alias to point to your SVN repository for your JavaScript, CSS, and other web assets.

By doing this you can work directly on the JavaScript files locally and not need to copy them onto the web server every time you need to test a change.



In this chapter we've discussed the merits of the different web server options available for Oracle APEX, covering the Oracle HTTP Server, the Embedded PL/SQL gateway, and the Oracle APEX Listener. Installation of the Ext JS library into each of these environments, including using Ext JS in a hosted APEX environment where you don't have access to the web server, has also been covered.

In setting up for success, we discussed the importance of taking a little extra time at the beginning of a project to set up a productive development environment. To aid the development process, we set up a SVN source code repository and included some tools to allow the automated backup and commit of an Oracle APEX workspace to the repository. A number of other automation opportunities were also discussed.

Let's now start to get acquainted with the Ext JS library, looking at some of the functionality it provides for manipulating the Document Object Model (DOM).

About the Author

  • Mark Lancaster

    Mark has been delivering business solutions using Oracle tools and technology since 1995. He switched to using Oracle APEX in 2007 after using mod_plsql for years - "APEX is much much better". He has had the good fortune of consulting for a wide variety of organizations in industries including commercial fishery management, mineral resources, superannuation regulation, property asset management, distance education, casinos and debt collection. Mark is an Oracle ACE, having been actively involved in the Oracle community for many years on national and state committees, as well as writing articles and presenting at conferences.

    He is the AUSOUG QLD Branch President, and maintains a blog at

    Browse publications by this author
Oracle Application Express 4.0 with Ext JS
Unlock this book and the full library FREE for 7 days
Start now