Setting an AI agent loose inside a live Oracle APEX app

Not a greenfield demo — a personal finance dashboard on Oracle APEX 24 and Autonomous Database, actively built and maintained by an AI coding agent, with git as the source of truth and a hard stop before anything gets written.

Oracle APEX 24 · Autonomous DBSQLcl MCPClaude Code

Most write-ups about AI coding agents are about greenfield projects — an empty repo, a chat window, a working app by lunch. That’s a different problem from the one I had: a production Oracle APEX application, already carrying real financial data, that I wanted an agent to keep building into safely, the same way I’d trust a new engineer to. This is the actual configuration that made that work — not the sales pitch, the plumbing.

01 .The problem with “just let it click around”

APEX Builder is a web application with its own internal session state. There’s no live API for page-level structure — the regions, dynamic actions, and Interactive Grid columns that make up a page live inside APEX’s own metadata tables, edited through a browser UI designed for a human at a mouse. An agent driving that UI directly is possible, but the result is invisible: no diff, no history, no way to know what changed until something breaks in front of a user.

Every other part of this codebase — Python loaders, SQL views, PL/SQL procedures — already lived in git with normal code review discipline. APEX pages were the one part that didn’t. That gap is what the whole setup below exists to close.

02.Setting it up, step by step

None of this needs anything exotic — a folder, git, a wallet download, and two config files. In order:

1. The project folder

Plain directories, organized by what actually changes independently — DDL separate from PL/SQL, the APEX export in its own tree, Python loaders on the side.

mkdir perfinapp && cd perfinapp
mkdir -p sql/ddl sql/plsql sql/views apex/f107 python docs

2. Git, with the wallet kept out of it

Standard git init, with one non-negotiable line in .gitignore before anything else happens — the ADB wallet and any credential file must never be a commit away from public.

git init

.gitignore

wallet/
*.pem
*.zip
.dbtools/

3. CLAUDE.md — the file the agent reads first

This is the one piece that isn’t standard tooling: a plain markdown file at the project root that Claude Code loads automatically at the start of every session, before it touches anything. It’s the durable memory a chat window doesn’t have — write it once, and every future session already knows the rules.CLAUDE.md

# Perfinapp — Project Instructions

## What this project is
Personal finance dashboard on Oracle APEX 24 + Autonomous DB.
Schemas: WKSP_STOCKTRADE, WKSP_STOCKDATA. APEX Application: 107.

## Database access
Connection is via the `sqlcl` MCP server.
Saved connection name: `perfinapp_adb` (password in SQLcl's own
credential store, never passed in chat).

## HARD RULE — confirm before execute
For any statement that is not a plain SELECT — any DDL, any DML,
any PL/SQL block that writes data:
1. Show the exact SQL in full.
2. State which table(s)/schema(s) it touches and whether reversible.
3. Wait for explicit approval before running it.

## Oracle coding rules
1. Positional binds only — never named binds.
2. No RETURNING ... INTO — fails in thin mode.
3. [add project-specific gotchas here as you hit them]

## APEX changes
APEX has no live API for page structure. Workflow:
1. Export the app to apex/f107/ via SQLcl.
2. Edit the exported SQL directly, never in the Builder UI.
3. Reimport, verify, re-export, diff, commit.

The point isn’t this exact wording — it’s that the rules live in a file under version control, get refined the same way code does, and never have to be re-typed into a conversation.

4. Connecting to the Autonomous Database

From the OCI console, open the ADB instance’s DB Connection panel and download the wallet zip. Unzip it somewhere stable and point TNS_ADMIN at that folder so SQLcl can find it.

unzip Wallet_perfinapp.zip -d ~/wallets/perfinapp
# macOS/Linux
export TNS_ADMIN=~/wallets/perfinapp
# Windows PowerShell
$env:TNS_ADMIN = "$HOME\wallets\perfinapp"

Then, from a SQLcl prompt, save the connection — this is the step that means a password never has to be typed into a chat session again:

sql /nolog
SQL> conn -save perfinapp_adb -savepwd ADMIN/<password>@perfinapp_high

-save names the connection; -savepwd encrypts and stores the password in SQLcl’s own credential store. From then on, both a human at a terminal and an agent through MCP can reconnect with just connect perfinapp_adb.

5. Registering the MCP server

Oracle ships an official Model Context Protocol server with SQLcl 25.2+ — it’s the same SQLcl binary, started in MCP mode instead of interactive mode. However your client registers a server (Claude Code, Claude Desktop, or another MCP-aware tool), point it at that binary:

claude mcp add sqlcl -- sql -mcp

Once registered, the agent gets connect/run-sql/schema-information tools scoped to whatever connections exist in that SQLcl install — in this case, exactly one: perfinapp_adb. Every statement it runs is logged server-side, on Oracle’s own audit trail, not a client-side log that’s easy to lose track of.

6. The app itself, exported to plain SQL

APEX has always been able to export an application as SQL — that part isn’t new. What matters is treating that export as the live source of truth rather than an occasional backup: one file per page, one per shared component, one per table and view and procedure, all committed.

apex export -applicationid 107 -split -dir apex/f107 -overwrite-files

