62 DBMS_DEVELOPER

The package DBMS_DEVELOPER provides simple and developer friendly methods to retrieve information about database objects.

This chapter contains the following topics:

62.1 DBMS_DEVELOPER Overview

The package DBMS_DEVELOPER provides subprograms to retrieve information about database objects.

The DBMS_DEVELOPER package provides simple methods to retrieve relevant information about the database objects. This package is useful for developer tools and developers who would like to get information about the objects in the database, their shape, their structure, etc.

In terms of performance, the DBMS_DEVELOPER package retrieves information within few milliseconds.

62.2 DBMS_DEVELOPER Security

This section lists the security restrictions related to DBMS_DEVELOPER package.

The function is executed with invoker rights. You will be allowed to access metadata for a database object ONLY if you are authorized to do a "describe" on the object. If a you do not have such authorization, an error will be raised and no metadata will be retrieved.

Here's a summary of privileges required:

  • Tables and Views: You need SELECT or READ privileges on the table or view.
  • Indexes: You need SELECT or READ privileges on the table on which the index is defined.
  • Synonyms: You need SELECT or READ privileges on the synonym as well as the underlying object.

If you're describing objects in your own schema, no additional privilege is needed. However, if you're describing objects in another schema, you'll need at least the SELECT privilege on those objects.

If you are a DBA or have the SELECT ANY TABLE, READ ANY TABLE, and SELECT ANY VIEW system privilege, you can call DBMS_DEVELOPER.GET_METADATA on any table, index, or view in the database without needing explicit grants.

62.3 Summary of DBMS_DEVELOPER Subprograms

The table lists the DBMS_DEVELOPER subprograms and briefly describes them.

The following table specifies the subprograms for DBMS_DEVELOPER package and its brief description.

62.3.1 GET_METADATA Function

This section describes the syntax, input and output formats of the GET_METADATA function.

This function takes information about the object and produces the corresponding metadata as a CLOB containing JSON-formatted text output.

Syntax

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 CLOB;

Table 62-1 Input Configuration Fields

Field Value

name

The object name. A synonym of the object name can also be provided. It is case sensitive and should be provided as it appears in the data dictionary.

schema

The schema name. The default is the current user. It is case sensitive and should be provided as it appears in the data dictionary.

object_type

The type of object that you want to be retrieved. This is optional as name resolution can also be done without specifying object_type.

Supported values: TABLE, INDEX, and VIEW

level

The level of detail. The default is TYPICAL.

Supported values:

  1. BASIC : Describes essential information.
  2. TYPICAL : Includes more information of specified objects.
  3. ALL : Provides the most comprehensive level of information.

etag

A unique identifier for a specific 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.

  1. If an etag is provided and it matches with current etag, the function returns an empty document.
  2. If the etag of the document in the database does not match the etag that was provided, a new document is returned (along with the new embedded etag).
GET_METADATA function will not return a JSON schema, instead, it will return a JSON document. Depending on the level of metadata you would like to retrieve, you can use the appropriate parameters for GET_METADATA function to obtain the desired metadata JSON document.

Refer to JSON Schema for Different Object and Sub-Object Types to understand which schema you would need for your application.

Examples

