Make APEXlang Easy: Makefiles and Merge-Safe Imports

TL;DR: I put the commands I use daily in APEX projects (export, import, validate, lint, test) into a makefile, so they run as make export-app or make test. On top of that, make sync-app exports the app first and uses a Git three-way merge to combine your local APEXlang changes with what others did in the App Builder before importing, so nobody’s work gets overwritten.

In an APEX project you can do so much through SQLcl and probably also benefit from using git and dbLinter. Remembering all the exact commands to type is hard and slows you down. It would also be great if colleagues could reuse what you figured out. One way to persist them and to abstract the complexity is just writing a .sh script. But I recently rediscovered Make for this purpose.

Make is a classic Unix build tool. It rebuilds files from their inputs and skips whatever is already up to date.

I use it much like the scripts in a package.json of a Node project. You have a single makefile in which you define targets. A target is a named shortcut that you run with make {target}, like make lint or make export-app. Below each target, you list the shell commands it runs.

But you need to install it on your system. That is fairly easy, as it is available in all Linux package managers, often as part of a build essentials package (the name varies by distro), and via xcode-select --install or brew on macOS.

If you are on Windows, I can recommend looking into WSL.

With APEX 26.1 I usually want to export an app to my git repo to either just check what changed or make changes locally.

The goal is to just be able to run make export-app. So we can create our first makefile to do this:

-include .env

DB_CONN    ?= project@dev.united-codes.com
APP_ID     ?= 2618
EXPORT_DIR ?= ./src/app
APP_DIR    ?= $(EXPORT_DIR)/my-app-alias

export-app:
	printf 'apex export -applicationid $(APP_ID) -exptype APEXLANG -dir $(EXPORT_DIR) -overwrite-files \n exit' | sql -name "$(DB_CONN)"

.PHONY: export-app

We define and default four variables that will be necessary:

• DB_CONN: name of the saved connection to your DB (connection name in SQL Dev VS Code/SQLcl)
• APP_ID
• EXPORT_DIR: where you store the app in your git repository
• APP_DIR: export dir with appended app alias (relevant for import)

Line 1 has -include .env. Your colleague can create a .env file like this:

DB_CONN=project-dev

The export-app target just pipes an apex export command into the SQLcl CLI.

Make assumes a target is a file it has to build. If a file or folder with the same name exists (like a test folder), Make thinks the job is already done and does nothing. Listing your targets in .PHONY tells Make they are not files and should always run. (More context)

Adding a target for imports is now fairly straightforward:

# ...

import-app:
	printf 'apex import -input $(APP_DIR)  \n exit' | sql -name "$(DB_CONN)"

.PHONY: export-app import-app # add new targets here

LINT_PATH  ?= src
REF_BRANCH ?= development

validate-app:
	printf 'apex validate -input $(APP_DIR) \n exit' | sql -nolog

# Run dbLinter over the PL/SQL sources.
lint:
	dblinter check $(LINT_PATH)

# Run dbLinter only on lines changed against the baseline branch.
lint-changed:
	dblinter check --newCodeOnly=true --referenceBranch=$(REF_BRANCH) $(LINT_PATH)

show-conn:
	@echo "DB_CONN=$(DB_CONN) APP_ID=$(APP_ID) APP_DIR=$(APP_DIR)"

Remember to add each to .PHONY. And don’t shy away from creating more variables if you need them.

With this I can easily run utPLSQL test suites:

SUITE    ?= uc_ai

test:
	sql -name $(DB_CONN) @scripts/run-tests.sql $(SUITE)

run-tests.sql looks like this:

set serveroutput on size unlimited
set feedback off
-- Otherwise SQLcl echoes the block twice as "old:" / "new:" for the &1 variable.
set verify off

-- Keep number formatting locale-independent
alter session set nls_numeric_characters = '.,';

whenever sqlerror exit failure
whenever oserror exit failure

begin
  ut.run('&1');
end;
/

exit

The SUITE variable gets passed as a parameter into the script through &1. By default, it runs the uc_ai suitepath. But we can also manually trigger another:

make test SUITE=uc_ai.agents

Separate from the app export, the same makefile also rebuilds release scripts. This time, each target is named after the file it creates. After the colon, I list the files it is built from.

Make is smart: it only rebuilds a file if it is missing or older than one of those files.

SOURCES := $(shell find src -type f \( -name '*.pks' -o -name '*.pkb' -o -name '*.sql' \))
GENERATOR_DEPS := scripts/package_utils.sh

