With Oracle Database 23ai RU 23.7, Oracle introduced the new package DBMS_DEVELOPER. 
Note: With Oracle Database RU 19.28, it’s also available in 19c. 

Note: If you are interested in features and release updates you can find more information in the blog post New Features and Release Updates in 19c and 23ai.
According to the documenation it offers straightforward and user-friendly methods for accessing information about database objects.

But which Oracle Database interfaces or tools are available so far for this purpose? Tools such as SQL*PLUS, SQL Developer, SQL Modeler, or SQLcl offer retrieval techniques for metadata. To obtain details about database objects, specific line mode commands like DESCRIBE, INFO, and DDL can be used, alongside special menu tabs. Here are some interesting examples for SQLcl you might remember.

SQL> info hr.countries
TABLE: COUNTRIES
         LAST ANALYZED:2025-02-05 16:20:42.0
         ROWS         :25
         SAMPLE SIZE  :25
         INMEMORY     :DISABLED
         COMMENTS     :country table. References with locations table.
Columns
NAME           DATA TYPE           NULL  DEFAULT    COMMENTS
*COUNTRY_ID    CHAR(2 BYTE)        No               Primary key of countries table.
 COUNTRY_NAME  VARCHAR2(60 BYTE)   Yes              displayed
 REGION_ID     NUMBER              Yes              Region ID for the country. Foreign key to
                                                    region_id column in the departments table.
Indexes
INDEX_NAME            UNIQUENESS    STATUS    FUNCIDX_STATUS    COLUMNS
_____________________ _____________ _________ _________________ _____________
HR.COUNTRY_C_ID_PK    UNIQUE        VALID                       COUNTRY_ID

References
TABLE_NAME    CONSTRAINT_NAME    DELETE_RULE    STATUS     DEFERRABLE        VALIDATED    GENERATED
_____________ __________________ ______________ __________ _________________ ____________ ____________
LOCATIONS     LOC_C_ID_FK        NO ACTION      ENABLED    NOT DEFERRABLE    VALIDATED    USER NAME

The next example shows how to generate the DDL statement.

SQL> ddl hr.countries
  CREATE TABLE "HR"."COUNTRIES"
   (    "COUNTRY_ID" CHAR(2) COLLATE "USING_NLS_COMP" CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE,
        "COUNTRY_NAME" VARCHAR2(60) COLLATE "USING_NLS_COMP",
        "REGION_ID" NUMBER,
         CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE,
         CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID")
          REFERENCES "HR"."REGIONS" ("REGION_ID") ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 PCTTHRESHOLD 50;
   COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_ID" IS 'Primary key of countries table.';
   COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_NAME" IS 'displayed';
   COMMENT ON COLUMN "HR"."COUNTRIES"."REGION_ID" IS 'Region ID for the country. Foreign key to region_id column in the departments table.';
   COMMENT ON TABLE "HR"."COUNTRIES"  IS 'country table. References with locations table.';
Operation completed successfully

In addition don’t overlook the powerful PL/SQL Package DBMS_METADATA. It provides a method to retrieve metadata from the database dictionary in both XML format and DDL, and you can use the XML to re-create the corresponding object.  For more information please check the Further Readings section below.

But what about the new package DBMS_DEVELOPER? Unlike DBMS_METADATA and the other utilities previously mentioned, the results are delivered in JSON format and require only minimal privileges, specifically SELECT and READ on the objects. Additionally, the retrieval process is very quick.

At the moment there is only one function available – the function GET_METADATA. It takes the required information about the object and produces a JSON document containing the corresponding metadata for that object. Here is how the syntax looks like …

DBMS_DEVELOPER.GET_METADATA (  
name            IN VARCHAR2,  
schema          IN VARCHAR2 DEFAULT NULL,  
object_type     IN VARCHAR2 DEFAULT NULL,  
level           IN VARCHAR2 DEFAULT 'TYPICAL'  
etag            IN RAW      DEFAULT NULL)
RETURN JSON;

How can you use it? Supported ‘object_type’ values are at the moment TABLE, INDEX, and VIEW.  The argument ‘level’ is used to provide the level of detail such as BASIC, TYPICAL, and ALL. The ‘etag’ is a unique identifier for the version of the document. This etag value lets an application determine whether the content of a particular version of a document is the same as that of another version (see the example below). The result is a JSON document. Related JSON schemas can be found in the documentation to describe the structure of the received JSON data. 

Let’s start with a simple example. Connect as the owner HR and return the JSON document for the table COUNTRIES and use the object name and the level BASIC in our first example.

Note: We used JSON_SERIALIZE with PRETTY to return a textual human readable representation of the JSON document.

set heading off long 10000
select JSON_SERIALIZE (DBMS_DEVELOPER.GET_METADATA(name => 'COUNTRIES', LEVEL => 'BASIC') pretty);

Executing this query produces the following JSON document:

{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "COUNTRIES",
    "schema" : "HR",
    "columns" :
    [
      {
        "name" : "COUNTRY_ID",
        "notNull" : true,
        "dataType" :
        {

          "type" : "CHAR",
          "length" : 2,
          "sizeUnits" : "BYTE"
        }
      },
      {
        "name" : "COUNTRY_NAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 60,
          "sizeUnits" : "BYTE"

        }
      },
      {
        "name" : "REGION_ID",
        "notNull" : false,
        "dataType" :
        {
          "type" : "NUMBER"
        }
      }
    ]
  },
  "etag" : "93D77AFB94E1F73B6A63C12687BD1CC1"
}