This section provides example usage for DBMS_DEVELOPER.GET_METADATA for different values of object_type and level.
  1. Retrieving metadata for level set to BASIC and for object_type set to TABLE :
    SQL> conn hr/hr
    Connected.
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'EMPLOYEES', 
    level => 'BASIC')RETURNING CLOB PRETTY) from dual;

    Executing this query would produce the following:

    {
      "objectType" : "TABLE",
      "objectInfo" :
      {
        "name" : "EMPLOYEES",
        "schema" : "HR",
        "columns" :
        [
          {
            "name" : "EMPLOYEE_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "FIRST_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "LAST_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "EMAIL",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "PHONE_NUMBER",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "HIRE_DATE",
            "notNull" : true,
            "dataType" :
            {
              "type" : "DATE"
            }
          },
          {
            "name" : "JOB_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 10,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "SALARY",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 8,
              "scale" : 2
            }
          },
          {
            "name" : "COMMISSION_PCT",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 2,
              "scale" : 2
            }
          },
          {
            "name" : "MANAGER_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "DEPARTMENT_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            }
          }
        ]
      },
      "etag" : "C2BAA871FC47AF364A441F1DCBDBB9D8"
    }
    
  2. Retrieving metadata for level set to TYPICAL and for object_type set to VIEW :
    SQL> conn hr/hr
    Connected.
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'EMP_DETAILS_VIEW', 
    level => 'TYPICAL')RETURNING CLOB PRETTY) from dual;

    Executing this query would produce the following:

    {
      "objectType" : "VIEW",
      "objectInfo" :
      {
        "name" : "EMP_DETAILS_VIEW",
        "schema" : "HR",
        "columns" :
        [
          {
            "name" : "EMPLOYEE_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "JOB_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 10,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "MANAGER_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "DEPARTMENT_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            }
          },
          {
            "name" : "LOCATION_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            }
          },
          {
            "name" : "COUNTRY_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "CHAR",
              "length" : 2,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "FIRST_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "LAST_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "SALARY",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 8,
              "scale" : 2
            }
          },
          {
            "name" : "COMMISSION_PCT",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 2,
              "scale" : 2
            }
          },
          {
            "name" : "DEPARTMENT_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 30,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "JOB_TITLE",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 35,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "CITY",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 30,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "STATE_PROVINCE",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "COUNTRY_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 40,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "REGION_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          }
        ],
        "readOnly" : true,
        "constraints" :
        [
          {
            "name" : "SYS_C007578",
            "constraintType" : "VIEW READONLY",
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "NOT VALIDATED",
            "sysGeneratedName" : true
          }
        ]
      },
      "etag" : "C08C2ECBBE343A51060D8C475E16968E"
    }
    
  3. Retrieving metadata for level set to ALL and for object_type set to VIEW :
    SQL> conn hr/hr
    Connected.
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'EMP_DETAILS_VIEW', 
    level => 'ALL')RETURNING CLOB PRETTY) from dual;

    Executing this query would produce the following:

    {
      "objectType" : "VIEW",
      "objectInfo" :
      {
        "name" : "EMP_DETAILS_VIEW",
        "schema" : "HR",
        "columns" :
        [
          {
            "name" : "EMPLOYEE_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "JOB_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 10,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "MANAGER_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            }
          },
          {
            "name" : "DEPARTMENT_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            }
          },
          {
            "name" : "LOCATION_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            }
          },
          {
            "name" : "COUNTRY_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "CHAR",
              "length" : 2,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "FIRST_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "LAST_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "SALARY",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 8,
              "scale" : 2
            }
          },
          {
            "name" : "COMMISSION_PCT",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 2,
              "scale" : 2
            }
          },
          {
            "name" : "DEPARTMENT_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 30,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "JOB_TITLE",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 35,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "CITY",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 30,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "STATE_PROVINCE",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "COUNTRY_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 40,
              "sizeUnits" : "BYTE"
            }
          },
          {
            "name" : "REGION_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            }
          }
        ],
        "readOnly" : true,
        "constraints" :
        [
          {
            "name" : "SYS_C007578",
            "constraintType" : "VIEW READONLY",
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "NOT VALIDATED",
            "sysGeneratedName" : true
          }
        ],
        "editioningView" : false
      },
      "etag" : "ED648644C063A29B04E2A65CFC2D2E76"
    }
  4. Retrieving metadata for level set to ALL and for object_type set to INDEX :
    SQL> conn hr/hr
    Connected.
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'EMP_EMP_ID_PK', 
    level => 'ALL')RETURNING CLOB PRETTY) from dual;
    

    Executing this query would produce the following:

    {
      "objectType" : "INDEX",
      "objectInfo" :
      {
        "name" : "EMP_EMP_ID_PK",
        "indexType" : "NORMAL",
        "owner" : "HR",
        "tableName" : "EMPLOYEES",
        "status" : "VALID",
        "columns" :
        [
          {
            "name" : "EMPLOYEE_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            },
            "isPk" : true,
            "isUk" : true,
            "isFk" : false,
            "numDistinct" : 107,
            "density" : 0.00934579439252336,
            "avgColLen" : 4,
            "lowValue" : "C202",
            "highValue" : "C20307",
            "hiddenColumn" : false
          }
        ],
        "uniqueness" : "UNIQUE",
        "lastAnalyzed" : "2025-05-09T06:44:31",
        "numRows" : 107,
        "sampleSize" : 107,
        "partitioned" : false,
        "distinctKeys" : 107,
        "compression" : "DISABLED",
        "segmentCreated" : "YES",
        "visibility" : "VISIBLE",
        "toBeDropped" : false
      },
      "etag" : "752F0B7990BD188863BBBB80D5248F35"
    }
  5. Retrieving metadata where level and object_type are not explicitly specified:
    SQL> conn hr/hr
    Connected.
    SQL>
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'EMPLOYEES')
    RETURNING CLOB PRETTY) from dual;

    Even though the object_type is not specified, the name resolution happens under the hood. The name object_type corresponding to the name => 'EMPLOYEES' is determined to be table. So the JSON document is produced at the default typical level for the table object_type. The output JSON document corresponding to the above example is shown here :

    {
      "objectType" : "TABLE",
      "objectInfo" :
      {
        "name" : "EMPLOYEES",
        "schema" : "HR",
        "columns" :
        [
          {
            "name" : "EMPLOYEE_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            },
            "isPk" : true,
            "isUk" : true,
            "isFk" : false
          },
          {
            "name" : "FIRST_NAME",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "LAST_NAME",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "EMAIL",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 25,
              "sizeUnits" : "BYTE"
            },
            "isPk" : false,
            "isUk" : true,
            "isFk" : false
          },
          {
            "name" : "PHONE_NUMBER",
            "notNull" : false,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 20,
              "sizeUnits" : "BYTE"
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "HIRE_DATE",
            "notNull" : true,
            "dataType" :
            {
              "type" : "DATE"
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "JOB_ID",
            "notNull" : true,
            "dataType" :
            {
              "type" : "VARCHAR2",
              "length" : 10,
              "sizeUnits" : "BYTE"
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : true
          },
          {
            "name" : "SALARY",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 8,
              "scale" : 2
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "COMMISSION_PCT",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 2,
              "scale" : 2
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : false
          },
          {
            "name" : "MANAGER_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 6
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : true
          },
          {
            "name" : "DEPARTMENT_ID",
            "notNull" : false,
            "dataType" :
            {
              "type" : "NUMBER",
              "precision" : 4
            },
            "isPk" : false,
            "isUk" : false,
            "isFk" : true
          }
        ],
        "lastAnalyzed" : "2025-05-09T06:44:29",
        "numRows" : 107,
        "sampleSize" : 107,
        "avgRowLen" : 69,
        "external" : "NO",
        "temporary" : "NO",
        "indexes" :
        [
          {
            "name" : "EMP_EMAIL_UK",
            "indexType" : "NORMAL",
            "uniqueness" : "UNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:30",
            "numRows" : 107,
            "sampleSize" : 107,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "EMAIL"
              }
            ]
          },
          {
            "name" : "EMP_EMP_ID_PK",
            "indexType" : "NORMAL",
            "uniqueness" : "UNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:31",
            "numRows" : 107,
            "sampleSize" : 107,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "EMPLOYEE_ID"
              }
            ]
          },
          {
            "name" : "EMP_DEPARTMENT_IX",
            "indexType" : "NORMAL",
            "uniqueness" : "NONUNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:31",
            "numRows" : 106,
            "sampleSize" : 106,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "DEPARTMENT_ID"
              }
            ]
          },
          {
            "name" : "EMP_JOB_IX",
            "indexType" : "NORMAL",
            "uniqueness" : "NONUNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:32",
            "numRows" : 107,
            "sampleSize" : 107,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "JOB_ID"
              }
            ]
          },
          {
            "name" : "EMP_MANAGER_IX",
            "indexType" : "NORMAL",
            "uniqueness" : "NONUNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:32",
            "numRows" : 106,
            "sampleSize" : 106,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "MANAGER_ID"
              }
            ]
          },
          {
            "name" : "EMP_NAME_IX",
            "indexType" : "NORMAL",
            "uniqueness" : "NONUNIQUE",
            "status" : "VALID",
            "lastAnalyzed" : "2025-05-09T06:44:32",
            "numRows" : 107,
            "sampleSize" : 107,
            "partitioned" : false,
            "columns" :
            [
              {
                "name" : "LAST_NAME"
              },
              {
                "name" : "FIRST_NAME"
              }
            ]
          }
        ],
        "constraints" :
        [
          {
            "name" : "EMP_LAST_NAME_NN",
            "constraintType" : "CHECK - NOT NULL",
            "searchCondition" : "\"LAST_NAME\" IS NOT NULL",
            "columns" :
            [
              {
                "name" : "LAST_NAME"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_EMAIL_NN",
            "constraintType" : "CHECK - NOT NULL",
            "searchCondition" : "\"EMAIL\" IS NOT NULL",
            "columns" :
            [
              {
                "name" : "EMAIL"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_HIRE_DATE_NN",
            "constraintType" : "CHECK - NOT NULL",
            "searchCondition" : "\"HIRE_DATE\" IS NOT NULL",
            "columns" :
            [
              {
                "name" : "HIRE_DATE"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_JOB_NN",
            "constraintType" : "CHECK - NOT NULL",
            "searchCondition" : "\"JOB_ID\" IS NOT NULL",
            "columns" :
            [
              {
                "name" : "JOB_ID"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_SALARY_MIN",
            "constraintType" : "CHECK",
            "searchCondition" : "salary > 0",
            "columns" :
            [
              {
                "name" : "SALARY"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_EMAIL_UK",
            "constraintType" : "UNIQUE",
            "columns" :
            [
              {
                "name" : "EMAIL"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_EMP_ID_PK",
            "constraintType" : "PRIMARY KEY",
            "columns" :
            [
              {
                "name" : "EMPLOYEE_ID"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false
          },
          {
            "name" : "EMP_DEPT_FK",
            "constraintType" : "REFERENTIAL INTEGRITY",
            "columns" :
            [
              {
                "name" : "DEPARTMENT_ID"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false,
            "referencedConstraintName" : "DEPT_ID_PK",
            "referencedTable" : "DEPARTMENTS",
            "referencedOwner" : "HR",
            "Action" : "RESTRICT",
            "referencedColumns" :
            [
              {
                "name" : "DEPARTMENT_ID"
              }
            ]
          },
          {
            "name" : "EMP_JOB_FK",
            "constraintType" : "REFERENTIAL INTEGRITY",
            "columns" :
            [
              {
                "name" : "JOB_ID"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false,
            "referencedConstraintName" : "JOB_ID_PK",
            "referencedTable" : "JOBS",
            "referencedOwner" : "HR",
            "Action" : "RESTRICT",
            "referencedColumns" :
            [
              {
                "name" : "JOB_ID"
              }
            ]
          },
          {
            "name" : "EMP_MANAGER_FK",
            "constraintType" : "REFERENTIAL INTEGRITY",
            "columns" :
            [
              {
                "name" : "MANAGER_ID"
              }
            ],
            "status" : "ENABLED",
            "deferrable" : false,
            "validated" : "VALIDATED",
            "sysGeneratedName" : false,
            "referencedConstraintName" : "EMP_EMP_ID_PK",
            "referencedTable" : "EMPLOYEES",
            "referencedOwner" : "HR",
            "Action" : "RESTRICT",
            "referencedColumns" :
            [
              {
                "name" : "EMPLOYEE_ID"
              }
            ]
          }
        ]
      },
      "etag" : "250A14E4E687854657E7336BCE46432F"
    }
  6. Retrieving metadata when the specified etag matches the current etag would return an empty document :
    SQL> conn hr/hr
    Connected.
    SQL>
    SQL> SELECT JSON_SERIALIZE(dbms_developer.get_metadata(name => 'REGIONS', 
    etag => '9ADC6D08AE0D5C82C0188D53F3ECD5B9')RETURNING CLOB PRETTY) from dual;
    Executing this query would produce an empty JSON document :
    {
    }
62.3.1.1 JSON Schema for Different Object and Sub-Object Types

This section provides an overview to the different schema possible for various object types, sub-object types and levels.

The JSON Schema can be used as a reference by applications to parse the JSON document returned. If you are programming against a specific version of the database program, you use that specific version of the JSON schema for that database version, and that schema will always be the same. But, if you upgrade or downgrade to a different release, you will be using a different schema version and then you need to use that correct schema. If there is any change in the schema, the version number in the $id field will be changed. Applications can rely on this number in case Oracle plans to revise the JSON schema in the future. Existing versions will remain unchanged, so applications will remain fully functional and unaffected by the updates.

62.3.1.1.1 JSON Schema for Object Type : TABLE

This section describes the JSON schema for the object type TABLE at all levels.

JSON Schema for TABLE Object Type in Tabular Form

Table 62-2 JSON schema for object type : TABLE

FIELDS     LEVELS
      BASIC TYPICAL ALL

name

   

schema

   

columns

   

hasBeenAnalyzed

     

lastAnalyzed

     

numRows

     

sampleSize

     

avgRowLen

     

clusterName

     

clusterOwner

     

external

     

temporary

     

indexes

     

constraints

     

segmentCreated

       

inMemory

       

compression

       

dependencies

       

authorizations

grantee      
privileges name
grantable

inMemoryPriority

       

inMemoryDistribute

       

inMemoryCompression

       

inMemoryDuplicate

       

iotType

       

Note:

The fields of the sub-objects columns and constraints varies depending on the choice of the level. The detailed information of the fields present in the sub-object columns is described in the section : JSON Schema for Sub-Object Type : columns. The detailed information of the fields present in the sub-object constraints is described in the section : JSON Schema for Sub-Object Type : constraints.

JSON Schema for TABLE object type at level BASIC

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/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"
    ]
}

JSON Schema for TABLE object type at level TYPICAL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/TYPICAL/object_info/table",
    "title": "DBMS_DEVELOPER.GET_METADATA/TYPICAL/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"
            }
        },
        "hasBeenAnalyzed": {
            "description": "Indicates whether table has been analyzed",
            "type": "boolean",
            "default": false
        },
        "lastAnalyzed": {
            "description": "Last time table was analyzed",
            "type": "string",
            "format": "date-time"
        },
        "numRows": {
            "description": "Number of rows",
            "type": "integer"
        },
        "sampleSize": {
            "description": "Analyzed sample size",
            "type": "integer"
        },
        "avgRowLen": {
            "description": "Average row length",
            "type": "integer"
        },
        "clusterName": {
            "description": "Name of the cluster, if any, to which the table belongs",
            "type": "string"
        },
        "clusterOwner": {
            "description": "Owner of the cluster, if any, to which the table belongs",
            "type": "string"
        },
        "external": {
            "description": "Indicates whether the table is an external table (YES) or not (NO)",
            "type": "string"
        },
        "temporary": {
            "description": "Indicates whether the table is temporary (YES) or not (NO)",
            "type": "string"
        },
        "indexes": {
            "description": "Table indexes",
            "type": "array",
            "items": {
                "$ref": "../index"
            }
        },
        "constraints": {
            "description": "Table constraints",
            "type": "array",
            "items": {
                "$ref": "../constraint"
            }
        }
    },
    "required": [
        "name",
        "schema",
        "columns",
        "external",
        "temporary"
    ]
}

JSON Schema for TABLE object type at level ALL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/ALL/object_info/table",
    "title": "DBMS_DEVELOPER.GET_METADATA/ALL/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"
            }
        },
        "hasBeenAnalyzed": {
            "description": "Indicates whether table has been analyzed",
            "type": "boolean",
            "default": false
        },
        "lastAnalyzed": {
            "description": "Last time table was analyzed",
            "type": "string",
            "format": "date-time"
        },
        "numRows": {
            "description": "Number of rows",
            "type": "integer"
        },
        "sampleSize": {
            "description": "Analyzed sample size",
            "type": "integer"
        },
        "avgRowLen": {
            "description": "Average row length",
            "type": "integer"
        },
        "clusterName": {
            "description": "Name of the cluster, if any, to which the table belongs",
            "type": "string"
        },
        "clusterOwner": {
            "description": "Owner of the cluster, if any, to which the table belongs",
            "type": "string"
        },
        "external": {
            "description": "Indicates whether the table is an external table (YES) or not (NO)",
            "type": "string"
        },
        "temporary": {
            "description": "Indicates whether the table is temporary (YES) or not (NO)",
            "type": "string"
        },
        "indexes": {
            "description": "Table indexes",
            "type": "array",
            "items": {
                "$ref": "../index"
            }
        },
        "constraints": {
            "description": "Table constraints",
            "type": "array",
            "items": {
                "$ref": "../constraint"
            }
        },
        "segmentCreated": {
            "description": "Indicates whether the table segment is created.",
            "type": "string"
        },  
        "inMemory": {
            "description": "Indicates whether the In-Memory Column Store (IM column store) is 
                            enabled (ENABLED) or disabled (DISABLED) for the table",
            "type": "string"
        }, 
        "compression": {
            "description": "Indicates whether table compression is enabled (ENABLED) or not (DISABLED)",
            "type": "string"
        }, 
        "dependencies": {
            "description": "Indicates whether the table is an external table (YES) or not (NO)",
            "type": "string"
        },
         "authorizations": {
            "description": "Table authorizations",
            "type": "object",
            "properties": {
                "grantee": {
                    "description": "Name of the user to whom access was granted",
                    "type": "string"  
                },
                "privileges": {
                    "description": "All privileges granted to grantee",
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "name": {
                                "description": "Privilege on the object,
                                "type": "string"
                            },
                            "grantable": {
                                "description": "Indicates whether the privilege was granted with the GRANT OPTION",
                                "type": "string"
                            }
                        }
                    }
                }
            }      
        }, 
        "inMemoryPriority": {
            "description": "Indicates the priority for In-Memory Column Store (IM column store) population. ",
            "type": "string"
        }, 
        "inMemoryDistribute": {
            "description": "Indicates how the IM column store is distributed in an 
                            Oracle Real Application Clusters (Oracle RAC) environment",
            "type": "string"
        }, 
        "inMemoryCompression": {
            "description": "Indicates the compression level for the IM column store",
            "type": "string"
        }, 
        "inMemoryDuplicate": {
            "description": "Indicates the duplicate setting for the IM column store in an Oracle RAC environment",
            "type": "string"
        }, 
        "iotType": {
            "description": "If the table is an index-organized table, then IOT_TYPE is IOT, 
                            IOT_OVERFLOW, or IOT_MAPPING. If the table is not an 
                            index-organized table, then IOT_TYPE is NULL.",
            "type": "string"
        }
    },
    "required": [
        "name",
        "schema",
        "columns",
        "external",
        "temporary",
        "inMemory",
        "compression",
        "dependencies",
        "segmentCreated"
    ]
}
62.3.1.1.2 JSON Schema for Object Type : VIEW

This section describes the JSON schema for the object type VIEW at all levels.

JSON Schema for VIEW Object Type in Tabular Form

Table 62-3 JSON Schema for VIEW Object Type

Fields     LEVELS
      BASIC TYPICAL ALL

name

   

schema

   

columns

   

readOnly

     

constraints

     

editioningView

       

authorizations

grantee

     

privileges

name
grantable

Note:

The fields of the sub-objects columns and constraints varies depending on the choice of the level. The detailed information of the fields present in the sub-object columns is described in the section : JSON Schema for Sub-Object Type : columns. The detailed information of the fields present in the sub-object constraints is described in the section : JSON Schema for Sub-Object Type : constraints.

JSON Schema for VIEW object type at level BASIC

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

JSON Schema for VIEW object type at level TYPICAL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/TYPICAL/object_info/view",
    "title": "DBMS_DEVELOPER.GET_METADATA/TYPICAL/OBJECT_INFO/VIEW",
    "description": "Information for a View object",
    "type": "object",
    "properties": {
        "name": {
            "description": "View name",
            "type": "string"
        },
        "schema": {
            "description": "View schema",
            "type": "string"
        },
        "columns": {
            "description": "View columns",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        },
        "readOnly": {
            "description": "An indicator of whether the view is a Read Only View",
            "type": "boolean",
            "default": true
        },
        "constraints": {
            "description": "View constraints",
            "type": "array",
            "items": {
                "$ref": "../constraint"
            }
        }
    },
    "required": [
         "name",
         "schema",
         "readOnly",
         "columns"
    ]
}

JSON Schema for VIEW object type at level ALL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/ALL/object_info/view",
    "title": "DBMS_DEVELOPER.GET_METADATA/ALL/OBJECT_INFO/VIEW",
    "description": "Information for a View object",
    "type": "object",
    "properties": {
        "name": {
            "description": "View name",
            "type": "string"
        },
        "schema": {
            "description": "View schema",
            "type": "string"
        }, 
        "columns": {
            "description": "View columns",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        },
        "readOnly": {
            "description": "An indicator of whether the view is a Read Only View",
            "type": "boolean",
            "default": true
        }, 
        "constraints": {
            "description": "View constraints",
            "type": "array",
            "items": {
                "$ref": "../constraint"
            }
        },,
        "editioningView": {
            "description": "An indicator of whether the view is an Editioning view",
            "type": "boolean",
            "default": false
        },
        "authorizations": {
            "description": "View authorizations",
            "type": "object",
            "properties": {
                "grantee": {
                    "description": "Name of the user to whom access was granted",
                    "type": "string"  
                },
                "privileges": {
                    "description": "All privileges granted to grantee",
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "name": {
                                "description": "Privilege on the object,
                                "type": "string"
                            },
                            "grantable": {
                                "description": "Indicates whether the privilege was granted with the GRANT OPTION",
                                "type": "string"
                            }
                        }
                    }
                }
            }      
        }
    },
    "required": [
         "name",
         "schema",
         "columns"
    ]
}
62.3.1.1.3 JSON Schema for Object Type : INDEX

