Building CI/CD for Oracle APEX – Part 1: Why Exports Are Not Enough

A few years ago, I was attending an Oracle event in Copenhagen when I heard a sentence that really stuck with me:

“APEX is not a file. It's data.”

And that's true. One of the great advantages of Oracle APEX is that much of an application lives inside the database. Application definitions, components, PL/SQL code, and the underlying data model can all be queried, modified, and inspected using SQL.

With the introduction of APEX Lang, this world is beginning to change as well, but that's a story for another time.

For me, one of the long-standing challenges has been managing database changes associated with Oracle APEX applications and automating their deployment.

Over the years, I've experimented with several approaches, but none of them gave me a truly satisfactory result. With the recent progress in AI tools, I decided to revisit the problem, and I've been working quite intensively on a new approach.

The complete CI/CD process isn't ready yet, but I've already learned several interesting lessons along the way. I'd like to share them in this series.

The APEX Deployment File Isn't the Biggest Problem

Exporting and deploying an Oracle APEX application is relatively straightforward. You can generate an application export file and import it into the target environment.

The real challenge, at least in my experience, lies in the database objects behind the application.

During development, new tables, columns, views, triggers, PL/SQL packages, and other objects are created, while existing objects are modified. These changes may also involve privileges, dependencies, and other configuration details.

The difficult part isn't necessarily generating the DDL statements. It's reliably keeping track of what has changed and determining which changes need to be applied to the next environment.

For example, when we add a new column to a table in DEV, that change also needs to be applied to TEST and eventually PROD. The same applies to a modified view, package, or trigger.

In our deployment process, we rely on SQL scripts prepared by developers. However, these scripts don't necessarily provide a complete picture of the actual differences between two environments.

And manual administration and deployment can easily lead to mistakes. A missing object, a forgotten privilege, or a change executed in the wrong order may be enough to cause problems.

Of course, we can generate complete schema exports and try to compare them. In practice, however, this isn't as simple as it sounds. Generated DDL may contain environment-specific information, object definitions may appear in a different order, and there's an important difference between modifying an existing table and creating a new object.

That's why I've been thinking about reversing the usual approach.

What if I started with the actual state of the DEV, TEST, and PROD databases instead of a manually maintained list of changes to deploy?

If I can reliably identify the differences between two environments, I should eventually be able to use that information to create a deployment plan and, later, a deployment package.

This doesn't mean that every difference should automatically be deployed. But at least the comparison wouldn't depend entirely on every developer remembering to document every change.

That became the starting point for my new approach.

The Environment I'm Working With

At Enrol Consulting, we use three independent Oracle Autonomous Database environments for our internal development projects: DEV, TEST, and PROD.

These databases contain the schemas, Oracle APEX workspaces, and applications belonging to our different projects.

About a year ago, we also created a fourth database to host our internal application called the Tenancy Management System (TMS).

TMS wasn't originally designed for CI/CD. We use it to manage our different environments centrally, including distributing configuration data, managing users and privileges, and monitoring scheduled background processes.

Each of the three environments has its own technical schema and APEX workspace:

• TMS_DEV and TMS_DEV_WKS

• TMS_TEST and TMS_TEST_WKS

• TMS_PROD and TMS_PROD_WKS

The TMS application, running in the central ENROL_TMS database, communicates with these environments through APIs.

Figure 1. Architecture of the Enrol DEV, TEST, and PROD environments and their connection to the central TMS system.

Since this architecture was already in place, it seemed natural to build the new solution on top of it.

My goal was to have TMS regularly collect the current database and APEX state of all three environments and turn that information into something that could be compared consistently.

In the longer term, I'd like TMS to use these differences to propose deployment changes and, with the help of AI, generate verifiable deployment scripts.

The audit information collected alongside the current state may also help explain when a particular change occurred and which developer's activity it was associated with.

How ChatGPT and Codex Helped

I used ChatGPT and Codex together while designing and building the solution.

ChatGPT mainly helped me think through the problems, discuss possible approaches, and design the architecture. Codex helped with examining database objects, developing PL/SQL code, and testing the changes.

This significantly accelerated the process. I could experiment with ideas and technical approaches that would probably have taken much more time to implement manually.

However, I also quickly experienced one of the risks of AI-assisted development.

Whenever we encountered a problem, AI would often suggest another mechanism to solve it: a new table, an additional package, another status flag, an extra validation step, or a more elaborate processing workflow.

Individually, these suggestions often made sense. But after a while, I noticed that the system was becoming increasingly complicated.

The first version, for example, had separate mechanisms for DDL auditing, collecting APEX snapshots, and tracking APEX Developer activity. It also involved several data transfer and state management processes, snapshot identifiers, watermarks, and historical tables that kept growing.

The solution worked, but its complexity and resource requirements continued to increase.

Eventually, I felt that we were spending too much effort solving problems introduced by the solution itself, without making proportional progress toward the original goal.

At that point, I decided that I didn't want to add more features. I wanted to simplify the design significantly.

But first, I had to reformulate the problem.

The question was no longer how to store as much information as possible about every database change. Instead, it became:

What is the minimum information I actually need to reliably identify the differences between two environments?

For me, this was also an important lesson about working with AI. AI can be extremely helpful during implementation, but designing a simple and maintainable architecture still requires deliberate decisions.

The New Model: Current State and Event History

One of the most important simplifications was separating the current state from the event history.

These two types of information answer different questions.

The current state tells me what exists in an environment right now.

The event history helps explain who performed an operation, when it happened, and what kind of operation it was.

For building a deployment package, the current state is what I primarily need.

