Oracle Application Express 4.0 with Ext JS

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 (http://agilemanifesto.org/principles.html) 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.

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

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 http://www.oracle.com/technetwork/developer-tools/apex/index.html, 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.

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.

The Ext JS SDK (software development toolkit) can be downloaded as a single zipped file from the Sencha website at http://www.sencha.com/products/extjs/download. 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.

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.

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.

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.

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.

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.

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).

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

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 http://www.oracle.com/technetwork/middleware/ias/index-091236.html.

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

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.

Note

If you're interested in accessing the XML DB repository using WebDAV, Dietmar Aust provides instructions in his blog at http://daust.blogspot.com/2006/03/where-are-images-of-application.html.

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;
GETFTPPORT
----------
0

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
GETFTPPORT
----------
2100

• 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 http://filezilla-project.org/, 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

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.

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.

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:

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.

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 http://apex.oracle.com, 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="http://extjs.cachefly.net/ext-3.3.1/resources/css/ext-all.css" />
<script type="text/javascript" src="http://extjs.cachefly.net/ext-3.3.1/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="http://extjs.cachefly.net/ext-3.3.1/ext-all.js"></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.

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 (http://subversion.apache.org/) 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 (http://visualsvn.com/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 (http://www.open.collab.net/downloads/subversion/). 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:

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 http://www.oracle.com/technology/tech/oci/instantclient/index.html.

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
setlocal
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.

Note

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
:no_svn_home
echo ERROR
echo ERROR: SVN_HOME environment variable is not set, no automated SVN check in.
echo ERROR
goto exit
:exit
endlocal

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:

Note

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.

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).