This section describes the JSON schema for the object type INDEX at all levels.

JSON Schema for INDEX Object Type in Tabular Form

Table 62-4 JSON Schema for INDEX Object Type

Fields     LEVELS
      BASIC TYPICAL ALL
name    
indexType    
owner    
tableName    
status    
columns    
uniqueness      
funcIdxStatus      
hasBeenAnalyzed      
lastAnalyzed      
numRows      
sampleSize      
partitioned      
distinctKeys        
compression        
segmentCreated        
visibility        
toBeDropped        

Note:

The fields of the sub-object columns varies depending on the choice of level. The detailed information of the fields present in the sub-object columns is described in the section : JSON Schema for Sub-Object Type : columns.

JSON Schema for INDEX object type at level BASIC

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/BASIC/object_info/index",
    "title": "DBMS_DEVELOPER.GET_METADATA/BASIC/OBJECT_INFO/INDEX",
    "description": "Information for index",
    "type": "object",
    "properties": {
        "name": {
            "description": "Index name",
            "type": "string"
        },
        "indexType": {
            "description": "Index Types i.e. CLUSTER, NORMAL, NORMAL/REV, BITMAP, DOMAIN, 
                            LOB, IOT - TOP, FUNCTION-BASED NORMAL, FUNCTION-BASED NORMAL/REV, 
                            FUNCTION-BASED BITMAP, FUNCTION-BASED DOMAIN, VECTOR",
            "type": "string"
        },
        "owner": {
            "description": "Owner of the indexed object",
            "type": "string"
        },
        "tableName": {
            "description": "Name of the indexed object",
            "type": "string"
        },
        "status": {
            "description": "Status",
            "type": {
                "enum": [
                    "VALID",
                    "UNUSABLE",
                    "INPROGRS",
                    "N/A"
                ]
            }
        },
        "columns": {
            "description": "Index column(s)",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        }
    },
    "required": [
        "name",
        "indexType",
        "owner",
        "tableName",
        "status",
        "columns"
    ]
}

