How To Integrate Oracle APEX With Subversion

Simpler version control for Oracle
Application Express
Rui Barata
rui.pedro.barata@gmail.com
December 2017
Version 1.0
Abstract
This paper describes how to integrate Oracle APEX with a version control system by using local
database instances installed on the developers’ workstations.
Simpler version control for Oracle Application Express
Table of Contents
Abstract ................................................................................................................................................... 1
Introduction ............................................................................................................................................ 2
The development database is shared between all developers ...................................................... 2
The development APEX environment is shared between all developers ....................................... 2
Existing strategies ................................................................................................................................... 4
Build Options....................................................................................................................................... 4
A schema per developer ..................................................................................................................... 4
Java splitter ......................................................................................................................................... 4
Proposed strategy – local databases ...................................................................................................... 6
Prepare a Source Control structure .................................................................................................... 7
Set up each developer workstation .................................................................................................... 8
Ensure the workstation has enough resources............................................................................... 8
Install the Oracle database into the workstation. .......................................................................... 8
Install APEX into the workstation ................................................................................................... 8
Install application schemas ............................................................................................................. 8
Install the APEX workspaces ........................................................................................................... 9
Set up an automated warning system ................................................................................................ 9
Development cycle............................................................................................................................ 11
1. Merge from trunk to branch ..................................................................................................... 13
2. Load the APEX application into the workstation’s database from the developer’s branch ..... 14
3.Change the application, using the APEX Application Builder .................................................... 15
4.Add a build option...................................................................................................................... 16
5.Export the APEX application, save it to the developer’s branch ............................................... 16
6.Commit to SVN........................................................................................................................... 16
7.Merge from trunk to branch ...................................................................................................... 16
8.Merge from branch to trunk ...................................................................................................... 17
Updating the local databases............................................................................................................ 17
Results ................................................................................................................................................... 18
References ............................................................................................................................................ 18
Acknowledgments................................................................................................................................. 18
About the Author .................................................................................................................................. 18
Page 1
Introduction
In 2015 I started working for a new company, maintaining and expanding an existing APEX
application.
The development team is currently composed of four developers, using a very standard
infrastructure – Production, QA and Development database/APEX instances, and the Subversion
source control system.
Our development flow was also quite standard for an APEX shop, going like this:
1) All developers work on a common Development instance (Oracle + APEX)

2) Whenever a developer has completed a feature, a release script is prepared. It contains all
object changes (tables, packages, APEX application exports)
3) This script is executed against the DEV and QA databases, to allow the testing team to start
work on it. As developers have different schedules and speeds, a new release will also
generally contain unfinished features from other developers.
4) On the last days of each sprint, all development stops and developers concentrate on fixing
bugs. No new development takes place.
5) At the sprint’s end, the testing team certifies the latest release in the QA database. This is
then deployed to Production. If this release is not certified, the deployment is delayed and
the sprint is lengthened until all critical bugs are fixed.
6) A new sprint starts, and developers start new developments on the Development database
This was my first experience with working with APEX with a larger group, and some problems
became evident. Namely,
The development database is shared between all developers

That is, if one developer changes a database object all other developers are affected.
As an example, suppose that developer Anne is changing package PKG_A as part of her tasks. At the
same time, fellow developer Bert has just changed table TBL_B as part of his tasks. This change
invalidates several packages, including PKG_A. Anne will therefore have to wait until Bert fixes
PKG_A before she is able make do her own changes to it.
For the same reason, it is not possible for two developers to change the same PL/SQL package
simultaneously.
The development APEX environment is shared between all developers

That is, changes made by one developer to an APEX object will be immediately seen by all other
developers.
Say, for instance, that developers Anne and Bert are both updating the same application. Bert’s
change will take 3 days and affect several pages, which will be in error until the change is finished.
Anne’s change will take 2 days, and affect one those pages. As such, Anne’s and Bert’s changes can’t
be done at the same time.
This in effect means that the 2 changes can’t be done in less than 5 days, despite none of them
taking that long.
It also makes life harder for testers.
Page 2
Say, for instance, that developers Anne and Bert are both updating the same application and start at
the same time. As it happens, Anne’s change is very simple and takes one day to complete. Bert’s
change will take 3 days to do, and make the application impossible to test until it is finished.
This means that it won’t be possible to release the application to QA until Bert has finished his
changes, despite Anne’s change having been finished for 2 days when that happens.
Page 3
Existing strategies
How can these problems be tackled? There are several documents on this subject, as e.g.
• Life Cycle Management with Oracle Application Express (Revision 2)

