DBA Hub

📋Steps in this guide1/6

DBMS_DEVELOPER Package - Database Object Metadata in JSON Format

The DBMS_DEVELOPER package was introduced in Oracle database 23a/26aii (23.7) to return metadata about database objects in JSON format.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article rely on the following table and view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
conn testuser1/testuser1@//localhost:1521/freepdb1

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname  varchar2(14),
  loc    varchar2(13)
);

create or replace view dept_v as select deptno, dname from dept;
2

GET_METADATA Basics

In this example we return the metadata describing the table. Notice the output is minified, so it looks like a solid block of text. The rest of the examples use the function to pretty print the output to make it easier to read. Notice the output includes metadata about the table, including all the columns. The etag attribute represents the current version of the document. If the object were changed, for example adding an extra column, the etag would change, allowing us to programatically react to that change. This will be discussed later.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
set long 1000000 linesize 100 pagesize 100
select dbms_developer.get_metadata (name => 'DEPT') as metadata;

METADATA
--------------------------------------------------------------------------------
{"objectType":"TABLE","objectInfo":{"name":"DEPT","schema":"TESTUSER1","columns"
:[{"name":"DEPTNO","notNull":true,"dataType":{"type":"NUMBER","precision":2},"is
Pk":true,"isUk":true,"isFk":false},{"name":"DNAME","notNull":false,"dataType":{"
type":"VARCHAR2","length":14,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk
":false},{"name":"LOC","notNull":false,"dataType":{"type":"VARCHAR2","length":13
,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false}],"hasBeenAnalyzed":
false,"indexes":[{"name":"PK_DEPT","indexType":"NORMAL","uniqueness":"UNIQUE","s
tatus":"VALID","hasBeenAnalyzed":false,"columns":[{"name":"DEPTNO"}]}],"constrai
nts":[{"name":"PK_DEPT","constraintType":"PRIMARY KEY","columns":[{"name":"DEPTN
O"}],"status":"ENABLE","deferrable":false,"validated":"VALIDATED","sysGeneratedN
ame":false}]},"etag":"D7A651A41686A88CBA87066D3A31B278"}

SQL>

select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "DEPT",
    "schema" : "TESTUSER1",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false
      },
      {
        "name" : "DNAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 14,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : false
      },
      {
        "name" : "LOC",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 13,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : false
      }
    ],
    "hasBeenAnalyzed" : false,
    "indexes" :
    [
      {
        "name" : "PK_DEPT",
        "indexType" : "NORMAL",
        "uniqueness" : "UNIQUE",
        "status" : "VALID",
        "hasBeenAnalyzed" : false,
        "columns" :
        [
          {
            "name" : "DEPTNO"
          }
        ]
      }
    ],
    "constraints" :
    [
      {
        "name" : "PK_DEPT",
        "constraintType" : "PRIMARY KEY",
        "columns" :
        [
          {
            "name" : "DEPTNO"
          }
        ],
        "status" : "ENABLE",
        "deferrable" : false,
        "validated" : "VALIDATED",
        "sysGeneratedName" : false
      }
    ]
  },
  "etag" : "D7A651A41686A88CBA87066D3A31B278"
}

SQL>
3

NAME and SCHEMA Parameters

The and parameters are case sensitive, and parameter values must match an existing object as described in the data dictionary. If the parameter is not specified, the current schema is assumed, so the following calls result in the same output when logged into the user. A synonym to the relevant table or view can be used in the parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT')
         pretty) as metadata;

select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT', schema => 'TESTUSER1')
         pretty) as metadata;
4

OBJECT_TYPE Parameter

The supported object types are currently limited to , and . The parameter is optional, as the object type is not necessary for name resolution. We've already seen an example of describing a table. The following examples display the metadata for an index and a view using the explicit parameter value. This parameter may become more relevant if the object type support is extended to other objects. For example packages and package bodies share the same name, so the object type is needed for name resolution. The same is true of types and type bodies.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
select json_serialize(
         dbms_developer.get_metadata (name => 'PK_DEPT', object_type => 'INDEX')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "INDEX",
  "objectInfo" :
  {
    "name" : "PK_DEPT",
    "indexType" : "NORMAL",
    "Owner" : "TESTUSER1",
    "tableName" : "DEPT",
    "status" : "VALID",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false
      }
    ],
    "uniqueness" : "UNIQUE",
    "hasBeenAnalyzed" : false
  },
  "etag" : "38AD7AF6BDCE96B3032B63E772C7570C"
}

SQL>