JSON Schema for INDEX object type at level TYPICAL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/TYPICAL/object_info/index",
    "title": "DBMS_DEVELOPER.GET_METADATA/TYPICAL/OBJECT_INFO/INDEX",
    "description": "Information for index",
    "type": "object",
    "properties": {
        "name": {
            "description": "Index name",
            "type": "string"
        },
        "indexType": {
            "description": "Index Types i.e. CLUSTER, NORMAL, NORMAL/REV, BITMAP, DOMAIN, 
                            LOB, IOT - TOP, FUNCTION-BASED NORMAL, FUNCTION-BASED NORMAL/REV, 
                            FUNCTION-BASED BITMAP, FUNCTION-BASED DOMAIN, VECTOR",
            "type": "string"
        },
        "owner": {
            "description": "Owner of the indexed object",
            "type": "string"
        },
        "tableName": {
            "description": "Name of the indexed object",
            "type": "string"
        },  
        "status": {
            "description": "Status",
            "type": {
                "enum": [
                    "VALID",
                    "INVALID",
                    "INPROGRS",
                    "N/A"
                ]
            }
        },
        "columns": {
            "description": "Index column(s)",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        },
        "uniqueness": {
            "description": "Uniqueness",
            "type": "string",
            "enum": [
                "UNIQUE",
                "NONUNIQUE"
            ]
        },
        "funcIdxStatus": {
            "description": "Functional index status",
            "type": "string"
        },
        "hasBeenAnalyzed": {
            "description": "Indicates whether index has been analyzed",
            "type": "boolean",
            "default": false
        },
        "lastAnalyzed": {
            "description": "Last time View was analyzed",
            "type": "string",
            "format": "date-time"
        },
        "numRows": {
            "description": "Number of rows",
            "type": "integer"
        },
        "sampleSize": {
            "description": "Analyzed sample size",
            "type": "integer"
        },
        "partitioned": {
            "description": "Indicates whether the index is partitioned",
            "type": "boolean",
            "default": false
        }
    },
    "required": [
        "name",
        "indexType",
        "owner",
        "tableName",
        "status",
        "columns"
    ]
}

