What if documenting your code was as simple as running a stored procedure?

Developers love to build — but few enjoy writing technical documentation. In this post, I’ll walk you through how I automated the generation of technical docs for Oracle APEX and PL/SQL using OpenAI, metadata queries, and a bit of creative design inside APEX.

🧠 The Problem

Every application has packages, procedures, and tables that deserve documentation — yet it’s the first thing we skip and the last thing we regret skipping.

⚙️ The Approach

Here’s what I built:

A simple PL/SQL package that:
- Loops through all your objects (DBA_OBJECTS)
- Fetches their DDL using DBMS_METADATA
- Sends that DDL to OpenAI via REST
- Stores the Markdown output in a versioned registry table
An APEX app that:
- Lets you explore documentation by object, type, and version
- Displays Markdown using native components
- Supports multi-role documentation types: TECHNICAL, USER ACCEPTANCE, CI/CD

🧩 A few Screenshots on how an APEX tool could look

✍️ Sample Prompt (Technical)

Here’s the prompt I use for generating technical documentation:

You are a senior software architect. Generate concise, professional technical documentation in Markdown format for the given Oracle database object. The documentation must include:

## Purpose
- A brief paragraph describing the objective of the object.
## Structure
- Key components or logic breakdown (e.g., functions, procedures, queries).
## Parameters
- If the object accepts input, detail each parameter, its type, and purpose.
## Outputs
- What the object returns or affects (if any).
## Usage
- Common or recommended use cases.
## Notes
- Any best practices, side effects, exceptions, or important warnings.

Use headers, bullet points, and code blocks (```sql) where suitable. Maintain clarity and avoid redundant explanations or repetition.

You can customize prompts per documentation type. Or let the ai to assist you and get the first version.

🎯 Results

The result? Instantly readable, consistently formatted documentation — generated in seconds. Here’s a preview:

📚 Additional Resources

The code? You can find it on a dedicated github repo.

💡 Why This Matters:

Saves time
Could improve onboarding for new devs if implemented with team standards.
Keeps documentation up-to-date as code evolves
Provides a starting point for developers generating their first draft.
Integrates directly into APEX for low friction
Could be integrated with version control tools e.g git.

🚀 What’s Next?

In future posts, I’ll show how to:

Generate user acceptance docs using test scenarios
Draft new package templates with OpenAI
Build data models from user stories

Let AI handle that — so you can focus on what matters: building great software.

Got questions or ideas? Leave a comment or connect with me on Hashnode/LinkedIn.

From Metadata to Manuscripts: How To Use AI to Generate Database Documentation