Building CI/CD for Oracle APEX - Part 2: When Different DDL Hashes Don't Mean Different Database Objects

At the end of Part 1, I had reached the point where I could regularly collect the state of the DEV, TEST and PROD environments. However, the first comparisons reported more differences than I had expected.

The problem was not that the hashes were working incorrectly. They were showing exactly what they were supposed to show: the input texts were different.

But I did not simply need to find different texts. I wanted to understand which differences represented real changes that were relevant to deployment.

That became the next task in the project.

Where does the comparison happen?

The solution still follows the architecture introduced in Part 1. The technical schemas running in the three environments collect the current state of the databases and APEX applications. The central Tenancy Management System, or TMS, retrieves this information through APIs.

Figure 1 - How the DEV, TEST and PROD environments connect to the central TMS.

This part is not about data transfer or generating the eventual deployment script. We are one step earlier: I need to define the rules for comparing the object definitions I have collected.

Until I can handle that properly, I cannot trust an automatically generated deployment plan either.

I keep the original DDL

I considered building my own DDL generator. For a table, for example, I could read the columns and constraints from the data dictionary and always output them in the same order.

In the end, I decided against it. I did not want to create another DDL generator that would require ongoing maintenance.

I still retrieve object definitions using DBMS_METADATA.GET_DDL, with the transformation parameters configured earlier. I store the resulting DDL unchanged. Normalization only modifies the representation used to calculate the comparison hash.

The two serve different purposes.

The stored DDL shows the definition returned by Oracle. I also want to use it later when preparing the deployment plan.

The SHA-256 hash calculated from the normalized representation helps determine whether the objects differ according to my comparison rules. The package stores the DDL, the hash value and the identifier of the applied rule separately.

Figure 2 - Keeping the original DDL while separately generating a versioned comparison hash. A match always means a match under the chosen rules, not proof of complete semantic equivalence.

It is important to note that the original DDL does not automatically become a ready-to-run deployment script either. Modifying an existing table, for example, still requires identifying the necessary change statements, their execution order and the required checks.

Different object types need different rules

One of my most important decisions was not to process every object with a single, generic text-cleaning routine.

Tables, sequences and source code can contain different kinds of differences. The PKG_AUDIT_SOURCE package therefore selects a rule by object type: DDL_TABLE_V2, DDL_SEQUENCE_V2, DDL_VIEW_V2, or the more general DDL_LEX_V2.

Tables: I sort constraints, not columns

For tables, one of the first problems was the order of constraint definitions.

Here is a simplified example to illustrate the issue:

CREATE TABLE emp (
    id   NUMBER,
    name VARCHAR2(100),
    CONSTRAINT emp_pk PRIMARY KEY (id),
    CONSTRAINT emp_name_ck CHECK (name IS NOT NULL)
);

The same columns and constraints can appear with the constraint definitions in a different order:

CREATE TABLE emp (
    id   NUMBER,
    name VARCHAR2(100),
    CONSTRAINT emp_name_ck CHECK (name IS NOT NULL),
    CONSTRAINT emp_pk PRIMARY KEY (id)
);

The two texts are different, but in this example, the difference is not caused by a new column or a modified constraint.

Normalization therefore puts complete, top-level constraint definitions into a consistent order for hashing. It preserves the column order and the content of the constraints. Changing where a constraint appears in the generated text is not the same as changing its condition.

However, this can only be applied when the parser recognizes the structure of the DDL properly.

If a table's DDL contains multiple statements, for example, the code does not try to reorder some of them arbitrarily. It preserves their lexical order and uses a separate rule identifier:

DDL_TABLE_UNVERIFIED_V2

This does not mean that the table is faulty. It means that table-specific normalization could not be performed under the supported rule.

Sequences: counter state is not configuration

Sequences presented a different kind of problem.

The START WITH value in the generated DDL can move as a sequence is used. That does not necessarily mean the configuration I want to deploy to another environment has changed.

I therefore keep the number in the raw DDL but replace it with a fixed marker in the representation used for hashing. In the actual implementation, that marker is:

CURRENT_START_VALUE

The other tokens remain part of the comparison. I am not ignoring the entire sequence definition; I am treating this changing value separately.

Synchronizing the counter later is still a separate task. Because of caching, the value output by GET_DDL is not necessarily the last number actually issued, so it is not sufficient on its own to plan that operation.

For this reason, I retrieve sequence DDL again during every snapshot: its state can move without a change to LAST_DDL_TIME.

Views and PL/SQL: removing whitespace is not enough

For text-based definitions, it might seem straightforward to remove comments and whitespace characters.

The implementation is more cautious than that.

I break the DDL into tokens, treating identifiers, literals, numbers and operators separately. Each token's type, length and content become part of the hash input. Removing formatting therefore does not mean simply joining the remaining pieces of text together.

I omit ordinary comments but retain SQL hints. String literals and quoted identifiers are preserved as well. Operator adjacency must not be lost either: the parser distinguishes, for example, between := and : =.

I do not normalize letter case. I do not want a source change from S_candidate to s_candidate to become automatically invisible, even when it does not change the program's behavior in that particular context. This is a deliberately conservative decision.

I am not building a complete SQL or PL/SQL semantic analyzer. I want testable rules that reduce formatting-related differences without trying to automatically judge every possible source change.

Figure 3 - Different object types require different normalization rules. In the table example, only the order of the top-level constraints changes; the column order remains the same.

I do not remove every environment-specific difference