select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT_V', object_type => 'VIEW')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "VIEW",
  "objectInfo" :
  {
    "name" : "DEPT_V",
    "schema" : "TESTUSER1",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        }
      },
      {
        "name" : "DNAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 14,
          "sizeUnits" : "BYTE"
        }
      }
    ],
    "readOnly" : false,
    "dualityView" : false
  },
  "etag" : "C3C3370CA4001C644D1ECCB6E69477CA"
}

SQL>
5

LEVEL Parameter

The parameter limits the amount of detail presented by the function call. The allowable values are , and , with being the default. The following examples return the metadata for an index in all three levels.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
-- BASIC
select json_serialize(
         dbms_developer.get_metadata (name => 'PK_DEPT', level => 'BASIC')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "INDEX",
  "objectInfo" :
  {
    "name" : "PK_DEPT",
    "indexType" : "NORMAL",
    "Owner" : "TESTUSER1",
    "tableName" : "DEPT",
    "status" : "VALID",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        }
      }
    ]
  },
  "etag" : "ECDD7A4E8F49836CFFCFBFE6A2F38A2D"
}

SQL>


-- TYPICAL
select json_serialize(
         dbms_developer.get_metadata (name => 'PK_DEPT', level => 'TYPICAL')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "INDEX",
  "objectInfo" :
  {
    "name" : "PK_DEPT",
    "indexType" : "NORMAL",
    "Owner" : "TESTUSER1",
    "tableName" : "DEPT",
    "status" : "VALID",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false
      }
    ],
    "uniqueness" : "UNIQUE",
    "hasBeenAnalyzed" : false
  },
  "etag" : "38AD7AF6BDCE96B3032B63E772C7570C"
}

SQL>


-- ALL
select json_serialize(
         dbms_developer.get_metadata (name => 'PK_DEPT', level => 'ALL')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "INDEX",
  "objectInfo" :
  {
    "indexType" : "NORMAL",
    "visiblilty" : "VISIBLE",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false,
        "hiddenColumn" : false
      }
    ],
    "uniqueness" : "UNIQUE",
    "Owner" : "TESTUSER1",
    "hasBeenAnalyzed" : false,
    "toBeDropped" : false,
    "segmentCreated" : "NO",
    "name" : "PK_DEPT",
    "status" : "VALID",
    "compression" : "DISABLED",
    "tableName" : "DEPT"
  },
  "etag" : "CCEB79C45D764F558FF3B94633B46FDF"
}

SQL>
6

ETAG Parameter

As mentioned previously, the etag attribute represents the current version of the document. The parameter allows us to compare a previous etag value to that currently produced by the object. We display the metadata for the view. Notice the etag is "C3C3370CA4001C644D1ECCB6E69477CA". We check the metadata by including the parameter in the call. Notice the output is an empty document, as the value passed in matches that of the document produced by the current object. We change the view definition, which will result in a change to the etag attribute. We check the metadata, including the original etag value. Notice the document is no longer empty, and the new document has a different etag value. The etag value is based on the document contents, so altering call parameters, like switching from to , will result in a different etag value. Be consistent in how you request metadata if you are planning to use the etag value. For more information see: - DBMS_DEVELOPER - DBMS_METADATA Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT_V')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "VIEW",
  "objectInfo" :
  {
    "name" : "DEPT_V",
    "schema" : "TESTUSER1",
    "columns" :
    [
      {
        "name" : "DEPTNO",
        "notNull" : true,
        "dataType" :
        {
          "type" : "NUMBER",
          "precision" : 2
        }
      },
      {
        "name" : "DNAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 14,
          "sizeUnits" : "BYTE"
        }
      }
    ],
    "readOnly" : false,
    "dualityView" : false
  },
  "etag" : "C3C3370CA4001C644D1ECCB6E69477CA"
}

SQL>

select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT_V', etag => 'C3C3370CA4001C644D1ECCB6E69477CA')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
}

SQL>

create or replace view dept_v as select dname from dept;

select json_serialize(
         dbms_developer.get_metadata (name => 'DEPT_V', etag => 'C3C3370CA4001C644D1ECCB6E69477CA')
         pretty) as metadata;

METADATA
----------------------------------------------------------------------------------------------------
{
  "objectType" : "VIEW",
  "objectInfo" :
  {
    "name" : "DEPT_V",
    "schema" : "TESTUSER1",
    "columns" :
    [
      {
        "name" : "DNAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 14,
          "sizeUnits" : "BYTE"
        }
      }
    ],
    "readOnly" : false,
    "dualityView" : false
  },
  "etag" : "2AD82BA49CF2CB063F66DB655154DCC8"
}

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!