Describe tables to your AI with APEX_DB_DICTIONARY

A

APEX 26.1 shipped a small package called APEX_DB_DICTIONARY, and at first glance you might ignore it. We already have DBMS_METADATA for pulling object definitions, right? Well, these two solve different problems, and once I saw the difference I started reaching for the new one a lot.

Here is the short version. DBMS_METADATA gives you DDL meant to recreate an object. APEX_DB_DICTIONARY gives you a description meant to be read, by a human or by an LLM.

Noisy old way

Say you want to hand the structure of EMP to your AI, so it can write you some queries. The reflex is GET_DDL.

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP') FROM dual;

And what you get back is a wall of storage clauses, tablespace names, segment attributes and other junk you don't care about. To make it readable you have to do a lof of transformations first.

BEGIN
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
END;
/

That helps, but you still get a CREATE TABLE statement. It tells the model how to build the table, not what the table means. The column comments live somewhere else, the constraints come back with useless names, and a single table can eat a few hundred tokens before you have said anything useful.

DBMS_METADATA is great at its actual job, which is migration and object recreation. For AI use it is the wrong tool.

The new way

APEX_DB_DICTIONARY formats the result as markdown or plain text. One call, several tables, done.

DECLARE
    v_text CLOB;
BEGIN
    v_text := APEX_DB_DICTIONARY.GET_TABLE_INFO(
        p_table_names => 'EMP, DEPT'
    );
    DBMS_OUTPUT.PUT_LINE(v_text);
END;
/

What comes back is a tidy structural summary. Columns with their types, the primary and foreign keys spelled out in plain language, table and column comments folded in, plus annotations and SQL Domain info if you are using that. The comments and annotations are together with the structure, so the model gets the meaning behind the shape.

By default it shows constraints, comments, annotations and domains, and skips indexes and virtual columns. You can flip those with boolean parameters, so you decide how much detail goes into the prompt.

Wait, do we not already have DBMS_DEVELOPER for this?

This is the part that confused me at first. Oracle 23ai shipped DBMS_DEVELOPER.GET_METADATA, backported to 19c, and Jeff Smith wrote a nice piece on it: QUICKLY access database object metadata via DBMS_DEVELOPER. So now we seemingly have two packages doing the same job.

Think of it as engine and bodywork. DBMS_DEVELOPER is the engine. It is a database primitive that returns metadata as JSON, one object at a time, with a minimal privilege model and invoker rights. It is fast and it is meant to be called by tools. Raw, single object, JSON.

APEX_DB_DICTIONARY is the bodywork APEX wraps around that engine. It calls GET_METADATA under the hood and then does some magic on top:

• formats the output as markdown or plain text instead of JSON
• takes a list of tables, or a regex, instead of one object at a time
• folds in comments, annotations and SQL Domains in a layout meant to be read
• gives you helper calls like a primary-key lookup or a plain table inventory
• returns native JSON on 21c and up, and falls back to CLOB on 19c

Grab a whole schema with a regex

Here is the nifty bit. You do not have to name every table. Feed it a pattern. Use "." to match everything in the current schema. So in one shot you turn your data model into a markdown document you can paste straight into a prompt, save as a context file, or attach to an APEX AI assistant configuration.

If you only want the inventory and not the full structure, there is a lighter call that lists table names with their comments.

DECLARE
    v_summary CLOB;
BEGIN
    v_summary := APEX_DB_DICTIONARY.GET_TABLES_SUMMARY(
        p_regex       => '^ABC_',
        p_object_type => 'TABLE',
        p_format      => APEX_DB_DICTIONARY.C_PLAIN
    );
    DBMS_OUTPUT.PUT_LINE(v_summary);
END;
/

Why do you need it

• DBMS_METADATA – recreating objects: DDL for migrations and schema exports
• DBMS_DEVELOPER – building tools: raw JSON metadata feed, one object at a time
• APEX_DB_DICTIONARY – talking to AI: prompt-ready markdown description, one call

If you are building anything that talks to an AI about your schema, a chat assistant over your data, a query generator, a Claude Code session reasoning about your tables, this is the cheapest way to give the model good context. It is compact, it carries your comments and annotations, and it is one function call instead of a transform ritual.

Try it against your own schema, run the regex over your prefix, and see how clean the output is compared to a GET_DDL dump.

If this saved you some time, pass it on to a colleague who is wiring AI into their workflow.