I already handle some environment-specific details when retrieving the DDL. The DBMS_METADATA transformation settings still include disabling EMIT_SCHEMA, SEGMENT_ATTRIBUTES and STORAGE.

However, this is not a general text-replacement operation.

The normalizer does not automatically replace schema references inside a view body, nor does it modify names within literals. I do not want to treat these as mere formatting noise. The code explicitly preserves schema qualifiers, quoted names and string values.

For me, the boundary is what I can exclude from the comparison using a clear, documented rule. I prefer to leave everything else visible.

The hash rule has a version, too

If I change the normalization rules, the same DDL may produce a different hash.

Storing the hash alone is therefore not enough. I also save the applied rule identifier in the object's DDL_HASH_RULE_ID field. The source-side API passes this identifier along with the DDL and the hash value.

Two hashes calculated under different rules must not be compared as though they had the same meaning.

This also affects caching. An unchanged object's previous DDL and hash can only be reused when the relevant conditions are met. Among other things, the code checks object identity, status, LAST_DDL_TIME and the rule version. When introducing a new rule, it is not enough to say that the database object itself has not changed.

Hash calculation takes place in the source-side package. The central TMS compares the values already calculated, so querying a difference view does not involve processing the entire collection of DDL texts again.

What did the comparisons show?

The comparison made when V2 was introduced reported fewer differing hashes than the previous version:

I presented the earlier figures in Part 1. The V2 state reported 199 and 120 differences respectively.

I do not consider these controlled before-and-after results measuring only the effect of normalization. I also excluded technical LOB and ISEQ$$_ derivative objects that should not be deployed independently from the difference view, while keeping them in the snapshot.

A lower number does not, by itself, prove that every remaining result is correct.

During an earlier check, for example, the interaction between a trailing -- comment and a semicolon produced false deployment alerts for four view pairs. I have not yet classified the exact causes of several other results. I therefore still do not describe the 199 and 120 differences as the same number of ready-to-execute deployment tasks.

Detecting a difference and deciding what to deploy are two separate steps.

I also check how the objects were processed

The next check answered a different question: for how many of the objects collected in DEV had the appropriate hash rule been applied?

The DEV state checked on October 26, 2026 produced the following results:

The 2,994 objects represent 90.98% of all objects examined. This is a processing classification, not a percentage measure of comparison accuracy.

There were 2,996 objects with a CAPTURED status in total, because that count also includes the two tables with a separate uncertainty flag. The status and the hash rule therefore need to be interpreted together.

Figure 4 - Processing classifications for the DEV state checked on October 26, 2026. Coverage does not imply automatic deployability.

The table results show the distinction particularly clearly: 712 of 714 tables received the DDL_TABLE_V2 rule identifier, while two were marked DDL_TABLE_UNVERIFIED_V2.

The DDL for both tables was available. However, the query did not reveal exactly which condition prevented table-specific normalization for these definitions. I do not want to replace that missing explanation with a guess.

For one trigger, by contrast, capture failed:

ORA-06502: PL/SQL: value or conversion error
LPX-00217: invalid character 0 (U+0000)

The result points to an invalid null character, but this error message alone does not establish where it came from.

The NOT_SUPPORTED category is different again. It describes the current scope of my own collector, not a general limitation of Oracle. In the code, for example, LOB objects and sequences named ISEQ$$_... are deliberately excluded from independent DDL processing.

An uncertain result is still a result

Initially, I wanted a simple yes-or-no answer: the objects either match or differ.

In practice, I first need to know whether the comparison has a suitable basis at all.

Is the DDL available? Is the object type supported? Did processing succeed? Were the hashes calculated using comparable rules?

The two unverified tables and the failed trigger capture show why these questions cannot be skipped.

CAPTURED indicates that processing succeeded in that sense. DDL_TABLE_UNVERIFIED_V2 adds that the usual table-specific normalization could not be applied. The two do not contradict each other.

My goal is certainly not to review every uncertain case manually after each snapshot. I want to develop a set of rules, tested by object type, that handles more and more situations correctly. Until I can interpret a case reliably, however, I would rather keep the uncertainty visible.

What about APEX components?

Although I have focused on database object DDL in this part, the same distinction matters for APEX.

I collect application pages, regions, items, buttons, processes and other components from the APEX dictionary views. The first comparison question is whether the same logical component exists in both environments. An internal numeric component ID is not sufficient on its own to establish that correspondence in every case.

Only after components have been matched correctly does it make sense to compare their relevant properties.

This does not mean that I necessarily want to deploy them component by component. A full application export can still be the basis for deployment. For now, component-level comparison is primarily intended to help me understand changes and identify which functionality needs testing.

Where do I go from here?

For me, the most important change is not that I see fewer differing hashes.

It is that I can define more precisely what the comparison measures, what I deliberately ignore and where uncertainty remains.

The original DDL is preserved. Normalization follows versioned, object-type-specific rules. Failed or insufficiently understood processing does not disappear behind a simple "same" or "different" answer.

This still does not complete the CI/CD solution. The differences need to become a reviewable deployment plan, with dependencies, execution order and the required change statements taken into account.

Before that, however, there is another requirement: I also need to trust how the states being compared were collected.

What happens if an object changes while the snapshot is being captured? What if a transfer stops halfway through? Or two scheduled processes try to work with the same source at the same time?

The next part will focus on snapshot reliability: handling concurrent runs, loading a complete state atomically and how I try to ensure that auditing does not put the production environment at risk.