Skip to main content

🧠 Build a PL/SQL Code Review Skill for Codex — Create, Import, and Use It in Real Time

Calculating read time…

A Codex Skill is a reusable folder of instructions — a SKILL.md file plus optional scripts and reference docs — that teaches Codex how to perform a specific, repeatable job, like reviewing Oracle PL/SQL code the exact way your team wants it reviewed, every single time.

Without a Skill, every PL/SQL review starts from zero: you re-explain "check for unhandled exceptions, check for row-by-row processing, check for SQL injection in dynamic SQL" in every single prompt, and Codex's review quality depends entirely on how well you happened to phrase that prompt that day. A Skill fixes this permanently — write the review checklist once, and Codex applies it consistently, whether it's you, a teammate, or a CI job invoking it.

📦 A Skill is just a folder — SKILL.md plus optional scripts/, references/, and assets/

🎯 Skills activate two ways: explicitly (you type $skill-name) or implicitly (Codex matches your request to the skill's description)

🏗️ Codex loads skills from repo, user, admin, and system locations — so a PL/SQL review skill can live with the project, or globally on your machine

🧠 The whole trick: put the reusable review knowledge in the Skill, not in your prompt — that's what makes the review repeatable instead of reinvented every time

📑 In This Post

🧩 Section 1: What a Codex Skill Actually Is

💡 The Recipe Card Analogy

Think of Codex as a very capable chef who's never worked in your specific kitchen. Every time you ask for a dish without a recipe, the chef improvises — sometimes great, sometimes not quite what you wanted. A Skill is a written recipe card you hand the chef once: exact ingredients, exact steps, exact plating. From then on, whenever someone orders that dish, the chef pulls the card and follows it — consistent, every time, regardless of who's asking.

Structurally, a Skill is nothing exotic — it's a folder with one required file, SKILL.md, containing a small YAML header (a name and a description) followed by plain-English instructions:

my-skill/

📄 SKILL.md — required: metadata + instructions

📁 scripts/ — optional: executable code for deterministic checks

📁 references/ — optional: longer documentation, loaded only when needed

📁 assets/ — optional: templates or files used in output

Codex uses something called progressive disclosure: at startup, it only reads each skill's name and description — cheap, lightweight, no wasted context. Only once Codex actually decides to use a skill does it load the full SKILL.md body, and only then load any referenced files inside references/. This is exactly why the description field matters so much — it's the only thing Codex sees before deciding whether your PL/SQL skill is relevant to what you just asked.

✅ DO: Write the description field like a trigger condition, not a summary — "Use when the user asks to review, audit, or check PL/SQL code, a package body/spec, or Oracle database code" tells Codex exactly when to reach for it.

🗄️ Section 2: Why PL/SQL Code Needs Its Own Review Skill

Codex's general-purpose code review instincts are trained mostly on mainstream application languages. PL/SQL has its own failure patterns that a generic reviewer routinely misses entirely:

🔀 Generic Code Review

Checks naming conventions, obvious logic bugs, and general readability — useful, but blind to Oracle-specific traps like implicit cursor leaks, silently swallowed exceptions, or row-by-row processing where set-based SQL would run 100x faster.

🔗 PL/SQL-Aware Review (this skill)

Checks the same general things, plus a checklist built specifically for Oracle: bind variables in dynamic SQL, WHEN OTHERS THEN NULL exception swallowing, missing BULK COLLECT/FORALL, unbounded cursor loops, and definer's-rights privilege risks.

⚠️ A Real Failure Pattern This Catches: A common PL/SQL anti-pattern is EXCEPTION WHEN OTHERS THEN NULL; — this silently swallows every error, including ones that should have stopped a transaction. A generic reviewer often skips right past it because syntactically it's valid, harmless-looking code. A PL/SQL-aware skill flags it every time, because the checklist explicitly knows to look for it.

📋 Section 3: Designing the Review Checklist

Before writing a single line of SKILL.md, decide what the skill should actually check. A solid PL/SQL review checklist covers four categories:

🔐 Security — dynamic SQL built with string concatenation instead of bind variables (SQL injection risk), unnecessary use of AUTHID DEFINER, over-broad GRANT statements referenced in comments.

⚡ Performance — row-by-row (FOR ... LOOP) processing where BULK COLLECT/FORALL would avoid excessive context switches, missing indexes implied by WHERE clauses, unnecessary COMMITs inside loops.

🧯 Error Handling — bare WHEN OTHERS THEN NULL, exceptions caught but not logged, missing RAISE or RAISE_APPLICATION_ERROR after a caught error that should propagate.

📐 Standards — missing package-level comments, inconsistent naming (e.g. no p_ prefix for parameters), procedures exceeding a reasonable length without being broken into smaller units.

✅ DO: Keep this checklist as the actual content of your SKILL.md and references/ files — Section 4 turns exactly this checklist into a working skill.

🛠️ Section 4: Step-by-Step — Build the PL/SQL Review Skill

  1. Create the skill folder.
    Skills are just folders — the folder name becomes part of how you'll reference it later. 
    mkdir -p plsql-reviewer/references
cd plsql-reviewer
  2. Write the main SKILL.md file.
    This is the file Codex always reads once it decides to use the skill. Keep the instructions concise and imperative — push the exhaustive rule list into a separate reference file (Step 3) so this stays fast to load.
    📌 What This File Does: The YAML frontmatter (name, description) is what Codex scans at startup to decide relevance. The body below it is the step-by-step procedure Codex follows once it activates the skill — including how to format its findings, so every review comes back in the same structure. 
    ---
name: plsql-reviewer
description: Reviews Oracle PL/SQL packages, procedures, functions, and triggers for security, performance, error-handling, and coding-standard issues. Use when the user asks to review, audit, or check PL/SQL code, a package body/spec, a procedure, a function, a trigger, or Oracle database code before commit or deployment.
---

You are reviewing Oracle PL/SQL code. Follow this procedure exactly.

1. Read the full PL/SQL file or diff provided by the user before commenting
   on anything. Do not review partial context.

2. Load references/plsql-checklist.md and check the code against every
   rule in it. Do not skip categories even if the code looks clean at a
   glance.

3. If scripts/static_checks.py is available and the user is working from a
   real file (not pasted text), run it and treat any findings it reports as
   candidates to verify manually, not automatic conclusions.

4. Classify every issue found as Critical, High, Medium, or Low severity:
   - Critical: SQL injection risk, or silently swallowed exceptions that
     could mask data corruption.
   - High: missing bulk operations causing severe performance risk at scale,
     or exceptions caught without logging or re-raising.
   - Medium: standards violations that hurt maintainability but don't
     affect correctness or security.
   - Low: minor style/naming inconsistencies.

5. Output the review as a Markdown report with this exact structure:
   - One-line summary (pass/fail-style verdict)
   - Findings grouped by severity, each with: the procedure/line reference,
     a one-sentence explanation of the problem, and a concrete fix.
   - A final "Nothing else flagged" note only if fewer than 3 issues total
     were found, to avoid implying a false sense of completeness when review depth
     was necessarily limited.

6. Never rewrite the user's code automatically unless they explicitly ask
   for a corrected version after seeing the findings.
  3. Write the detailed checklist as a reference file.
    📌 What This File Does: This is the exhaustive rule list from Section 3, turned into concrete, checkable items. It lives in references/ instead of directly in SKILL.md so Codex only loads it when actually performing a review — keeping the initial skill listing lightweight. 
    # references/plsql-checklist.md

## Security
- Dynamic SQL (EXECUTE IMMEDIATE) built via string concatenation of
  user-supplied values instead of bind variables (:1, :2, USING clause).
- AUTHID DEFINER used without a clear justification in comments.
- Any GRANT or privilege-escalation logic embedded in package code.

## Performance
- FOR loops issuing one DML statement per row where BULK COLLECT + FORALL
  would batch the operation instead.
- Cursors opened without a matching CLOSE, or without %NOTFOUND handling
  that could cause an infinite loop.
- COMMIT statements inside a loop instead of after the batch completes.
- Missing indexes implied by WHERE clauses on large tables (flag as a
  question to verify with the DBA, not a certain fact).

## Error Handling
- "WHEN OTHERS THEN NULL" or any exception handler that swallows an error
  without logging or re-raising it.
- Exceptions caught but not logged anywhere (no INSERT into an error log
  table, no DBMS_OUTPUT, no RAISE).
- Missing RAISE_APPLICATION_ERROR with a meaningful error code and message
  for business-rule violations.

## Standards
- Missing header comment on package specification describing its purpose.
- Parameters not prefixed consistently (e.g. p_ for parameters, v_ for
  local variables, g_ for package globals).
- Single procedure or function exceeding roughly 150 lines without being
  broken into smaller, named units.
  4. (Optional) Add a lightweight static-check script.
    📌 What This Script Does: Scans a real .pkb/.sql file for two of the most common, mechanically-detectable red flags — dynamic SQL built by string concatenation, and swallowed exceptions — before Codex even applies its own judgment. This catches obvious cases fast and consistently; Codex still reviews everything else. 
    # scripts/static_checks.py
import re
import sys

def check_file(path):
    with open(path) as f:
        lines = f.readlines()

    findings = []
    for i, line in enumerate(lines, start=1):
        # Flag likely string-concatenated dynamic SQL
        if re.search(r"EXECUTE IMMEDIATE.*\|\|", line, re.IGNORECASE):
            findings.append(f"Line {i}: possible unbound dynamic SQL - {line.strip()}")

        # Flag swallowed exceptions
        if re.search(r"WHEN\s+OTHERS\s+THEN\s+NULL", line, re.IGNORECASE):
            findings.append(f"Line {i}: exception swallowed silently - {line.strip()}")

    return findings

if __name__ == "__main__":
    path = sys.argv[1]
    results = check_file(path)
    if results:
        print(f"Static check findings in {path}:")
        for r in results:
            print(f" - {r}")
    else:
        print(f"No mechanical red flags found in {path} (manual review still required).")
    ⚠️ Note: This script is intentionally simple pattern-matching — it will miss subtler cases and can flag false positives. Step 2's SKILL.md instructions deliberately tell Codex to treat its output as "candidates to verify," not conclusions.

Your finished folder should look like this:

plsql-reviewer/

📄 SKILL.md

📁 references/plsql-checklist.md

📁 scripts/static_checks.py (optional)

📥 Section 5: Step-by-Step — Import the Skill Into Codex

Codex looks for skills in several locations, depending on whether you want the skill available everywhere on your machine, or scoped to one repository. Pick the location that matches your intent:

Scope Location Use For
USER (personal, any project)$HOME/.agents/skills/Your own PL/SQL reviews across every repo you work in
REPO (team-shared)$REPO_ROOT/.agents/skills/Checked into Git so every teammate gets the same review standard
ADMIN (org-wide)/etc/codex/skills/Every user on a shared machine or build server
  1. Choose USER scope for your first test.
    Create the folder if it doesn't exist yet, then copy your skill in: 
    mkdir -p ~/.agents/skills
cp -r plsql-reviewer ~/.agents/skills/
  2. Let Codex detect it automatically.
    Codex detects skill changes on its own in most cases. Start (or restart) a Codex session: 
    codex
    If the skill doesn't appear right away, restart Codex once — this is the documented fallback, not a sign something is broken.
  3. Verify it loaded.
    Inside the Codex session: 
    /skills
    What you should see: plsql-reviewer listed alongside its description and file path. If it's missing, double-check the folder actually contains a SKILL.md file directly inside it (not nested one level too deep).
  4. For team rollout, use REPO scope instead.
    Inside your Oracle project's Git repository: 
    mkdir -p .agents/skills
cp -r plsql-reviewer .agents/skills/
git add .agents/skills/plsql-reviewer
git commit -m "Add PL/SQL code review skill for Codex"
    What this does: Codex scans .agents/skills from your current folder up to the repository root, so anyone who clones the repo and runs Codex from inside it automatically gets the same review skill — no separate installation step per teammate.
✅ DO: Start with USER scope while you're still tuning the checklist, then move the finished, tested version into REPO scope so the whole team benefits from the same standard.

⚡ Section 6: Using the Skill in Real Time

Once imported, you can trigger the skill two ways:

🎯 Explicit Invocation

You directly name the skill, guaranteeing it's used:

$plsql-reviewer review packages/order_processing.pkb

🤖 Implicit Invocation

You just describe the task naturally, and Codex matches it to the skill's description on its own:

Can you review this PL/SQL package before I commit it?

Here's what a real, live session looks like — walking through a package with a deliberately planted issue:

You: $plsql-reviewer review update_order_status procedure in order_pkg.pkb

⬇️

Codex: loads the skill, reads the full procedure, checks it against references/plsql-checklist.md, runs scripts/static_checks.py if the file is on disk.

⬇️

Codex's report (example):

Verdict: 2 issues found - do not deploy as-is.

CRITICAL
- Line 47: WHEN OTHERS THEN NULL silently swallows any error during the
  status update, including constraint violations. Fix: log the error via
  an error table or DBMS_OUTPUT, then RAISE to let the caller know the
  update failed.

MEDIUM
- Line 12: Loop issues one UPDATE per row (row-by-row) instead of a single
  set-based UPDATE or BULK COLLECT + FORALL. At current data volumes this
  is likely fine, but will not scale past a few thousand rows per run.
✅ DO: Ask follow-up questions in the same session — "show me the corrected version of line 47" — the skill's instructions deliberately don't auto-rewrite code, so you stay in control of what actually changes.

👥 Section 7: Team Rollout — Sharing the Skill

Once the skill is proven on your own machine, three moves turn it into a team standard rather than a personal habit:

1. Commit it to REPO scope (Section 5, Step 4) so it travels with the codebase automatically.

2. Reference it in your PR template or contributing guide — "run $plsql-reviewer before requesting review" — so it becomes part of the workflow, not just available.

3. Package it as a plugin if you want it distributed beyond one repository — plugins can bundle multiple related skills (e.g. one for PL/SQL, one for SQL migration scripts) and optionally an MCP server connection alongside them.

🎯 Use this when: Standardizing code review practices across a database team that's adopting Codex — this turns "please remember to check for X" into something Codex enforces automatically, every time.

⚠️ Section 8: Common Mistakes

Writing a vague description field.
A description like "reviews code" won't reliably trigger implicit invocation, because Codex has no strong signal that this skill applies to PL/SQL specifically versus any other language. Be explicit: name PL/SQL, packages, procedures, triggers, and Oracle by name.

Putting the entire checklist directly inside SKILL.md instead of references/.
This works, but defeats progressive disclosure — every skill listing gets heavier, and large skill sets can get partially omitted from Codex's initial view if descriptions get too long collectively.

Nesting SKILL.md one folder too deep.
Codex expects SKILL.md directly inside the skill's own folder (e.g. plsql-reviewer/SKILL.md), not inside a further subfolder. A misplaced file simply won't be detected, with no obvious error message.

Forgetting to restart Codex after a skill change that wasn't auto-detected.
Auto-detection works most of the time, but if an edit doesn't appear reflected, restart before assuming the skill itself is broken.

❓ Section 9: FAQ

Does the skill actually connect to my Oracle database?
No — a Skill is instructions only. If you want Codex to run queries against a live database (not just review code you paste or point it to), that's a separate MCP server connection (like the Oracle SQLcl MCP server), which a skill can optionally reference as a dependency but doesn't replace.

Can I use this same skill in Claude Code or other agents?
Yes — SKILL.md follows an open, shared specification, so the same folder generally works across Codex, Claude Code, and other compatible agents with little to no modification.

What happens if two skills have the same name?
Codex does not merge them — both will appear separately in skill selectors, which can be confusing. Keep skill names unique across the locations you use.

Can the skill automatically fix the issues it finds?
Only if you ask it to. The example SKILL.md in this post deliberately instructs Codex not to rewrite code automatically, so a review never silently turns into an unreviewed change.

How do I temporarily disable the skill without deleting it?
Add an entry to ~/.codex/config.toml:

[[skills.config]]
path = "/path/to/plsql-reviewer/SKILL.md"
enabled = false

Restart Codex afterward for the change to take effect.

🎉 Section 10: Summary

🧩 A Skill is a folder, not magic — SKILL.md plus optional scripts and references, teaching Codex a repeatable procedure

🗄️ PL/SQL needs its own checklist — generic review misses Oracle-specific traps like swallowed exceptions and row-by-row processing

📋 The checklist is the real value — security, performance, error handling, and standards, written once and applied every time

📥 Import scope matches intent — USER for personal use, REPO for team-shared standards checked into Git

⚡ Invoke explicitly with $skill-name or let Codex match it implicitly — both work, explicit invocation guarantees it

👥 Team rollout is just committing the folder — no separate install step for teammates once it's in .agents/skills at the repo root

✅ The Core Lesson: A good prompt gets you one good review. A good Skill gets you the same good review, every time, from anyone on the team, without re-explaining your standards. That's the actual leverage — not smarter AI, just knowledge that doesn't have to be re-typed.

Happy Reviewing! 🔥

Labels:

Comments

Post a Comment