JSON Schema for INDEX object type at level ALL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/ALL/object_info/index",
    "title": "DBMS_DEVELOPER.GET_METADATA/ALL/OBJECT_INFO/INDEX",
    "description": "Information for index",
    "type": "object",
    "properties": {
        "name": {
            "description": "Index name",
            "type": "string"
        },
        "indexType": {
            "description": "Index Types i.e. CLUSTER, NORMAL, NORMAL/REV, BITMAP, DOMAIN, 
                            LOB, IOT - TOP, FUNCTION-BASED NORMAL, FUNCTION-BASED NORMAL/REV, 
                            FUNCTION-BASED BITMAP, FUNCTION-BASED DOMAIN, VECTOR",
            "type": "string"
        },
        "owner": {
            "description": "Owner of the indexed object",
            "type": "string"
        },
        "tableName": {
            "description": "Name of the indexed object",
            "type": "string"
        },
        "status": {
            "description": "Status",
            "type": {
                 "enum": [
                    "VALID",
                    "INVALID",
                    "INPROGRS",
                    "N/A"
                ]
            }
        },
        "columns": {
            "description": "Index column(s)",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        },
        "uniqueness": {
            "description": "Uniqueness",
            "type": "string",
            "enum": [
                "UNIQUE",
                "NONUNIQUE"
            ]
        },
        "funcIdxStatus": {
            "description": "Functional index status",
            "type": "string"
        },
        "hasBeenAnalyzed": {
            "description": "Indicates whether index has been analyzed",
            "type": "boolean",
            "default": false
        }, 
        "lastAnalyzed": {
            "description": "Last time View was analyzed",
            "type": "string",
            "format": "date-time"
        },
        "numRows": {
            "description": "Number of rows",
            "type": "integer"
        },
        "sampleSize": {
            "description": "Analyzed sample size",
            "type": "integer"
        },
        "partitioned": {
            "description": "Indicates whether the index is partitioned",
            "type": "boolean",
            "default": false
        },  
        "distinctKeys": {
            "description": "Number of distinct keys in the index",
            "type": "integer"
        },
        "compression": {
            "description": "Compression property of the index: ENABLED, DISABLED, ADVANCED HIGH, ADVANCED LOW",
            "type": "string",
            "enum": [
                "ENABLED",
                "DISABLED",
                "ADVANCED HIGH",
                "ADVANCED LOW"
            ]
        },
        "segmentCreated": {
            "description": "Whether the index segment has been created",
            "type": "string",
            "enum": [
                "YES",
                "NO",
                "N/A"
            ]
        },
        "visibility": {
            "description": "Whether the index is VISIBLE or INVISIBLE to the optimizer",
            "type": "string",
            "enum": [
                "VISIBLE",
                "INVISIBLE"
            ]
        },
        "toBeDropped": {
            "description": "Whether index is dropped and is in Recycle Bin",
            "type": "boolean"
        }
    },
    "required": [
         "name",
         "indexType",
         "owner",
         "tableName",
         "status",
         "columns",
         "partitioned"
         "compression",
         "segmentCreated",
         "visibility",
         "tobeDropped"
   ]
}
62.3.1.1.4 JSON Schema for Sub-Object Type : columns

