Exploring the DBMS_DEVELOPER Package: JSON Metadata for Oracle Objects

Oracle Database 23ai introduced the powerful DBMS_DEVELOPER package, giving developers and database administrators a streamlined way to access database object metadata in JSON format. This feature represents a significant advancement in how we interact with database schemas, offering a more structured and programmatic way to extract and analyze metadata compared to traditional dictionary views or the older DBMS_METADATA package. 

In this article, we'll explore the capabilities of DBMS_DEVELOPER, focusing on its GET_METADATA function through detailed examples and practical implementation scenarios.        

Understanding DBMS_DEVELOPER

The DBMS_DEVELOPER package was designed specifically for modern application development patterns, where JSON has become a universal data exchange format. Rather than returning metadata as DDL statements (like DBMS_METADATA), this package returns structured JSON documents that can be easily parsed, processed, and integrated into applications or DevOps workflows.        

Key Benefits

• Structured data format: Returns metadata as JSON objects that can be easily parsed    
• Programmatic access: Perfect for integration with applications and automation scripts  
• Versioning capabilities: Built-in ETag mechanism for tracking object changes
• Configurable detail levels: Ability to retrieve basic, typical, or comprehensive metadata

Setting Up Our Environment

Let's set up a sample schema to demonstrate the package functionality:      

CREATE TABLE customers (
  customer_id NUMBER(10) CONSTRAINT pk_customers PRIMARY KEY,
  first_name  VARCHAR2(50) NOT NULL,
  last_name   VARCHAR2(50) NOT NULL,
  email       VARCHAR2(100) CONSTRAINT uk_customer_email UNIQUE,
  join_date   DATE DEFAULT SYSDATE,
  status      VARCHAR2(10) DEFAULT 'ACTIVE'
);

CREATE INDEX idx_customer_name ON customers(last_name, first_name);

CREATE OR REPLACE VIEW active_customers AS 
  SELECT customer_id, first_name, last_name, email 
  FROM customers 
  WHERE status = 'ACTIVE';

GET_METADATA Basics

The core function of the DBMS_DEVELOPER package is GET_METADATA, which returns metadata about database objects in JSON format. Let's start with a basic example:      

-- Using JSON_SERIALIZE for formatted output
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(name => 'CUSTOMERS') 
  PRETTY) AS metadata;

The result is a structured JSON document containing comprehensive information about the table, including:             

• Table name and schema          
• Column definitions with data types and constraints        
• Primary key, unique key, and foreign key information     
• Index definitions   
• An etag value representing the current state of the object

This structured format makes it significantly easier to extract specific information programmatically compared to parsing DDL statements.        

NAME and SCHEMA Parameters

The NAME and SCHEMA parameters work together to identify the specific database object. These parameters are case-sensitive and must match the object definition in the data dictionary.      

-- Explicitly specifying schema
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'CUSTOMERS', 
    schema => 'FINANCE') 
  PRETTY) AS metadata;

-- Using current schema (implicit)
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(name => 'CUSTOMERS') 
  PRETTY) AS metadata;

When the SCHEMA parameter is omitted, the function uses the current schema. This behavior provides flexibility when working with objects across different schemas in your database environment.        

OBJECT_TYPE Parameter

The OBJECT_TYPE parameter allows you to explicitly specify the type of object you're retrieving metadata for. While often optional (as the database can infer the object type from the name), it becomes necessary in cases where name resolution alone is insufficient. Currently, `DBMS_DEVELOPER` supports three object types:             

• TABLE
• INDEX
• VIEW

Let's examine metadata for our index and view:      

-- Retrieving index metadata
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'IDX_CUSTOMER_NAME', 
    object_type => 'INDEX') 
  PRETTY) AS metadata;

-- Retrieving view metadata
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'ACTIVE_CUSTOMERS', 
    object_type => 'VIEW') 
  PRETTY) AS metadata;

The OBJECT_TYPE parameter becomes particularly important when dealing with objects that share the same name but have different types, such as packages and package bodies.        

LEVEL Parameter

The LEVEL parameter controls the amount of detail included in the JSON output. Oracle provides three levels:             

• BASIC: Minimal information
• TYPICAL: Standard level of detail (default)
• ALL: Comprehensive metadata

This flexibility lets you balance concise output with detailed information based on your needs.      

-- Basic level metadata
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'IDX_CUSTOMER_NAME', 
    level => 'BASIC') 
  PRETTY) AS metadata;

-- All details
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'IDX_CUSTOMER_NAME', 
    level => 'ALL') 
  PRETTY) AS metadata;

The output at the ALL level includes additional attributes such as segment information, compression settings, and physical storage details that aren't present at the BASIC level.        

ETAG Parameter

One of the most powerful features of DBMS_DEVELOPER is the etag mechanism, which provides version tracking for database objects. The etag value changes whenever the object definition changes, making it invaluable for change detection.      

-- Store the current etag value
DECLARE
  v_metadata CLOB;
  v_etag VARCHAR2(100);
BEGIN
  v_metadata := DBMS_DEVELOPER.GET_METADATA(name => 'ACTIVE_CUSTOMERS');
  SELECT JSON_VALUE(v_metadata, '$.etag') INTO v_etag FROM dual;
  DBMS_OUTPUT.PUT_LINE('Current etag: ' || v_etag);
END;
/

-- Modify the view
CREATE OR REPLACE VIEW active_customers AS 
  SELECT customer_id, first_name, last_name, email, join_date 
  FROM customers 
  WHERE status = 'ACTIVE';