A one-line change to a Dynamic Action’s JavaScript, a new column on a grid, an entirely new page — all of it becomes a normal git diff, reviewed the same way as any other pull request.editexported .sqlreimportremove_page +verifyapex_appl_*re-exportwhole appdiffcatch driftcommitonly if clean

every APEX change, without exception

03.The loop in practice

Edit the exported SQL for the affected region, column, or process directly — never in the Builder UI, so nothing happens that isn’t in a file.

Reimport through APEX’s own wwv_flow_imp_page API. Structural changes need the page removed and rebuilt from the file first — APEX’s import doesn’t cleanly upsert an existing page’s internal structure.

Verify against APEX’s own metadata — apex_application_page_regions, apex_appl_page_ig_columns, the button and process tables. Not “it looks right” — actually querying what the import created.

Re-export the whole application. This is the step that catches anything the edit touched that wasn’t the intended change.

Diff the fresh export against git. A clean diff — only the intended file, only the intended lines — is the actual pass condition.

Commit, with a message that explains why, not just what. Git history for an APEX page now reads like git history for anything else.

04.Why the confirmation rule earns its place

CLAUDE.md carries a lot of small rules, but one of them does most of the actual safety work.

The one that matters most

For any statement that isn’t a plain SELECT — any INSERT, UPDATE, DDL, any PL/SQL block that writes data — show the exact SQL in full, name which tables it touches and whether it’s reversible, and wait for explicit approval. Every write in this app’s history has gone through that gate. No exceptions, no batching two writes under one approval.

Underneath that sit the smaller, harder-won rules — the kind you only get by breaking them once:

Positional binds only — :1, :2, never :named — this environment throws ORA-01484 on named binds.
No RETURNING ... INTO — fails silently in thin-mode connections. Insert, then look the row back up.
Column-naming quirks specific to this schema, so nobody rediscovers them mid-incident.
Never mix a window function with GROUP BY in one query — ORA-30483, push the window function to an outer query.

05.How it checks itself, and what happens when I report a bug

None of the steps above are worth much without a habit underneath them: never take “should be fixed” on faith. Every deploy gets closed out by querying the database again — the same tables the app itself reads — not by re-reading the code and assuming it’s now correct.

→

After a page redeploy: query APEX’s own metadata — apex_application_page_regions, apex_appl_page_ig_columns — to confirm the live structure actually matches the file, then apex_workspace_activity_log.error_message to confirm the next real page load comes back clean.

→

After a calculation fix: run it against a dedicated test user with hand-computed expected values, compare, then delete the test rows — and query again afterward to confirm zero rows are left behind. Nothing gets called done from a description of what the code should do.

→

When I report something broken — usually just a screenshot — the first move is never a guess at the fix. It’s a query: the activity log for a page error, or the actual table behind the screen, to see what the database really holds right now.

Two examples from this app, both closed the same way:

Reported

A page returning a bare Bad Request 400 — no stack trace, no detail, the exact shape of error that invites guessing.

Checked

A direct query against apex_workspace_activity_log.error_message — not the page itself, which had nothing more to say.

Found

The real error in one shot: an invalid attribute value on a Dynamic Action, rejected at page-compile time.

Confirmed

Same query, run again after the fix — timestamped page loads with an empty error_message, not just “should work now.”

Reported

A loan’s outstanding balance didn’t move after a lumpsum payment was entered — even though the payoff date had shifted correctly, which made it look like the whole feature was broken.

Checked

The actual payment table, and the loan row’s own numbers, before touching any code — confirming the payment really was recorded and the schedule really had recalculated downstream.

Found

A narrower bug than the symptom suggested: the balance snapshot only refreshed at month-end, so a payment made mid-month had already changed the real schedule — just not the one number being displayed as “as of today.”

Confirmed

Re-ran the exact scenario against the same loan and checked the balance against a hand calculation before calling it fixed.

That second one is the more useful example, honestly — the fix wasn’t obvious from the bug report alone. It only became obvious once the actual data confirmed which part of the calculation was wrong and which parts already weren’t.

06.What actually shipped

Loan amortization engine

Month-by-month schedule with dated rate-change history and lumpsum prepayments, both EMI-fixed and tenure-fixed modes

Liabilities module

New schema, calc engine, and UI end to end — loans, credit cards, other debts

Calculation fixes

Root-caused a mid-year compounding asymmetry that had been silently overstating a tax figure

First regression suite

Isolated fixtures, hand-verified expected values, CI-shaped exit codes

07.The honest bottleneck

This isn’t autonomous, on purpose. Every write is shown in full before it runs — that confirmation is the actual safety net, not a formality being observed on the way to removing it later. And APEX’s Builder UI still isn’t something the agent drives directly; the export-edit-reimport loop exists specifically so it never has to.

If there’s one thing worth taking from this setup, it’s that the hard part was never the AI. It was making a stateful, UI-driven system into something as reviewable as a normal codebase — after that, the agent could be held to the same bar as anyone else committing to the repo.

Perfinapp — a personal finance dashboard on Oracle APEX 24 and Autonomous Database.