Oracle Designer (6i/9i/10g) is a CASE tool from the early 2000s, out of support since 2013, yet still the center of development in many legacy Oracle shops. The common assumption is that you can only work with it through its GUI (Repository Object Navigator, Design Editor). That assumption is wrong.
Designer stores everything in an Oracle schema and ships a complete PL/SQL API next to it. That means an AI coding agent (Claude Code, or any tool that can run sqlplus) can read and write the repository directly: create entities, tables, columns, keys, sequences, triggers, and complete form module definitions — validated by Designer's own engine, without a single click in the GUI. The only steps left for a human are pressing Generate (Forms/DDL generators are client-side) and drawing diagrams.
This repository packages that capability as an agent skill: distilled procedures, discovery queries, known pitfalls with solutions, and proven SQL templates. It is written for Claude Code skills format, but it is plain Markdown — usable as context for any AI coding tool.
Everything here was verified live against a production-grade Designer 10g repository (Oracle 10.2.0.4): a full cycle from empty schema area to a generated, running Oracle Forms module, done entirely through the API in two days, including the debugging of every generator error along the way.
- Architecture: CI_* views (read), CIO* packages (write, one per element type), CDAPI (transactions + validation), JR_CONTEXT (workarea), RMOTEXT (PL/SQL text bodies).
- The universal session pattern: workarea context →
cdapi.initialize(app)→ activity → typed records with value/indicator pairs →validate_activity→close_activity/abort_activity. - Discovery method: the API is self-documenting — read package specs from
all_source, read your site's standards from existing elements (domains, naming, preference sets) instead of vanished documentation. - Element procedures: entity, attribute, table, column, primary key, table–entity mapping, sequence, database trigger with PL/SQL body, complete form module chain (module → window → component → inclusion → table usage → bound items → preference sets), ownership move/share between applications.
- Form generation debugging: how to read the generator output and the
.errfile, and which repository properties fix which CDG error. - Safety engineering for a live shared repository: a change-audit report, a persistent change-log table with a nightly job, defensive scripting rules, rollback strategies.
- ~20 documented pitfalls with exact error messages and fixes.
- Oracle Designer repository (verified on 10g / repository 6i model; single default workarea) reachable via sqlplus.
- A repository user with the repository-user role (created by the repository administration utility). Full admin/owner access is NOT required — everything here worked with a regular repository user.
- For form generation: Oracle Designer client (human-operated) and the physical objects (table, sequence, triggers) created on the database the form compiles against.
Copy the repo into your skills directory:
~/.claude/skills/oracle-designer-cdapi/
├── SKILL.md
├── references/
└── templates/
Then adapt the placeholders (<CONNECT>, <APP>, …) or simply tell the agent your connection string — the skill instructs it to discover everything else from the repository itself.
- ER/diagram layouts are binary — cannot be created or modified via the API.
- Client-side generators (Forms Generator, Server/DDL Generator) must be run by a human; the agent prepares definitions and debugs the results.
- Verified on Designer 10g against Oracle 10.2. Other versions share the same API family (CDAPI dates back to Designer 2.1) but re-verify the specifics.
MIT. Use at your own risk — always test against a non-production repository or a scratch application first, and know your database backup situation before writing.