The resulting JSON document at level BASIC is based on the following JSON schema for the object type TABLE .

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/23.7/DBMS_DEVELOPER.GET_METADATA/BASIC/object_info/table",
    "title": "DBMS_DEVELOPER.GET_METADATA/BASIC/OBJECT_INFO/TABLE",
    "description": "Information for a table object",
    "type": "object",
    "properties": {
        "name": {
            "description": "Table name",
            "type": "string"
        },
        "schema": {
            "description": "Table schema",
            "type": "string"
        },
        "columns": {
            "description": "Table columns",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        }
    },
    "required": [
        "name",
        "schema",
        "columns"
    ]
}

To get an overview which level of information is provided in the JSON documents, refer to the documentation which lists the different JSON schemas in tabular form depending on the level and object type used:


Now let’s use a non-privileged user who has only connect and read permissions. In our example a privileged user creates a user DEVMETA with CONNECT and SELECT privilege on the table COUNTRIES for schema HR. 

SQL> create user devmeta identified by dev;
User created.

SQL>  grant connect to devmeta;
Grant succeeded.

SQL> grant select on hr.countries to devmeta;
Grant succeeded.

Connect as DEVMETA and retrieve information about the table COUNTRIES in the most comprehensive level (here ALL). 

connect devmeta/dev@<servicename>
Connected.

set heading off long 10000
select JSON_SERIALIZE (DBMS_DEVELOPER.GET_METADATA
                              (schema      => 'HR',
                               object_type => 'TABLE',
                               name        => 'COUNTRIES',
                               level       => 'ALL' )
                        returning clob pretty) result;

And this is an excerpt of the result. 

{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "schema" : "HR",
    "columns" :
    [
      {
        "dataType" :
        {
          "type" : "CHAR",
          "length" : 2,
          "sizeUnits" : "BYTE"
        },
        "isPk" : true,
        "hiddenColumn" : false,
        "numDistinct" : 25,
        "isUk" : true,
        "highValue" : "5A57",
        "isFk" : false,
        "lowValue" : "4152",
        "density" : 0.04,
        "notNull" : true,
        "name" : "COUNTRY_ID",
        "avgColLen" : 3
      },

 {
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 60,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "hiddenColumn" : false,
        "numDistinct" : 25,
        "isUk" : false,
        "highValue" : "5A696D6261627765",
        "isFk" : false,
        "lowValue" : "417267656E74696E61",
        "density" : 0.04,
        "notNull" : false,
        "name" : "COUNTRY_NAME",
        "avgColLen" : 11
      },
    ...  
    "avgRowLen" : 16,
    "name" : "COUNTRIES",
    "compression" : "DISABLED"
  },
  "etag" : "93D77AFB94E1F73B6A63C12687BD1CC1"
}

 

In our last example we investigate the usage of etags. The etag value in our example is “93D77AFB94E1F73B6A63C12687BD1CC1”. User HR adds the following annotation to the column COUNTRY_NAME of the table COUNTRY.

SQL> alter table hr.countries modify country_name annotations (add display 'NAME');
Table altered.

Connect to DEVMETA again and execute DBMS_DEVELOPER.GET_METADATA. 

set heading off long 10000
select json_serialize(DBMS_DEVELOPER.GET_METADATA
                        (schema      => 'HR', 
                         object_type => 'TABLE', 
                         name        => 'COUNTRIES') returning clob pretty) result;
{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "COUNTRIES",
    "schema" : "HR",
    "columns" :
    [
      {
        "name" : "COUNTRY_ID",
        "notNull" : true,
        "dataType" :
        {

          "type" : "CHAR",
          "length" : 2,
          "sizeUnits" : "BYTE"
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false
      },
      {
        "name" : "COUNTRY_NAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 60,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : false,
        "annotations" :
        [
          {
            "name" : "DISPLAY",
            "value" : "NAME"
          }
        ]
      },
...   },
  "etag" : "856A9E800B96DA2D2427CF4A6058FCBA"
}

The annotations field is added in the result and the etag value has changed to ‘856A9E800B96DA2D2427CF4A6058FCBA’. How can you use the etag value now? If the etag value in the statement matches the current most recent etag, the function will return an empty document. In all other cases you will receive the document with the recent etag.
In our next example the etag value matches, so we will receive an empty document.  

set long 10000 heading off
col result format a200
select json_serialize(DBMS_DEVELOPER.GET_METADATA
                       (schema      => 'HR', 
                        object_type => 'TABLE', 
                        name        => 'COUNTRIES', 
                        etag        => '856A9E800B96DA2D2427CF4A6058FCBA' ) returning clob pretty) result;

RESULT
--------------------------------------------------------------------------------
{
}

This demonstrates the functionality through simple examples. For further details and additional examples, please consult the documentation. 

Further Readings