http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-life-cycle-
management-wp-3030229.pdf
• Professional Software Development using Oracle Application Express
http://www.rwijk.nl/AboutOracle/psdua.pdf
What are the proposed solutions?
• Build Options
• A schema per developer
• Java splitter
Each of these has its own advantages and drawbacks.
Build Options
Build Options are a setting in APEX that allow hiding specific elements (page items, regions, pages,
etc), from a final user. That is, they allow the hiding of a new feature in the application until it is
completed. Still, it is not possible to hide a change to an existing feature.
A schema per developer

This helps with the development of database objects (packages, tables, etc). But it does not solve the
problems related to APEX itself.
Java splitter
To do this the following tools are needed:
• APEXExportSplitter: a Java application that exports the individual components of an APEX

application into their own files.
• APEX_APPLICATION_INSTALL. Set_Offset: an APEX object ID relocation procedure that is
available from the APEX PL/SQL packages.
In this scenario, the Subversion repository contains the individual component files (as exported by
APEXExportSplitter) rather than full APEX application exports (e.g. “f100.sql”).
This method relies on each developer having their own APEX workspace containing their own copies
of the application being developed, and using a combination of APEXExportSplitter,
APEX_APPLICATION_INSTALL. Set_Offset and custom shell scripts, to ensure that there are no APEX
object ID collisions and that the different versions of each application developed in parallel by the
developers are mergeable.
This looked very complicated, and probably not appropriate for the dimension of our team.
Why does it need to be so complex?
The problem comes from the way APEX object IDs are managed by APEX, in a way that is not friendly
to source control systems.
Page 4
Life Cycle Management with Oracle Application Express (Revision 2)
Using regular ‘diff’ tools to analyze the deltas between two application export files, exported from
different environments, will raise a very large number of false positives, making it nearly impossible
to identify true differences in functionality. The main reason for this is that every component ID is
different when an application is exported from a different environment.
Could there be a simpler way of doing this?
Page 5
Proposed strategy – local databases

Note that all of the complexity described above comes from the fact that APEX object IDs differ
between applications inside a single development Oracle database.
This means that having each developer working on their copy of the application won’t work, as it will
be very difficult to merge them back afterwards.
What if each developer had their own development Oracle database?
This is what we did, and it worked.
The main advantage of this approach -- each developer with their own database -- is that it
completely solves ID conflict problems. Before starting development, each developer loads the
application into their own database, therefore preserving all the object IDs without causing ID
collisions.
It works like this:
Page 6
To implement it, we need to do the following:
Prepare a Source Control structure

We start with a standard structure, with the “trunk” folder further subdivided by Oracle schema and
component type
Under the “branches” folder, we create a subfolder for each developer.
In our configuration, each developer subfolder contains 2 subfolders:
• “branch”, which is a fork of “trunk”

• “deltas”, containing information on releases
Then we export all our APEX applications and the workspace itself to the apex folder in trunk.
Page 7
Each developer then needs to set up their own personal instance:
Set up each developer workstation

Ensure the workstation has enough resources
Namely, ensure that the workstation has enough RAM and free disk space
Install the Oracle database into the workstation.

There are two options for this:
1) Oracle XE
A free version of Oracle 11.2 Standard, which can accommodate up to 11 GB of data
2) Oracle OTN downloads
The database version to install on the developer’s workstation must be a close as possible to the
existing version on the Development server.
Install APEX into the workstation

Download and install the same version as installed on the Development Server, from
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
Follow the steps that refer to the EPG install of APEX for that version
Using the ADMIN APEX user, install the workspace dump files from SVN. This should include all APEX
developer users.
Install application schemas