# These are real files with real inputs, so make only regenerates what is stale.
install_uc_ai_complete.sql: $(SOURCES) $(GENERATOR_DEPS) scripts/generate_install_script_complete.sh
	bash scripts/generate_install_script_complete.sh

upgrade_packages.sql: $(SOURCES) $(GENERATOR_DEPS) scripts/generate_upgrade_script.sh
	bash scripts/generate_upgrade_script.sh

uninstall.sql: $(SOURCES) $(GENERATOR_DEPS) scripts/generate_uninstall_script.sh
	bash scripts/generate_uninstall_script.sh

I can bundle all three into a single generate target (only generate goes into .PHONY; the file targets must not, or they would be regenerated every time):

generate: install_uc_ai_complete.sql upgrade_packages.sql uninstall.sql

And for a test installation, I need to run generate first:

test-install: generate
	bash local-26ai.sh test-script-install ./install_uc_ai_complete.sql

(local-26ai.sh and test-script-install come from uc-local-apex-dev)

Now that we can edit files locally, we run into a new issue: when another developer makes changes in APEX after your last export, you import over their changes, which are then gone.

Wouldn’t it be great if, before an import, you exported first and could either auto-merge others’ changes into your own files or resolve conflicts? This sounds more like a Git problem than an APEX problem.

# The branch you work on and import from.
SYNC_BRANCH ?= main

# Merge-safe import: export the DB into the apex-db branch, merge it into SYNC_BRANCH,
# validate, then import. Re-run after resolving conflicts. See scripts/apex-sync.sh.
#   make sync-app            asks before importing
#   make sync-app YES=1      no prompt
#   make sync-app MSG="..."  commit message for pending app changes (else prompted)
sync-app:
	MSG="$(MSG)" DB_CONN="$(DB_CONN)" APP_ID="$(APP_ID)" EXPORT_DIR="$(EXPORT_DIR)" APP_DIR="$(APP_DIR)" \
		SYNC_BRANCH="$(SYNC_BRANCH)" ./scripts/apex-sync.sh sync $(if $(YES),-y)

# One-time: create the apex-db branch at the commit whose app dir matched the DB.
sync-init:
	DB_CONN="$(DB_CONN)" APP_ID="$(APP_ID)" APP_DIR="$(APP_DIR)" ./scripts/apex-sync.sh init $(BASE)

So we have two new targets. We run sync-init once and only sync-app from then on. But the real logic lives in apex-sync.sh.

Before showing you the script itself, here is a summary of what it does:

The trick is a second branch called apex-db. It only ever contains what the database looked like at the last sync. That gives Git the missing piece for a real three-way merge: a common base. Git can then tell what you changed locally and what others changed in the App Builder since then.

When you run make sync-app, the script:

1. Commits your pending app changes. It shows you the changed files and asks for a commit message. Changes to other files in your repo are left alone.

2. Exports the app from the database. The export goes into a separate Git worktree on the apex-db branch, so your own files are never touched. If something changed in the DB since the last sync, it is committed there as a “DB snapshot”.

3. Merges apex-db into your SYNC_BRANCH. This is where the three scenarios resolve themselves:

   • Nobody else changed anything: nothing to merge.
   • Others changed different parts: Git merges them automatically.
   • Others changed the same part as you: you get regular merge conflicts. Resolve them in your editor, commit and run make sync-app again.

   Which branch that is comes from SYNC_BRANCH. It defaults to main; if your team works on development, set SYNC_BRANCH=development in your makefile or .env. The script refuses to run on any other branch, so you don’t accidentally import a half-finished feature branch.

4. Validates the merged result with apex validate. A merge can be clean for Git and still produce something APEX does not accept.

5. Checks nobody saved in the Builder in the meantime. It compares the app’s last_updated_on timestamp from before the export with the current one. If it moved, the script aborts and you simply sync again.

6. Imports the app after you confirm.

7. Moves apex-db forward to your imported state, so the next sync uses it as the new base.

Because every step checks where it stands, you can run make sync-app as often as you like. If there is nothing to do, it tells you and stops.

