Skip to content

14.-SQLcl-Project-with-APEXlang-First-Impressions.md

Latest commit

 

History

History
420 lines (290 loc) · 24.2 KB

14.-SQLcl-Project-with-APEXlang-First-Impressions.md

File metadata and controls

420 lines (290 loc) · 24.2 KB

14. SQLcl Project with APEXlang: First Impressions

Executive Summary

  • Setup was reasonably easy, but one component disappeared quickly.

    • I used the Oracle AI Database 26ai Free VirtualBox Appliance for this test.
    • Oracle retired that appliance download page by June 4, 2026.
    • Readers can still use this document as a workflow report, but they may need a different Oracle 26ai Free environment.

  • SQLcl Project works with APEX 26.1 / SQLcl 26.1.2, but it still needs polish.

    • The biggest bug I found was binary file corruption during project export of APEX applications.
    • The skills sync command works, but the current shared/global skill installation model needs improvement for real multi-project, multi-version work.
    • apex import not working through the MCP server is unpleasant because it prevents the agent from completing the full validate-and-import loop through MCP.
    • A few command-path and alias-folder behaviors are inconvenient enough that local aliases are currently important.

  • APEXlang is the exciting part.

    • It works, and it changes the feeling of APEX development in a very positive way.
    • It still needs structure around it: SQLcl aliases, a repeatable validation/import workflow, Oracle's APEXlang skill, and grounded local reference material.
    • I ended up needing two local skill layers: one existing APEX UI reference skill with real utility classes, icons, CSS variables, and UI patterns; and one new APEXlang liaison skill that tells the agent how to find and use the right APEXlang and APEX UI context. Both are included in this repository.

  • I was able to complete a full demo development cycle.

    • I deployed the existing SQLcl Project to APEX 26.1.
    • I exported it as APEXlang, modified it with an agent, validated the APEXlang, imported it back into the database, reviewed the result, and continued toward the SQLcl Project workflow.
    • That is enough for me to be ready for a client upgrade, with the understanding that the workflow still needs care.

  • The GitHub Copilot experience was negative in this test.

    • I could not make the workflow practical with Copilot because simple experiments consumed roughly 70% of my monthly business allowance and still felt slow.
    • In that Copilot test, I used strong Claude models, including Claude Sonnet 4.6.
    • I am not claiming the cause was Oracle's APEX skill or APEXlang itself.
    • The practical result is the important part: with Copilot, the experiment nearly exhausted my monthly budget; with Codex using GPT-5.5, I was able to make the workflow work while using a reasonable part of my ChatGPT Pro allowance.

14.1. Overview

This document captures my first experience installing the APEX 26.1 / SQLcl 26.1.2 toolchain and using APEXlang with an existing SQLcl Project repository.

The short version: it works, and the experience is very promising. I was able to install the required components, export an existing APEX application as APEXlang, edit it through an AI agent, validate the generated APEXlang, and import it back into the database. At the same time, the workflow still needs several workarounds, local aliases, and repository-specific AI instructions to be productive.

This is not intended to be a formal Oracle installation guide. It is a field report from one working setup, with the practical notes I would want before repeating the process.

14.2. Environment and Toolchain

The test environment used:

  • Oracle AI Database 26ai Free VirtualBox Appliance
  • ORDS upgraded for the new VM
  • APEX 26.1
  • SQLcl 26.1.2
  • SQLcl Project
  • APEXlang compiler built into SQLcl
  • Oracle APEX AI skills installed through SQLcl
  • GitHub Copilot and Codex as AI agent clients

The existing SQLcl Project repository had previously been working with APEX 24.2-style exports. After upgrading the toolchain, project export started exporting the APEX application in APEXlang format instead of the previous YAML structure.

14.3. Installing the Required Components

The first important discovery was that APEX 26.1 could not be installed into my existing Oracle Free VM, which was still on database version 23. The APEX installer reported that this database version was not supported.