Define the target size of the workstation’s database, according to:
1) If using an OTN database, the space on the workstation’s disk that has been allocated to it
2) If using XE 11G, also consider that database’s 11 GB database size limit
If possible, delete data from the Development database as needed so that exporting the application
schemas will fit into the target database size.
Transfer those schemas into the workstation database, either by export/import or a transfer tool like
e.g. SQL Developer. If needed, restrict some source database table data at this step so that the
workstation’s database remains under the target size.
Page 8
Confirm that after the data transfer all constraints, triggers, packages, etc are enabled and valid.
Note that once a developer database has been set up, it is possible to copy it to the other
developers’ workstations.
Install the APEX workspaces

To ensure that all APEX object IDs are the same, all the developers need to work over the same
workspace. This is achieved by exporting the workspace from the APEX DEV database and then re-
importing it into each developer’s database.
The workspaces can be exported from the APEX builder as shown below.
Given the importance of the workspace export files to the development process, they should also be
stored in version control.
Set up an automated warning system

Sometimes, particularly at the beginning of the implementation, one of the developers will forget
one of the steps that are outlined below. This can potentially cause a change to not be propagated
into the trunk version, as explained below.
To prevent this problem from happening, a warning system can be set up that warns developers
when a change is in risk of being lost.
This warning system works like this:
1) As part of the development process, each developer, after finishing each change in their
APEX instance, adds a new Build Option that uniquely identifies that change
Page 9
2) A script is set up to use these build options to detect lost changes. This script will
a. access the source control repository
b. look into the “trunk” branch
c. fetch the 2 latest versions of the APEX export files for each APEX application export
in trunk and compare them.
d. Look for any build options that were present in the previous version that are no
longer present in the latest
e. send a warning email to developers if any build missing build options were detected
This should be set up in a server to run periodically – say every 10 minutes.
An example script for Subversion and Linux could look like this:
#!/bin/bash
TOP_DIR=/home/svncheck
TMP_DIR=$TOP_DIR/tmp
SVN_USERNAME=check_user
SVN_PASSWORD=******
APPLICATION_NAME=f100
SVN_URL=svn://svn.company.com/trunk/apex/$APPLICATION_NAME.sql
EMAIL_ADDRESS=developers@company.com
rm -f $TMP_DIR/$APPLICATION_NAME.sql* 2>/dev/null
Page 10
svn export --username "$SVN_USERNAME" --password "$SVN_PASSWORD"

"$SVN_URL" $TMP_DIR/"$APPLICATION_NAME.current"
PREVIOUS_VERSION=$(svn log -l 2 -q --username "$SVN_USERNAME" --
password "$SVN_PASSWORD" "$SVN_URL" | grep -v -- '--------------' |
tail -1 | cut -d" " -f1)
svn export --username "$SVN_USERNAME" -r $PREVIOUS_VERSION --
password "$SVN_PASSWORD" "$SVN_URL"
$TMP_DIR/"$APPLICATION_NAME.previous"
cat $TMP_DIR/$APPLICATION_NAME.previous \
| grep ',p_build_option_name=>' \
| sed s/",p_build_option_name=>'//" \
| sed "s/'$//" \
| sort \
| uniq > $TMP_DIR/$APPLICATION_NAME.build_options.previous
cat $TMP_DIR/$APPLICATION_NAME.current \
| grep ',p_build_option_name=>' \
| sed s/",p_build_option_name=>'//" \
| sed "s/'$//" \
| sort \
| uniq > $TMP_DIR/$APPLICATION_NAME.build_options.current
MISSING_BUILD_OPTIONS=$(diff
$TMP_DIR/$APPLICATION_NAME.build_options.previous
$TMP_DIR/$APPLICATION_NAME.build_options.current | grep '^<' | sed
"s/^< //" )
if [[ "$MISSING_BUILD_OPTIONS" ]]; then