If a table in DEV contains a new column that doesn't exist in TEST, I need to detect that difference even if the corresponding audit event is missing for some reason.

Event history is useful additional information. It may help explain when a change happened and whose activity it was associated with.

But determining the actual state of an environment cannot depend on whether every single change was successfully logged.

This led to another important decision: I don't keep an unlimited history of complete snapshots.

In the central TMS database, I keep the latest successfully collected state for each environment. Event history is stored separately, with a retention period of 45 days.

This also simplified the problem of historical tables growing continuously.

The complete current state is still collected regularly, but TMS no longer needs to preserve every previous snapshot to compare the environments as they exist now.

How I Capture the Database State

I consolidated the source-side data collection into a single PL/SQL package, using the same structure in all three technical schemas.

The package is called PKG_AUDIT_SOURCE.

It collects the current state, processes APEX Developer events, and manages the cleanup of old events.

For the database schema state, I primarily rely on Oracle data dictionary views.

The main sources include:

• DBA_OBJECTS – database objects and their status

• DBA_DEPENDENCIES – dependencies between objects

• DBA_TAB_PRIVS, DBA_COL_PRIVS – object privileges

• DBA_SYS_PRIVS, DBA_ROLE_PRIVS – system privileges and roles

• DBA_TAB_COMMENTS, DBA_COL_COMMENTS – table and column comments

I collect the state of APEX applications separately, using the APEX data dictionary views.

For database object DDL, I use DBMS_METADATA.GET_DDL.

I didn't want to write my own DDL generator. Oracle's generated DDL seemed like a reasonable starting point for eventually producing deployment scripts.

However, to compare different environments, some physical and environment-specific details need to be excluded.

For this, I use settings such as:

DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,
    'EMIT_SCHEMA',
    FALSE
);

DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,
    'SEGMENT_ATTRIBUTES',
    FALSE
);

DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,
    'STORAGE',
    FALSE
);

For example, the schema names are not necessarily identical in DEV, TEST, and PROD. I don't want every object to appear different simply because its owning schema has a different name.

However, these settings don't rewrite schema references inside a view definition. Those references may represent real differences that shouldn't be ignored.

The collected state is transferred to the central TMS through paginated JSON functions.

In TMS, I maintain five main data sets:

• C_AUDIT_SCHEMA_OBJECT

• C_AUDIT_SCHEMA_DEPENDENCY

• C_AUDIT_SCHEMA_GRANT

• C_AUDIT_SCHEMA_COMMENT

• C_AUDIT_APEX_COMPONENT

This gives me a consistent representation of database objects, their dependencies, privileges, comments, and APEX components across the three environments.

An important distinction is that this is not yet a deployment package. For an existing table, for example, the appropriate ALTER TABLE statements still need to be derived from the differences between the two states.

For now, my goal is to establish a reliable foundation for comparison.

Why I Don't Regenerate Every DDL Statement

One of the most resource-intensive parts of collecting snapshots was generating the DDL statements.

With thousands of objects, repeatedly calling DBMS_METADATA.GET_DDL can create significant load and increase execution time.

So I tried to avoid reprocessing objects that could be reliably identified as unchanged since the previous snapshot.

I only reuse the previously stored DDL and its hash when several conditions are satisfied.

Among other things, I check the object's name, owner, type, OBJECT_ID, LAST_DDL_TIME, and status. I also verify that the previously stored DDL is actually available and that the hash rule version matches the current version.

If any of these checks fail or the result is uncertain, I prefer to retrieve the DDL again.

For certain object types, such as sequences, I don't use this optimization at all, because their state may change without a DDL modification.

This doesn't make the entire snapshot process incremental. Dependencies, privileges, and comments are still collected every time, and TMS imports the current state again.

My main goal was to avoid repeating the most expensive DDL retrieval and hash calculation operations unnecessarily.

When the Hashes Reported More Differences Than Expected

Once current-state collection was working, I could move on to comparing environments.

I started by checking which objects had different DDL representations between DEV and TEST, and between TEST and PROD.

I generated hashes from the DDL statements and compared them.

The initial results were:

Of course, these numbers didn't mean that 307 or 217 objects actually needed to be deployed. They only indicated that the hashes were different.

When I started investigating the reasons behind these differences, I found several interesting issues.

One involved the order of constraint definitions in table DDL.

It turned out that DBMS_METADATA.GET_DDL could output structurally identical constraints in a different order. During my investigation, at least 38 DEV–TEST and 64 TEST–PROD table pairs showed differences caused solely by constraint ordering.

Sequences introduced another problem. The START WITH value in generated DDL may change even when that change alone doesn't justify redeploying the sequence.

For views, formatting, comments, and differences around the terminating semicolon could also produce misleading results.

At one point, I considered a very simple idea: what if I ignored smaller differences in DDL length?

For example, if the two DDL statements differed in length by fewer than ten characters, perhaps I could simply treat them as equivalent.

I quickly abandoned that idea.

Two PL/SQL programs of exactly the same length may behave completely differently, while a much longer comment may have no effect on the program's behavior at all.

DDL length is simply not a reliable way to determine whether an object needs to be deployed.

This was one of the most important realizations of the project so far: different hashes don't necessarily mean a real change that requires deployment.

If I want to build a reliable CI/CD solution, simply comparing two DDL strings isn't enough.

First, I need to define what actually counts as a change for each Oracle object type.

And that turned out to be a much more complicated question than I initially expected.

In the next part, I'll explain how I approached this problem for different Oracle object types, which normalization rules I introduced, and how they affected the comparison results.