#!/usr/bin/env bash
# Merge-safe APEX import: export the DB, three-way merge with your branch, then
# import.
#
# The DB branch (default `apex-db`) mirrors what the database looked like at the
# last sync. It only ever receives raw exports (and, after an import, the app
# dir of your branch), so merging it gives a real three-way merge between "our"
# edits and "their" edits made in the App Builder since the last sync.
#
#   apex-sync.sh init <commit>   create the DB branch at the commit that matched the DB
#   apex-sync.sh sync [-y]       export -> merge -> validate -> import
#
# Uncommitted changes in the app dir are committed first, with $MSG or a message
# asked for at the prompt; changes to other files are left uncommitted.
#
# `sync` is safe to re-run: after resolving a merge conflict, commit and run it
# again. It re-exports, finds nothing new, and continues with the import.
#
# Config comes from the environment (the makefile passes it through):
#   DB_CONN, APP_ID, EXPORT_DIR, APP_DIR   required / app location
#   SYNC_BRANCH   branch you work on and import from (default: main)
#   DB_BRANCH     branch mirroring the DB (default: apex-db); use one per app
#                 or per DB if a repo holds several

set -euo pipefail

DB_CONN=${DB_CONN:?DB_CONN not set}
APP_ID=${APP_ID:?APP_ID not set}
EXPORT_DIR=${EXPORT_DIR:-./src/app}
APP_DIR=${APP_DIR:?APP_DIR not set}
EXPORT_DIR=${EXPORT_DIR#./}
APP_DIR=${APP_DIR#./}

BRANCH=${DB_BRANCH:-apex-db}
MAIN=${SYNC_BRANCH:-main}
ROOT=$(git rev-parse --show-toplevel)
WT="$(cd "$(git rev-parse --git-common-dir)" && pwd)/${BRANCH//\//-}-worktree"

cd "$ROOT"

die() { printf 'apex-sync: %b\n' "$*" >&2; exit 1; }
log() { echo "==> $*"; }

# Last-updated timestamp of the app; the Builder bumps it on every save.
app_stamp() {
  printf "set heading off feedback off pagesize 0\nselect to_char(last_updated_on, 'YYYY-MM-DD_HH24:MI:SS') from apex_applications where application_id = %s;\nexit\n" "$APP_ID" \
    | sql -S -name "$DB_CONN" | tr -d '[:space:]'
}

# Export the app into the DB branch's worktree, replacing the old export so that
# components deleted in the Builder disappear from the snapshot too.
export_to_worktree() {
  rm -rf "${WT:?}/$APP_DIR"
  local out
  out=$(printf 'apex export -applicationid %s -exptype APEXLANG -dir %s -overwrite-files -exitwhendone\nexit\n' \
    "$APP_ID" "$WT/$EXPORT_DIR" | sql -name "$DB_CONN" 2>&1) || die "export failed:\n$out"
  [ -d "$WT/$APP_DIR" ] || die "export did not produce $APP_DIR:\n$out"
}

commit_worktree() {
  git -C "$WT" add -A -- "$APP_DIR"
  if git -C "$WT" diff --cached --quiet; then
    return 1
  fi
  git -C "$WT" commit -q -m "$1"
}

ensure_worktree() {
  git rev-parse -q --verify "refs/heads/$BRANCH" >/dev/null \
    || die "branch $BRANCH missing; run: make sync-init BASE=<commit whose app dir matched the DB>"
  if [ ! -d "$WT" ]; then
    git worktree prune
    git worktree add -q "$WT" "$BRANCH"
  fi
  [ "$(git -C "$WT" rev-parse --abbrev-ref HEAD)" = "$BRANCH" ] || die "$WT is not on $BRANCH"
  [ -z "$(git -C "$WT" status --porcelain)" ] || die "$WT has uncommitted changes; inspect or reset it"
}

cmd_init() {
  local base=${1:?usage: apex-sync.sh init <commit>}
  git rev-parse -q --verify "refs/heads/$BRANCH" >/dev/null && die "branch $BRANCH already exists"
  git branch "$BRANCH" "$base"
  log "created $BRANCH at $(git rev-parse --short "$base")"
}

cmd_sync() {
  local yes=0
  [ "${1:-}" = "-y" ] && yes=1

  # --- preconditions -------------------------------------------------------
  [ "$(git rev-parse --abbrev-ref HEAD)" = "$MAIN" ] \
    || die "check out $MAIN first (or set SYNC_BRANCH to the branch you import from)"
  [ "$MAIN" != "$BRANCH" ] || die "SYNC_BRANCH and DB_BRANCH must differ"
  [ ! -f "$(git rev-parse --git-dir)/MERGE_HEAD" ] || die "a merge is in progress; resolve and commit it, then re-run"
  # git merge aborts when anything is staged, even in unrelated files.
  git diff --cached --quiet -- . ":(exclude)$APP_DIR" \
    || die "files outside $APP_DIR are staged; commit or unstage them (git restore --staged <file>)"
  ensure_worktree

  # Uncommitted app changes are our side of the merge, so commit them (only
  # them: other files, staged or not, are left alone).
  if [ -n "$(git status --porcelain -- "$APP_DIR")" ]; then
    local msg=${MSG:-}
    if [ -z "$msg" ] && [ -t 0 ]; then
      log "uncommitted changes in $APP_DIR:"
      git status --short -- "$APP_DIR"
      read -r -p "Commit message: " msg
    fi
    git add -A -- "$APP_DIR"
    git commit -q -m "${msg:-App changes before DB sync}" -- "$APP_DIR"
    log "committed local changes in $APP_DIR"
  fi

  # --- 1. snapshot the DB --------------------------------------------------
  local stamp
  stamp=$(app_stamp)
  [ -n "$stamp" ] || die "could not read last_updated_on for app $APP_ID"
  log "exporting app $APP_ID from $DB_CONN (last updated $stamp)"
  export_to_worktree
  if commit_worktree "DB snapshot of app $APP_ID from $DB_CONN ($stamp)"; then
    log "DB has changes since the last sync:"
    git --no-pager diff --stat "$BRANCH~1" "$BRANCH" -- "$APP_DIR"
  else
    log "DB unchanged since the last sync"
  fi

  # --- 2. merge their changes into ours ------------------------------------
  if ! git merge -q --no-edit -m "Merge DB changes of app $APP_ID ($stamp)" "$BRANCH"; then
    echo
    git --no-pager diff --name-only --diff-filter=U
    die "merge conflicts above. Resolve them, commit, then run make sync-app again"
  fi

  if git diff --quiet "$BRANCH" HEAD -- "$APP_DIR"; then
    log "nothing to import; $MAIN and the DB are identical"
    return 0
  fi

  # --- 3. validate the merged result ---------------------------------------
  log "validating $APP_DIR"
  local out
  out=$(printf 'apex validate -input %s\nexit\n' "$APP_DIR" | sql -nolog 2>&1)
  if grep -q 'Compile Errors' <<<"$out"; then
    echo "$out" >&2
    die "validation failed; fix, commit, then run make sync-app again"
  fi

  # --- 4. import, unless someone saved in the Builder meanwhile ------------
  log "changes to import into $DB_CONN:"
  git --no-pager diff --stat "$BRANCH" HEAD -- "$APP_DIR"
  if [ $yes -eq 0 ]; then
    read -r -p "Import into $DB_CONN? [y/N] " answer
    [ "$answer" = "y" ] || [ "$answer" = "Y" ] || die "aborted; nothing imported"
  fi

  [ "$(app_stamp)" = "$stamp" ] || die "app $APP_ID was changed in the Builder since the export; run make sync-app again"

  log "importing"
  out=$(printf 'apex import -input %s\nexit\n' "$APP_DIR" | sql -name "$DB_CONN" 2>&1) || true
  if grep -qE 'ORA-[0-9]+|Compile Errors|^Error' <<<"$out"; then
    echo "$out" >&2
    die "import reported errors; the DB may be partially updated, check it before re-running"
  fi

  # --- 5. the DB now equals $MAIN: record that as the new base -------------
  rm -rf "${WT:?}/$APP_DIR"
  git -C "$WT" checkout -q "$MAIN" -- "$APP_DIR"
  commit_worktree "DB snapshot of app $APP_ID after importing $MAIN $(git rev-parse --short HEAD)" || true
  git merge -q --no-edit -m "Record import of app $APP_ID into $DB_CONN" "$BRANCH"
  log "done; $BRANCH now matches the DB"
}

case "${1:-}" in
  init) shift; cmd_init "$@" ;;
  sync) shift; cmd_sync "$@" ;;
  *) die "usage: apex-sync.sh {init <commit>|sync [-y]}" ;;