mail -s "ATTENTION: Please review $SVN_URL" $EMAIL_ADDRESS
<EOF
ATTENTION: Please review $SVN_URL
Build options are missing in application $$APPLICATION_NAME:
$MISSING_BUILD_OPTIONS
EOF
fi
This example script will send an email to the developers if a new version of f100 is
committed to trunk that is missing a build option that existed in the previous version.
Similar scripts can be directed to the developers’ own branches, providing early warning
even before a new version flows into trunk.
Development cycle
It consists of the following steps:
1. Merge from trunk to branch
2. Load the APEX application into the workstation’s database from the developer’s branch
Page 11
3. Change the application, using the APEX Application

Builder
4. Add a build option
5. Export the APEX application, save to the developer’s

branch
6. Commit to SVN
(repeat 1 to 6 as needed)
7. Merge from trunk to branch (same as 1.)
8. Merge from branch to trunk
The typical cycle being:
• The developer gets one or more tasks or bugfixes

from the backlog
• For each one of them, goes over steps 1 to 6
• After the set of tasks is concluded, and the
APEX application in the developer’s
branch is in a releasable state, steps 7
and 8 are executed.
In effect, the APEX application exports cease to be “special”

files with regards to version control, and start being treated
as any other code file.
Page 12
1. Merge from trunk to branch

There should not be any major issues when merging the APEX export from trunk to the developer’s
branch, as all the APEX IDs should be the same. Collisions detected by SVN should mostly fall into
one of the following types:
Application Information
This conflict will always appear, and is completely harmless – it just reflects the fact that the export
timestamp of the file has changed. The developer should choose the “Mine” version.
Application Components Summary
Also harmless, any version can be chosen here.
Page 13
Updated timestamp in components
Also harmless, any version can be chosen here.
2. Load the APEX application into the workstation’s database from the developer’s branch
The APEX application export can be imported from the Builder interface, in which case the
application’s ID will have to be preserved.
Alternatively, the APEX application export can be executed as an SQL script against the default
schema of the APEX workspace. This has the advantages of being easier to add to a deployment
script, and of always reusing the application ID.
The default schema of an APEX workspace is defined in the Oracle APEX administration console as
shown below.
Page 14
It is very important to get this step right.
For an example of the kinds of problems that can be caused by not doing this properly, suppose that
Anne has just finished changing application f100. She has also merged her changes to trunk. Bert has
merged his branch from trunk, but forgot to load into his instance the APEX application that he is
working on.
That is, at the moment:
• Bert’s version of f100.sql in his Subversion branch includes Anne’s changes, but
• Bert’s version of f100 in APEX is old and doesn’t include Anne’s changes.
Bert starts developing over his APEX instance, and adds his changes to it. He then exports f100 to his
branch in Subversion and commits it.
When he merges his branch back to trunk, all of Anne’s changes are therefore erased from the
trunk’s version of f100.sql.
This is where the warning system explained above becomes very useful. If Anne took the care to add
a build option to his change, all developers should now receive an email warning that it has now
vanished from trunk.
3.Change the application, using the APEX Application Builder

This is when the actual development occurs, and takes most of the time in the development cycle.
Page 15
4.Add a build option
Some care must be taken here, as there seem to be a bug in APEX (at least in version 5.1) that
prevents applications from running. This bug is triggered when the number of Build Options reaches
about 330. If needed, old build options can be periodically deleted to make room for new ones.
5.Export the APEX application, save it to the developer’s branch
6.Commit to SVN
7.Merge from trunk to branch

Same as Step 1. The advantages of merging down from trunk before merging up to trunk are that
1. It minimizes the number of collisions that need to be dealt with while modifying the trunk
version, and
2. It provides the developer with the opportunity to review the merged version in their own
branch if any more complex problems arise.
Note that this is the moment mistakes are more likely to occur. Developers must bear in mind that
after this merge happens the APEX application residing in the developer’s local database instance
has been obsoleted, and should not be used until it is re-imported from Subversion (that is, until
Step 2 is done again).
Page 16
8.Merge from branch to trunk

This is the same as steps 1 or 7, and should be done as soon as possible after step 7. After this step is
complete, the trunk version of the APEX application is ready for deployment into the DEV and QA
environments.
Updating the local databases