JSON schema for the sub-object columns for the levels: basic, typical and all.

JSON Schema for columns Sub-Object Type in Tabular Form

Table 62-5 JSON Schema for COLUMNS Sub-Object

Fields   Levels
    BASIC TYPICAL ALL
name  
default  
notNull  
dataType type
precision
length
scale
size
sizeUnits
fractionalSecondsPrecision
yearPrecision
dayPrecision
isPk    
isUk    
isFk    
numDistinct      
lowValue      
highValue      
density      
avgColLen      
hiddenColumn      
virtualColumn      
identityColumn      
encryptedColumn      

Note:

Certain fields are required based on the value of the type field. The following table lists the required fields for each type value.

JSON Schema for COLUMN sub-object type at level BASIC

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/BASIC/object_info/column",
    "title": "DBMS_DEVELOPER.GET_METADATA/BASIC/OBJECT_INFO/COLUMN",
    "description": "Information for column",
    "type": "object",
    "properties": {
        "name": {
            "description": "Column name",
            "type": "string"
        },
        "notNull": {
            "description": "Not null",
            "type": "boolean",
            "default": false
        },
        "default": {
            "description": "Default value",
            "anyOf": [
                {
                    "type": "string"
                },
                {
                    "type": "number"
                }
            ]
        },
        "dataType": {
            "description": "Datatype",
            "type": "object",
            "properties": {
                "type": {
                    "description": "dataType",
                    "type": "string"
                },
                "precision": {
                    "description": "precision",
                    "type": "integer",
                    "minimum": 1
                },
                "scale": {
                    "description": "scale",
                    "type": "integer",
                    "minimum": 0
                },
                "length": {
                    "description": "length",
                    "type": "integer",
                    "minimum": 0
                }, 
                "size": {
                    "description": "size",
                    "type": "integer",
                    "minimum": 1
                },
                "sizeUnits": {
                    "description": "varchar size units",
                    "type": "string",
                    "enum": [
                        "BYTE",
                        "CHAR"
                    ]
                },
                "fractionalSecondsPrecision": {
                    "description": "fractionalSecondsPrecision",
                    "type": "integer"
                },
                "yearPrecision": {
                    "description": "yearPrecision",
                    "type": "integer"
                },
                "dayPrecision": {
                    "description": "dayPrecision",
                    "type": "integer"
                }
            },
            "anyOf": [
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NUMBER"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision",
                            "scale"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "FLOAT"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "VARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "RAW"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "CHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NCHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "UROWID"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NVARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "enum": [
                                    "TIME",
                                    "TIME WITH TIMEZONE",
                                    "TIMESTAMP",
                                    "TIMESTAMP WITH TIME ZONE",
                                    "TIMESTAMP WITH LOCAL TIME ZONE"
                                ]
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "fractionalSecondsPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL YEAR TO MONTH"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "yearPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL DAY TO SECOND"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "dayPrecision",
                            "fractionalSecondsPrecision"
                        ]
                    }
                }
            ]
        }
    },
    "required": [
        "name",
        "dataType",
        "notNull"
    ]
}