-- Check if the object has changed using the stored etag
SELECT JSON_SERIALIZE(
  DBMS_DEVELOPER.GET_METADATA(
    name => 'ACTIVE_CUSTOMERS', 
    etag => 'A1B2C3D4E5F6G7H8I9J0') -- Previous etag value
  PRETTY) AS metadata;

When you pass an ETag value that matches the current state of the object, the function returns an empty JSON document {}. If the object has changed, it returns the complete metadata with a new ETag value. 

Practical Scenario: Database Migration and Documentation

Let's consider a practical scenario where DBMS_DEVELOPER proves invaluable: a large-scale database migration project with continuous schema changes.        

The Challenge

You're leading a project to migrate a critical application database from on-premises to Oracle Cloud. The development team continues to make schema changes during the migration process, and you need to:        

• Document the current state of all database objects
• Track changes between migration waves
• Validate that objects were created correctly in the target environment
• Generate comprehensive documentation for compliance requirements

The Solution

Using DBMS_DEVELOPER, you can create a robust metadata management system:      

CREATE TABLE schema_versions (
  object_name VARCHAR2(128),
  object_type VARCHAR2(30),
  object_schema VARCHAR2(128),
  capture_date TIMESTAMP,
  etag VARCHAR2(100),
  metadata CLOB
);

-- Procedure to capture all tables in a schema
CREATE OR REPLACE PROCEDURE capture_schema_metadata(p_schema VARCHAR2) AS
  v_metadata CLOB;
  v_etag VARCHAR2(100);
  CURSOR c_objects IS
    SELECT object_name, object_type 
    FROM all_objects 
    WHERE owner = p_schema
    AND object_type IN ('TABLE', 'INDEX', 'VIEW');
BEGIN
  FOR obj IN c_objects LOOP
    BEGIN
      v_metadata := DBMS_DEVELOPER.GET_METADATA(
        name => obj.object_name,
        schema => p_schema,
        object_type => obj.object_type
      );
      
      SELECT JSON_VALUE(v_metadata, '$.etag') INTO v_etag FROM dual;
      
      INSERT INTO schema_versions
        (object_name, object_type, object_schema, capture_date, etag, metadata)
      VALUES
        (obj.object_name, obj.object_type, p_schema, SYSTIMESTAMP, v_etag, v_metadata);
      
      COMMIT;
      
      DBMS_OUTPUT.PUT_LINE('Captured metadata for ' || obj.object_type || 
                           ' ' || p_schema || '.' || obj.object_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error capturing ' || obj.object_type || 
                             ' ' || p_schema || '.' || obj.object_name || 
                             ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

      This solution provides several key benefits:             

• Efficient change tracking: Using etags to identify exactly which objects have changed
• Structured documentation: Storing metadata in JSON format for easy extraction of specific attributes
• Historical record: Maintaining snapshots of schema evolution over time
• Validation capabilities: Comparing source and target schemas during migration

    During migration, you can extend this system to compare environments:      

-- Procedure to compare object between environments
CREATE OR REPLACE PROCEDURE compare_object(
  p_name VARCHAR2,
  p_type VARCHAR2,
  p_source_schema VARCHAR2,
  p_target_schema VARCHAR2,
  p_target_db VARCHAR2
) AS
  v_source_metadata CLOB;
  v_target_metadata CLOB;
  v_source_etag VARCHAR2(100);
  v_target_etag VARCHAR2(100);
BEGIN
  -- Get source metadata
  v_source_metadata := DBMS_DEVELOPER.GET_METADATA(
    name => p_name,
    schema => p_source_schema,
    object_type => p_type
  );
  
  -- Get target metadata via database link
  EXECUTE IMMEDIATE
    'SELECT DBMS_DEVELOPER.GET_METADATA(
      name => :1,
      schema => :2,
      object_type => :3
    ) FROM dual@' || p_target_db
    INTO v_target_metadata
    USING p_name, p_target_schema, p_type;
  
  -- Extract etag values
  SELECT JSON_VALUE(v_source_metadata, '$.etag') INTO v_source_etag FROM dual;
  SELECT JSON_VALUE(v_target_metadata, '$.etag') INTO v_target_etag FROM dual;
  
  -- Compare and report
  IF v_source_etag = v_target_etag THEN
    DBMS_OUTPUT.PUT_LINE('Objects match exactly');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Objects differ - detailed comparison needed');
    -- Further JSON comparison logic could be implemented here
  END;
END;
/

Conclusion

The DBMS_DEVELOPER package represents a significant advancement in Oracle's metadata management capabilities. By providing metadata in JSON format, Oracle has created a more developer-friendly interface that aligns with modern application architecture patterns. Key takeaways include:             

• JSON-based metadata is more programmatically accessible than traditional DDL statements 
• The etag mechanism provides a reliable way to track object changes        
• Multiple detail levels allow you to retrieve just the information you need      
• The package is particularly valuable for documentation, migration, and change tracking

While currently limited to tables, indexes, and views, the DBMS_DEVELOPER package has tremendous potential for expansion in future Oracle releases. Database architects and developers should consider integrating this powerful tool into their workflows, particularly for projects involving schema documentation, migration, or programmatic metadata access. As databases continue to evolve toward more autonomous and programmable systems, tools like DBMS_DEVELOPER will become increasingly central to efficient database management practices.