Since each developer will have their own local database, it is important to keep their databases as
current as possible.
This can be done by applying to each development database the deployment scripts created to
deploy changes to the DEV/QA environments.
The best time to do that is between steps 1. and 2. , as needed for the development task.
Page 17
Results
At the time of writing we have been using this technique for well over a year. During this period, we
did more than 700 merges to our main APEX application.
It is now quite easy to work on several changes at the same time, or to make complex changes that
cross the sprint’s deadlines.
Also, incomplete features now never make it to trunk, and therefore
On the other hand, it takes some amount of discipline to work in this way – in that not following the
procedure might result in changes being deleted from trunk. On this, the automated warning system
explained above proved very useful as it allows solving any merging issues before they affect the
development process.
All things considered, it made our development process easier to manage, and allowed us to more
closely follow the Agile Development methodology that we started adopting at roughly the same
time.
References
[1] Life Cycle Management with Oracle Application Express (Revision 2)
http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-life-cycle-
management-wp-3030229.pdf
[2] Professional Software Development using Oracle Application Express
http://www.rwijk.nl/AboutOracle/psdua.pdf
Acknowledgments
I wish to thank everybody that contributed for making this paper possible. Namely, my colleagues
Michael Brown, Paul D’Arcy and Vaughan Leiberum for using and improving the procedures
described here, Caitriona Finnegan for always keeping our testing up and running during the
implementation, and Sheena Wade for trusting us that this could be done, and everybody else in the
team.
About the Author

Rui Barata is an Oracle database developer who has worked with Oracle APEX since 2006. He
currently works as a technical lead at ION Trading. His main areas of
expertise are the Oracle database, SQL, PL/SQL, shell scripting and APEX.
Page 18

How To Integrate Oracle APEX With Subversion

Uploaded by

Copyright:

Available Formats

How To Integrate Oracle APEX With Subversion

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

How To Integrate Oracle APEX With Subversion

Uploaded by

Copyright:

Available Formats

Simpler version control for Oracle

1) All developers work on a common Development instance (Oracle + APEX)

The development database is shared between all developers

The development APEX environment is shared between all developers

It also makes life harder for testers.

• Life Cycle Management with Oracle Application Express (Revision 2)

What are the proposed solutions?

Each of these has its own advantages and drawbacks.

A schema per developer

• APEXExportSplitter: a Java application that exports the individual components of an APEX

Why does it need to be so complex?

Life Cycle Management with Oracle Application Express (Revision 2)

different when an application is exported from a different environment.

Could there be a simpler way of doing this?

Proposed strategy – local databases

What if each developer had their own development Oracle database?

This is what we did, and it worked.

It works like this:

To implement it, we need to do the following:

Prepare a Source Control structure

Under the “branches” folder, we create a subfolder for each developer.

In our configuration, each developer subfolder contains 2 subfolders:

• “branch”, which is a fork of “trunk”

Each developer then needs to set up their own personal instance:

Set up each developer workstation

Install the Oracle database into the workstation.

Install APEX into the workstation

Install application schemas

Install the APEX workspaces

Set up an automated warning system

This warning system works like this:

This should be set up in a server to run periodically – say every 10 minutes.

svn export --username "$SVN_USERNAME" --password "$SVN_PASSWORD"

if [[ "$MISSING_BUILD_OPTIONS" ]]; then

1. Merge from trunk to branch

3. Change the application, using the APEX Application

4. Add a build option

5. Export the APEX application, save to the developer’s

7. Merge from trunk to branch (same as 1.)

8. Merge from branch to trunk

The typical cycle being:

• The developer gets one or more tasks or bugfixes

In effect, the APEX application exports cease to be “special”

1. Merge from trunk to branch

Application Components Summary

Also harmless, any version can be chosen here.

Updated timestamp in components

Also harmless, any version can be chosen here.

It is very important to get this step right.

That is, at the moment:

3.Change the application, using the APEX Application Builder

4.Add a build option

5.Export the APEX application, save it to the developer’s branch

7.Merge from trunk to branch

8.Merge from branch to trunk

Updating the local databases

Also, incomplete features now never make it to trunk, and therefore

About the Author