JSON Schema for COLUMN sub-object type at level TYPICAL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/TYPICAL/object_info/column",
    "title": "DBMS_DEVELOPER.GET_METADATA/TYPICAL/OBJECT_INFO/COLUMN",
    "description": "Information for column",
    "type": "object",
    "properties": {
        "name": {
            "description": "Column name",
            "type": "string"
        },
        "default": {
            "description": "Default value",
            "anyOf": [
                {
                    "type": "string"
                },
                {
                    "type": "number"
                }
            ]
        },
        "notNull": {
            "description": "Not null",
            "type": "boolean",
            "default": false
        },
        "dataType": {
            "description": "Datatype",
            "type": "object",
            "properties": {
                "type": {
                    "description": "dataType",
                    "type": "string"
                },
                "precision": {
                    "description": "precision",
                    "type": "integer",
                    "minimum": 1
                },
                "scale": {
                    "description": "scale",
                    "type": "integer",
                    "minimum": 0
                },
                "length": {
                    "description": "length",
                    "type": "integer",
                    "minimum": 0
                }, 
                "size": {
                    "description": "size",
                    "type": "integer",
                    "minimum": 1
                },
                "sizeUnits": {
                    "description": "varchar size units",
                    "type": "string",
                    "enum": [
                        "BYTE",
                        "CHAR"
                    ]
                },
                "fractionalSecondsPrecision": {
                    "description": "fractionalSecondsPrecision",
                    "type": "integer"
                },
                "yearPrecision": {
                    "description": "yearPrecision",
                    "type": "integer"
                },
                "dayPrecision": {
                    "description": "dayPrecision",
                    "type": "integer"
                }
            },
            "anyOf": [
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NUMBER"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision",
                            "scale"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "FLOAT"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "VARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "RAW"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "CHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NCHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "UROWID"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NVARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "enum": [
                                    "TIME",
                                    "TIME WITH TIMEZONE",
                                    "TIMESTAMP",
                                    "TIMESTAMP WITH TIME ZONE",
                                    "TIMESTAMP WITH LOCAL TIME ZONE"
                                ]
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "fractionalSecondsPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL YEAR TO MONTH"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "yearPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL DAY TO SECOND"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "dayPrecision",
                            "fractionalSecondsPrecision"
                        ]
                    }
                }
            ]
        },
        "isPk": {
            "description": "Is Primary Key",
            "type": "boolean",
            "default": false
        },
        "isUk": {
            "description": "Is Unique Key",
            "type": "boolean",
            "default": false
        },
        "isFk": {
            "description": "Is Foreign Key",
            "type": "boolean",
            "default": false
        }
    },
    "required": [
        "name",
        "isPk",
        "isUk",
        "isFk",    
        "dataType",
        "notNull"
    ]
}

JSON Schema for COLUMN sub-object type at level ALL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/19.28/DBMS_DEVELOPER.GET_METADATA/ALL/object_info/column",
    "title": "DBMS_DEVELOPER.GET_METADATA/ALL/OBJECT_INFO/COLUMN",
    "description": "Information for column",
    "type": "object",
    "properties": {
        "name": {
            "description": "Column name",
            "type": "string"
        },
        "default": {
            "description": "Default value",
            "anyOf": [
                {
                    "type": "string"
                },
                {
                    "type": "number"
                }
            ]
        },
        "notNull": {
            "description": "Not null",
            "type": "boolean",
            "default": false
        },
        "dataType": {
            "description": "Datatype",
            "type": "object",
            "properties": {
                "type": {
                    "description": "dataType",
                    "type": "string"
                },
                "precision": {
                    "description": "precision",
                    "type": "integer",
                    "minimum": 1
                },
                "scale": {
                    "description": "scale",
                    "type": "integer",
                    "minimum": 0
                },
                "length": {
                    "description": "length",
                    "type": "integer",
                    "minimum": 0
                },
                "size": {
                    "description": "size",
                    "type": "integer",
                    "minimum": 1
                },
                "sizeUnits": {
                    "description": "varchar size units",
                    "type": "string",
                    "enum": [
                        "BYTE",
                        "CHAR"
                    ]
                },
                "fractionalSecondsPrecision": {
                    "description": "fractionalSecondsPrecision",
                    "type": "integer"
                },
                "yearPrecision": {
                    "description": "yearPrecision",
                    "type": "integer"
                },
                "dayPrecision": {
                    "description": "dayPrecision",
                    "type": "integer"
                }
            },
            "anyOf": [
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NUMBER"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision",
                            "scale"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "FLOAT"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "precision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "VARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "RAW"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "CHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length",
                            "sizeUnits"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NCHAR"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "UROWID"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "size"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "NVARCHAR2"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "length"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "enum": [
                                    "TIME",
                                    "TIME WITH TIMEZONE",
                                    "TIMESTAMP",
                                    "TIMESTAMP WITH TIME ZONE",
                                    "TIMESTAMP WITH LOCAL TIME ZONE"
                                ]
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "fractionalSecondsPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL YEAR TO MONTH"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "yearPrecision"
                        ]
                    }
                },
                {
                    "if": {
                        "properties": {
                            "type": {
                                "const": "INTERVAL DAY TO SECOND"
                            }
                        }
                    },
                    "then": {
                        "required": [
                            "dayPrecision",
                            "fractionalSecondsPrecision"
                        ]
                    }
                }
            ]
        },
        "isPk": {
            "description": "Is Primary Key",
            "type": "boolean",
            "default": false
        },
        "isUk": {
            "description": "Is Unique Key",
            "type": "boolean",
            "default": false
        },
        "isFk": {
            "description": "Is Foreign Key",
            "type": "boolean",
            "default": false
        },
        "numDistinct": {
            "description": "The number of distinct values in the column",
            "type": "integer"
        },
        "lowValue": {
            "description": "The low value in the column",
            "type": "string"
        },
        "highValue": {
            "description": "The high value in the column",
            "type": "string"
        },
        "density": {
            "description": "The average length of the column in bytes",
            "type": "integer"
        },
        "avgColLen": {
            "description": "The number of distinct values in the column",
            "type": "integer"
        },
        "hiddenColumn": {
            "description": "Indicates whether the column is a hidden column",
            "type": "boolean"
        },
        "virtualColumn": {
            "description": "Indicates whether the column is a virtual column",
            "type": "boolean"
        }
        "identityColumn": {
            "description": "Indicates whether the column is a identity column",
            "type": "boolean"
        },
        "encryptedColumn": {
            "description": "Indicates whether the column is a encrypted column",
            "type": "boolean"
        }
    },
    "required": [
         "name",
         "isPk",
         "isUk",
         "isFk",    
         "dataType",
         "notNull",
         "hiddenColumn",
         "virtualColumn",
         "identityColumn",
         "encryptedColumn"
    ]
}
62.3.1.1.5 JSON Schema for Sub-Object Type : constraints

