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

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 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.

Ready to Take Your Oracle Skills to the Next Level?

Master Oracle APEX – Join our Hands-On Intro to Oracle APEX course on OraPub and build powerful applications with expert guidance.

Check out Viscosity’s event page for upcoming virtual and on-site training opportunities.