esac

Now your makefile is so big that you can easily forget which targets are available. That is easy to fix.

# just running make defaults to "make help"
.DEFAULT_GOAL := help

# List the available commands.
# Each target is described by the first line of the comment block above it.
help:
	@awk '/^#/ { if (!c) d = substr($$0, 3); c = 1; next } \
	  /^[a-z][a-z-]*:/ && c { sub(/:.*/, ""); printf "  \033[1;36m%-14s\033[0m %s\n", $$0, d } \
	  { c = 0 }' $(firstword $(MAKEFILE_LIST))

Now you need to add a comment above each target.

Example output:

> make

  help           List the available commands.
  export-app     Export the app from $(DB_CONN) into $(APP_DIR).
  sync-app       Merge-safe import: export the DB into the apex-db branch, merge it into SYNC_BRANCH,
  sync-init      One-time: create the apex-db branch at the commit whose app dir matched the DB.
  import-app     Import $(APP_DIR) into $(DB_CONN) as is, overwriting any Builder changes.
  validate-app   Check the .apx sources without a database connection.
  lint           Run dbLinter over the PL/SQL sources.
  lint-changed   Run dbLinter only on lines changed against the baseline branch.
  show-conn      Show which connection the targets above would use.

Here is the complete makefile from one of my projects, with the project name swapped for a generic one. It has no lint or test targets, but everything else from this post is in it.