JSON schema for the sub-object constraints for the levels: typical and all.

JSON Schema for constraints Sub-Object Type in Tabular Form

Table 62-6 JSON Schema for Sub-Object Type : CONSTRAINTS

Fields        
    BASIC TYPICAL ALL
name    
constraintType    
searchCondition    
columns name  
referencedConstraintName    
referencedTable    
action    
referencedOwner    
referencedColumns name  
status    
deferrable    
validated    
sysGeneratedName    

Note:

If the constraintType is set to REFERENTIAL INTEGRITY, the following fields are required in the schema: referencedConstraintName, referencedOwner, referencedTable, and referencedColumns.

JSON Schema for CONSTRAINT sub-object type at level TYPICAL

Note:

Constraint schema is not available for the BASIC level.
{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/23.10/DBMS_DEVELOPER.GET_METADATA/TYPICAL/object_info/constraint",
    "title": "DBMS_DEVELOPER.GET_METADATA/TYPICAL/OBJECT_INFO/CONSTRAINT",
    "description": "Information for constraint",
    "type": "object",
    "properties": {
        "name": {
            "description": "Constraint name",
            "type": "string"
        },
        "constraintType": {
            "description": "Type of constraint definition: CHECK, CHECK - NOT NULL, PRIMARY KEY, UNIQUE, 
                            REFERENTIAL INTEGRITY, VIEW CHECK, VIEW READONLY",
            "type": "string"
        },
        "searchCondition": {
            "description": "Text of search condition for a check constraint",
            "type": "string"
        },
        "columns": {
            "description": "Table columns",
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "name": {
                        "description": "Name associated with column or attribute of object column 
                                        specified in the constraint definition",
                        "type": "string"
                    }
                }
            }
        },
        "referencedConstraintName": {
            "description": "Name of unique constraint definition for Referenced table",
            "type": "string"
        },
        "referencedTable": {
            "description": "Name of table used in referential constraint",
            "type": "string"
        },
        "action": {
            "description": "On delete rule",
            "type": {
                "enum": [
                    "RESTRICT",
                    "CASCADE",
                    "SET NULL",
                    "SET DEFAULT"
                ]
            }
        }, 
        "referencedOwner": {
            "description": "Owner of table used in referential constraint",
            "type": "string"
        },
        "referencedColumns": {
            "description": "Referenced table columns",
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "name": {
                        "description": "Referenced table column name",
                        "type": "string"
                    }
                }
            }
        },
        "status": {
            "description": "Constraint status",
            "type": {
                "enum": [
                    "ENABLED",
                    "DISABLED"
                ]
            }
        },
        "deferrable": {
            "description": "Deferrable",
            "type": "boolean",
            "default": false
        },
        "validated": {
            "description": "Validated",
            "type": {
                "enum": [
                    "VALIDATED",
                    "NOT VALIDATED"
                ]
            }
        },
        "sysGeneratedName": {
            "description": "System Generated constraint",
            "type": "boolean"
        }
    },
    "required": [
        "name",
        "constraintType",
        "status",
        "deferrable",
        "validated",
        "sysGeneratedName"
    ],
    "if": {
        "properties": {
            "constraintType": {
                "const": "REFERENTIAL INTEGRITY"
             }
         }
    },
    "then": {
        "required": [
            "referencedConstraintName",
             "referencedOwner",
             "referencedTable",
             "referencedColumns"
         ]
    }
}

JSON Schema for CONSTRAINT sub-object type at level ALL

{
    "$schema": "https://json-schema.org/draft/2020-12/schema",
    "$id": "https://oracle.com/schema/23.10/DBMS_DEVELOPER.GET_METADATA/ALL/object_info/constraint",
    "title": "DBMS_DEVELOPER.GET_METADATA/ALL/OBJECT_INFO/CONSTRAINT",
    "description": "Information for constraint",
    "type": "object",
     "properties": {
        "name": {
            "description": "Constraint name",
            "type": "string"
        },
        "constraintType": {
            "description": "Type of constraint definition",
            "type": "string"
        },
        "searchCondition": {
            "description": "Text of search condition for a check constraint",
            "type": "string"
        },
        "columns": {
            "description": "Table columns",
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "name": {
                        "description": "Name associated with column or attribute of object column 
                                        specified in the constraint definition",
                        "type": "string"
                    },
                    "position": {
                        "description": "Original position of column or attribute in definition",
                        "type": "integer"
                    }
                }
            }
        },
        "referencedConstraintName": {
            "description": "Name of unique constraint definition for Referenced table",
            "type": "string"
        },
        "referencedTable": {
            "description": "Name of table used in referential constraint",
            "type": "string"
        },
        "action": {
            "description": "On delete rule",
            "type": {
                "enum": [
                     "RESTRICT",
                     "CASCADE",
                     "SET NULL",
                     "SET DEFAULT"
                ]
            }
        },
        "referencedOwner": {
            "description": "Owner of table used in referential constraint",
            "type": "string"
        },
        "referencedColumns": {
            "description": "Referenced table columns",
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "name": {
                        "description": "Referenced table column name",
                        "type": "string"
                    },
                    "position": {
                        "description": "Original position of column or attribute in definition",
                        "type": "integer"
                    }
                }
            }
        },
        "status": {
            "description": "Constraint status",
            "type": {
                "enum": [
                    "ENABLED",
                    "DISABLED"
                ]
            }
        },
        "deferrable": {
            "description": "Deferrable",
            "type": "boolean",
            "default": false
        },
        "validated": {
            "description": "Validated",
            "type": {
                "enum": [
                    "VALIDATED",
                    "NONVALIDATED"
                ]
            }
        },
        "sysGeneratedName": {
            "description": "System Generated constraint",
            "type": "boolean"
        }
    },
    "required": [
         "name",
         "constraintType"
    ],
    "if": {
         "properties": {
              "constraintType": {
                    "const": "REFERENTIAL INTEGRITY"
               }
          }
    },
    "then": {
         "required": [
              "referencedConstraintName",
              "referencedOwner",
              "referencedTable",
              "referencedColumns"
         ]
    }
}