Skip to main content

Building CI/CD for Oracle APEX – Part 5: From Selected Changes to Versioned Release Artifacts

How I turn selected database differences into versioned SQL artifacts, with explicit targets, human review and a separate deployment decision.

Updated Markdown
Part of seriesBuilding Database-Driven CI/CD for Oracle APEX
Building CI/CD for Oracle APEX – Part 5: From Selected Changes to Versioned Release Artifacts

By this point in the series, TMS can collect the current state of DEV, TEST and PROD, compare database objects and APEX components, and provide event history where it is available.

That still does not create a deployment script.

A difference list tells me that two states are not the same. A release needs to say much more: which direction it is intended to move in, which snapshots it is based on, which differences have been selected, which ones require review, and exactly which generated file was approved.

For that reason, a release in TMS is not just the name of a SQL file. It is a separate, traceable unit of work.

A release is a selected set of differences

The release list shows the direction, status, creator and item count for each candidate release. A release can be open, closed or canceled. None of those states is the same as executing it in the target environment, and none should be read as proof that deployment or validation has already happened.

Figure 1. A release is an auditable unit of work. An open release has not been approved, and a closed release is not proof that SQL was executed in the target database.

This creates a useful boundary between separate pieces of work. A DEV-to-TEST candidate for one system does not get mixed with another system or a later TEST-to-PROD candidate. The release refers to differences found in snapshots; it does not change those snapshots.

A release needs an unambiguous context

The release record identifies the system, source and target environments, source and target databases, and source and target schemas. These are not convenience fields. A deployment artifact is meaningful only if its intended direction is explicit.

Figure 2. The deployment context is part of the release record. A generated file should never become generic SQL that can be run against an unknown target.

The lifecycle fields also distinguish who created, updated, approved or closed the release. The person who generates an installer should not be treated as having approved it merely by doing so.

Selecting a difference does not make it safe to deploy

Release items are created from selected DDL differences. Each item preserves the source and target snapshots, object identity and the action selected for review.

Figure 3. The system treats different kinds of changes differently. Creating a missing object and altering an existing table do not carry the same risk.

Code objects such as packages, package bodies, procedures, functions, views and triggers can often be candidates for a conservative generated installer. Structural or security changes require closer review. A DROP, RENAME, destructive change or unresolved dependency must not quietly turn into executable SQL.

That classification does not prevent automation. It gives automation a boundary. The system can prepare the cases that have a clear rule and explicitly stop at the cases that still need a person to make a decision.

The installer is a versioned artifact

One release can have several installer versions. Review may remove an item, change the execution order, add a required check or expose a problem in a generation rule. When that happens, I do not overwrite the existing file and pretend that it is the same artifact.

Figure 4. Installer versions for release AW-2, with their status, file name, generation time, creator and verification result. Version 3 is a draft; the earlier versions are superseded.

Versioning makes the review trail much clearer. It preserves which installer was current at a particular time and which earlier drafts it replaced.

In the current implementation, installer generation may be assisted by AI-based tooling, but that does not change the responsibility model. A generated artifact is still only a draft until it has been reviewed by a person who understands the target environment, the selected differences and the possible runtime impact.

Approval and execution are separate decisions

The installer detail page displays more than generated SQL. It identifies the release, source and target context, artifact version, file name, generation time, input identity and verification result.

Figure 5. Release and installer details alongside the SQL preview for AW-2, version 3. REVIEW REQUIRED comments flag changes and missing prerequisites that still need human review.

This is a deliberate boundary in the current solution. TMS does not automatically execute database or APEX deployments. It makes the state, evidence and generated artifact reviewable. Running the artifact in a target environment remains a separate, logged decision with an accountable owner.

What the process changed

The series began with a simple problem: exports and manually maintained SQL scripts do not reliably describe the true differences between development and production environments.

The resulting process is not one large automation. It is a chain of smaller, verifiable steps:

  1. Regular, validated snapshots of the actual DEV, TEST and PROD state.
  2. Object-type-specific comparison rules that make DDL differences more meaningful.
  3. Event history and context that help investigate how a difference may have occurred.
  4. APEX component comparison that guides review and testing without pretending to replace a full application export.
  5. A versioned release and installer artifact that remains subject to human review and approval.

For me, that is the more useful form of CI/CD for this kind of Oracle environment. Automation prepares the evidence and the artifact; it does not hide the decision or the risk of deployment. The real value is not pretending that deployment risk disappears, but making every important step more explicit, reviewable and traceable.

About the author

  • Oracle ACE Pro, founder of Enrol Consulting, and Oracle Database and APEX developer with more than two decades of experience building data-driven business applications. I write about Oracle Database, APEX, cloud and AI-assisted development.

Separating approval from execution is the part I found most valuable. Too many pipelines treat a generated script as ready to run once it exists, and keeping a human decision between the artifact and the deployment is a sensible guard. I also liked the point that selecting a difference does not make it safe to deploy. Versioning the installer so reviewers can see exactly which version was current at each step gives the audit trail real meaning instead of just a file name.

Building Database-Driven CI/CD for Oracle APEX

Part 5 of 5

A personal journey toward database-driven CI/CD for Oracle APEX. Exploring how to track real database changes, compare DEV, TEST and PROD environments, and build reliable deployment automation with PL/SQL, ChatGPT and Codex.

Start from the beginningBuilding CI/CD for Oracle APEX – Part 1: Why Exports Are Not EnoughA 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 Oracl