I had to create a new Oracle Database Free 26ai VM and then install or upgrade the rest of the stack there:

  1. Install or start a new Oracle Database Free 26ai VM.
  2. Upgrade ORDS for the new database environment.
  3. Install or upgrade APEX to 26.1 using the standard APEX procedure.
  4. Download and install SQLcl 26.1.2.

After the stack was ready, I deployed my existing SQLcl Project into the new 26.1 database and APEX 26.1 environment using the normal SQLcl deployment workflow. That deployment worked without problems. This deployed application became the application I later exported as APEXlang.

Important note: I downloaded the Oracle AI Database 26ai Free VirtualBox Appliance on Monday, but by the time I was writing this document Oracle had already retired that appliance download page. The URL is still useful as a reference, but readers may need to use another Oracle 26ai Free option instead.

Current download/reference pages:

For an example of the standard APEX installation flow used in this repository, see VM 1. Configure APEX and ORDS on Secondary PDB.

For the ORDS upgrade, I used Connor McDonald's article:

https://connor-mcdonald.com/2023/07/06/ords-upgrade-to-23-2-cmon-folks-its-easy/

14.4. Installing Oracle APEX Skills

SQLcl now includes a skills command. The two important subcommands for this workflow are:

skills list
skills sync --skill-name apex

I ran the sync command from SQLcl without needing to connect to a database:

skills sync --skill-name apex

In my test, this command used Oracle's public skills repository:

The command first creates local SQLcl skill content under the user's $HOME/.dbtools directory. Under that directory, SQLcl creates a skills folder and clones the repository content it needs.

After that, skills sync copies the synced skills into the shared AI-agent skill locations:

  • Copilot: $HOME/.copilot/skills
  • Codex: $HOME/.codex/skills

The important detail is that the APEXlang skill is not installed as an obvious top-level project-local skill. It lives under the shared apex skill, in the apexlang subfolder.

That works, but it creates two practical concerns:

  • Agents may not immediately discover that APEXlang guidance lives under the shared APEX skill.
  • Shared global skills are awkward when different repositories target different APEX versions.

For example, if one repository is on APEX 26.1, another is on APEX 26.2, and another is still on APEX 24.2, a single shared APEXlang skill is not always the right context. My preference is that SQLcl should provide an option to install or sync a skill directly into the repository where the command is called.

Enhancement request:

14.5. Exporting an Existing SQLcl Project to APEXlang

After installing the updated stack, I ran:

project export -o APEX.<app_id>

This was one of the most exciting parts of the test. Without changing my SQLcl Project configuration, the application was exported as APEXlang. Previously, the same project exported APEX metadata as YAML.

However, I found two important issues.

First, in SQLcl 26.1.2, project export appears to corrupt binary files embedded in an APEX application, such as images and attachments. My assumption is that the export process is reading some binary streams as text streams. The regular SQLcl command apex export, discussed below, handles these files correctly, so my workaround is to run an APEX export after project export for affected applications.

Bug reported: SQLcl corrupts APEX static files during export (APEXLang)

Second, when an APEX application alias changes, project export creates a new alias-based folder but does not remove the old one. In a SQLcl Project repository, that can leave stale APEX application folders behind.

Bug reported: SQLcl Project export should remove stale APEX alias folders when the application alias changes

I updated the repository alias prj_exp_app to make this safer and more repeatable for SQLcl 26.1.2. It removes the previous application export folder, runs project export, and then runs apex export after project export. The second export overwrites the corrupted binary files created by the first export.

Today, the exported APEX application structure under src/database/<schema>/apex_apps/ looks like this:

fNNN/
|-- fNNN.sql
`-- <app-alias>/
    |-- .apex/
    |   `-- apexlang.json
    |
    |-- application.apx
    |-- page-groups.apx
    |
    |-- deployments/
    |   `-- default.json
    |
    |-- pages/
    |   |-- p00000-global-page.apx
    |   |-- p00001-home.apx
    |   |-- p00002-some-page.apx
    |   |-- p00003-some-form.apx
    |   |-- ...
    |   `-- p09999-login.apx
    |
    |-- shared-components/
    |   |-- acl-roles.apx
    |   |-- authentications.apx
    |   |-- authorizations.apx
    |   |-- breadcrumbs.apx
    |   |-- build-options.apx
    |   |-- component-settings.apx
    |   |-- lists.apx
    |   |-- lovs.apx
    |   |-- static-files.apx
    |   |
    |   |-- static-files/
    |   |   |-- icons/
    |   |   |   |-- app-icon-32.png
    |   |   |   |-- app-icon-192.png
    |   |   |   `-- app-icon-512.png
    |   |   `-- ...
    |   |
    |   `-- themes/
    |       `-- <theme-name>/
    |           `-- theme.apx
    |
    `-- supporting-objects/
        |-- install.apx
        `-- upgrade.apx

One very important SQLcl Project detail: project stage is not staging APEXlang files directly. Even after the APEXlang export is present in the repository, project export still exports the fNNN.sql application file, and project stage stages that fNNN.sql file. The APEXlang tree is still extremely useful for review and AI-assisted development, but the deployment artifact produced by SQLcl Project still goes through the generated SQL application export.

Alias reference:

14.6. Validating and Importing APEXlang

SQLcl 26.1.2 provides APEXlang validation and import commands:

apex validate -input <apexlang-path> -ws <workspace>
apex import -input <apexlang-path> -ws <workspace>

Oracle documents APEXlang commands here:

apex validate reads APEXlang files and validates their syntax and structure. It behaves like a compiler check: if something is wrong, it reports syntax or component errors.

apex import performs validation first and then imports the APEXlang into the target APEX application in the currently connected database. In practice, this is the command that "compiles" APEXlang back into the live APEX application.

This split is very useful for AI-assisted development. The agent can edit .apx files, run apex validate, fix compiler errors, and only report the task complete after the APEXlang passes validation.

In my test:

  • apex validate worked through the MCP server.
  • apex import did not work through the MCP server because of an autocommit-related issue.

The exact error I received when trying to run apex import through MCP was:

Importing application ID: 106 into workspace: DEMO1

APEXLang Import Errors:
PLSQL_ERROR
ORA-17274: Could not rollback with auto-commit enabled.

That means validation can be automated safely inside the agent loop, but import may still need to be run manually from SQLcl until the MCP issue is fixed.

Bug report:

14.7. SQLcl Aliases for the Workflow

To make the workflow easier, I added or updated SQLcl aliases in:

You can load these aliases into your SQLcl environment with:

alias load <path_to_xml_file>

For this repository:

alias load scripts/xml/sqlcl-project-aliases.xml

The most relevant aliases are:

Alias Purpose
prj_exp_app Export an APEX application through SQLcl Project and work around the SQLcl 26.1.2 binary export issue.
prj_validate Run apex validate against the exported APEXlang application.
prj_compile Run apex import, which validates and imports the APEXlang application into the connected database.

Typical flow:

conn -name proj_dev
prj_exp_app 110
-- Do agentic and hands-on development by modifying the APEXlang files.
-- When development is done:
prj_validate 110
prj_compile 110
-- Review results, commit, and then continue with the SQLcl Project stage flow.

Again, project stage stages the generated fNNN.sql APEX export, not the APEXlang .apx files directly.

The command reference in 12. Common Commands, Directives, and Aliases should stay updated with these aliases because they are now part of the practical SQLcl Project workflow.

14.8. Working with APEXlang Through AI Agents

Once the application was exported as APEXlang, I tested agent-driven edits. I asked the agent to create or update pages and add components such as charts, cards, and faceted reports.

The experience was genuinely impressive. For anyone who has built APEX applications manually for years, being able to describe a page and have an agent produce APEXlang is a major step forward.

At the same time, there were several important limitations.

First, token and credit burn was significant in my GitHub Copilot test. In my environment, working with APEXlang through Copilot consumed a very large part of my monthly business allocation in a short period and the interaction was often slow. I used strong Claude models in Copilot, including Claude Sonnet 4.6, so this was not a test with a weak model.

Codex worked much better than GitHub Copilot in my test. With Codex using GPT-5.5, the workflow was faster, used a reasonable part of my ChatGPT Pro allowance, and handled the APEXlang workflow more comfortably. This may be specific to my environment, model choices, and the current state of both products, but it was a very clear practical difference during this test.

Second, agents can hallucinate APEXlang properties, component attributes, CSS utility classes, Font APEX icons, and other APEX-specific details. The compiler catches invalid APEXlang properties, but it does not catch every incorrect CSS class or visual choice. This makes grounding very important.

This is where the local apex-ui skill became even more important than before:

This skill existed before APEXlang in this repository. It contains grounded APEX UI reference material: utility classes, Font APEX icons, CSS variables, badge patterns, select item notes, and HTML escaping guidance. With APEXlang, it becomes more valuable because an agent is no longer only suggesting UI ideas; it is writing application source files.

One concrete example: I asked the agent to stretch a badge across the available field width. The agent spent several iterations trying different ideas because it did not know the APEX utility class that solved the problem. After pointing it back to apex-ui, it could use the width utility classes documented there, including w[5-100]p percentage-width classes such as w100p.

That kind of information is not APEXlang syntax, so apex validate will not protect us from getting it wrong. APEXlang validation can tell us whether a property exists; the apex-ui skill helps ground visual details that are valid only if the APEX theme, utility classes, icons, and CSS variables are real.

The most useful mitigation was to enforce a validation loop:

  1. Generate or update the .apx file.
  2. Run apex validate.
  3. Fix compiler errors.
  4. Repeat until validation passes.

For properties and component structures, the Oracle APEXlang skill also provides Node-based tooling for querying compiler-backed truth. This is useful, but it means the local environment needs to be ready to run those Node commands.

14.9. Repository-Specific APEXlang Skill

To make the workflow more reliable, I added a repository-specific APEXlang extension skill:

This skill captures local lessons learned and points the agent toward the shared Oracle APEXlang skill.

The current rules include:

  • Use the shared apex/apexlang skill as the authoritative APEXlang source.
  • Query compiler-backed property information when unsure about component properties.
  • Always validate generated or updated .apx files before completing a task.
  • Keep APEX page file names and page aliases synchronized.
  • Add iconCssClasses: fa when Font APEX icons are used in card/icon scenarios that require it.

This file is expected to grow as more real APEX 26.1 work moves through the APEXlang workflow.

14.10. Issues and Enhancement Requests

Area Issue Impact Workaround Link
skills sync Installs shared global skills rather than a repository-local versioned skill, and does not support granular subskill sync such as db/sqlcl or apex/apexlang. Hard to manage projects on different APEX versions; can also create unnecessary context noise and token usage. Add repo-local extension skill with explicit routing rules. Forum post
project export Corrupts binary files in exported APEX applications in SQLcl 26.1.2. Severe; images and attachments can be damaged. Run apex export after project export for affected apps. Forum post
project export Does not remove stale alias-based APEX export folders after application alias changes. Old folders remain in the repo and can confuse agents or reviews. Delete the old export folder before exporting. Forum post
apex import Does not currently work through the SQLcl MCP server in my test: ORA-17274: Could not rollback with auto-commit enabled. Agent can validate, but cannot complete live import through MCP. Run import manually in SQLcl. Forum post
apex validate / apex import Input path behavior is different from export directory behavior. Counterintuitive and harder to alias. Use aliases such as prj_validate and prj_compile. Forum post

14.11. Lessons Learned

APEXlang in SQLcl Project is already useful. The ability to export an existing APEX application as text, modify it with an agent, validate it with a compiler command, and import it back into the database is a big improvement over manual page work.

The workflow becomes much better with three additions:

  • SQLcl aliases for export, validation, and import.
  • Repository-local AI instructions for APEXlang.
  • Repository-local APEX UI reference skills for utility classes, icons, CSS variables, and component styling.
  • A strict compiler validation loop before accepting generated .apx changes.

The current rough edges are mostly around packaging, command consistency, MCP import support, and a few SQLcl export bugs. Those are important issues, but they do not change the overall conclusion: SQLcl Project plus APEXlang is a very promising direction for APEX development.

14.12. References