# Local export/import of an APEX app (APEXlang format).
#
# The connection name differs per developer, so it lives in a local, untracked
# .env file. Copy .env.example to .env and adjust:
#
#   cp .env.example .env
#
# Precedence: command line > .env > defaults below.
#   make export-app DB_CONN=my-conn

-include .env

DB_CONN    ?= my_app@dev.example.com
APP_ID     ?= 100
# The APEXLANG export appends a folder named after the app alias to -dir,
# so -dir is the parent and the app lands in $(EXPORT_DIR)/my-app.
EXPORT_DIR ?= ./src/app
APP_DIR    ?= $(EXPORT_DIR)/my-app
# sync-app: the branch you import from, and the branch mirroring the DB.
SYNC_BRANCH ?= main
DB_BRANCH   ?= apex-db

SYNC_ENV = DB_CONN="$(DB_CONN)" APP_ID="$(APP_ID)" EXPORT_DIR="$(EXPORT_DIR)" APP_DIR="$(APP_DIR)" \
	SYNC_BRANCH="$(SYNC_BRANCH)" DB_BRANCH="$(DB_BRANCH)"

.DEFAULT_GOAL := help

# List the available commands.
# Each target is described by the first line of the comment block above it.
help:
	@awk '/^#/ { if (!c) d = substr($$0, 3); c = 1; next } \
	  /^[a-z][a-z-]*:/ && c { sub(/:.*/, ""); printf "  \033[1;36m%-14s\033[0m %s\n", $$0, d } \
	  { c = 0 }' $(firstword $(MAKEFILE_LIST))

# Export the app from $(DB_CONN) into $(APP_DIR).
export-app:
	printf 'apex export -applicationid $(APP_ID) -exptype APEXLANG -dir $(EXPORT_DIR) -overwrite-files -exitwhendone  \n exit' | sql -name "$(DB_CONN)"

# Merge-safe import: export the DB into DB_BRANCH, merge it into SYNC_BRANCH,
# validate, then import. Re-run after resolving conflicts. See scripts/apex-sync.sh.
#   make sync-app            asks before importing
#   make sync-app YES=1      no prompt
#   make sync-app MSG="..."  commit message for pending app changes (else prompted)
sync-app:
	MSG="$(MSG)" $(SYNC_ENV) ./scripts/apex-sync.sh sync $(if $(YES),-y)

# One-time: create DB_BRANCH at the commit whose app dir matched the DB.
#   make sync-init BASE=26ce6c5
sync-init:
	$(SYNC_ENV) ./scripts/apex-sync.sh init $(BASE)

# Import $(APP_DIR) into $(DB_CONN) as is, overwriting any Builder changes.
# Prefer sync-app.
import-app:
	printf 'apex import -input $(APP_DIR)  \n exit' | sql -name "$(DB_CONN)"

# Check the .apx sources without a database connection.
validate-app:
	printf 'apex validate -input $(APP_DIR) \n exit' | sql -nolog

# Show which connection the targets above would use.
show-conn:
	@echo "DB_CONN=$(DB_CONN) APP_ID=$(APP_ID) APP_DIR=$(APP_DIR)"

.PHONY: export-app import-app sync-app sync-init validate-app show-conn help

Originally, I just wanted to write about using a makefile to store a few commands. But the more I used it, the more I automated. That’s why I ended up with the rather complex sync script. I have not battle-tested it for months, so use it with a bit of caution.

I also noticed that makefiles are great context for AI. Instead of describing things in large Markdown files, it can just be a target in a makefile. Also, LLMs are great at creating makefiles.

Please let me know what you think about this approach, or how the makefile and the sync script can be improved.