5.7 Obtaining Information About a Duality View
You can obtain information about a duality view, its underlying tables, their columns, and key-column links, using static data dictionary views. You can also obtain a JSON-schema description of a duality view, which includes a description of the structure and JSON-language types of the JSON documents it supports.
Static Dictionary Views For JSON Duality Views
You can obtain information about existing duality views by checking
static data dictionary views DBA_JSON_DUALITY_VIEWS,
USER_JSON_DUALITY_VIEWS, and
ALL_JSON_DUALITY_VIEWS.Foot 1 Each of these dictionary views includes the
following for each duality view:
-
The view name and owner
-
Name of the
JSON-type column -
The root table name and owner
-
Whether each of the operations insert, delete, and update is allowed on the view
-
Whether the view is read-only
-
The JSON schema that describes the JSON column
-
Whether the view is valid
-
Whether the view is enabled for logical replication.
You can list the tables that underlie duality views, using
dictionary views DBA_JSON_DUALITY_VIEW_TABS,
USER_JSON_DUALITY_VIEW_TABS, and
ALL_JSON_DUALITY_VIEW_TABS. Each of these dictionary
views includes the following for a duality view:
-
The view name and owner
-
The table name and owner
-
Whether each of the operations insert, delete, and update is allowed on the table
-
Whether the table is read-only
-
Whether the table has a flex column
-
Whether the table is the root table of the view
-
A number that identifies the table in the duality view
-
a number that identifies the parent table in the view
-
The relationship of the table to its parent table: whether it is nested within its parent, or it is the target of an outer or an inner join
You can list the columns of the tables that underlie duality
views, using dictionary views
DBA_JSON_DUALITY_VIEW_TAB_COLS,
USER_JSON_DUALITY_VIEW_TAB_COLS, and
ALL_JSON_DUALITY_VIEW_TAB_COLS. Each of these
dictionary views includes the view and table names and owners, whether the table is
the root table, a number that identifies the table in the view, and the following
information about each column in the table:
-
The column name, data type, and maximum number of characters (for a character data type)
-
The JSON key name
-
Whether each of the operations insert, delete, and update is allowed on the column
-
Whether the column is read-only
-
Whether the column is a flex column
-
Whether the column is generated.
-
Whether the column is hidden.
-
The position of the column in an identifying-columns specification (if it is an identifying column)
-
The position of the column in an ETAG specification (if relevant)
-
The position of the column in an
ORDER BYclause of a call to functionjson_arrayagg(or equivalent) in the duality-view definition (if relevant)
You can list the links associated with duality views, using
dictionary views DBA_JSON_DUALITY_VIEW_LINKS,
USER_JSON_DUALITY_VIEW_LINKS, and
ALL_JSON_DUALITY_VIEW_LINKS. Links are from
identifying columns to other columns. Each of these dictionary views includes the
following for each link:
-
The name and owner of the view
-
The name and owner of the parent table of the link
-
The name and owner of the child table of the link
-
The names of the columns on the from and to ends of the link
-
The join type of the link: nested or outer
-
The name of the JSON key associated with the link
See Also:
Static Data Dictionary Views in Oracle Database Reference
JSON Description of a JSON-Relational Duality View
A JSON schema specifies the structure and JSON-language types of JSON data. It can serve as a summary description of an existing set of JSON documents, or it can serve as a specification of what is expected or allowed for a set of JSON documents. The former use case is that of a schema obtained from a JSON data guide. The latter use case includes the case of a JSON schema that describes the documents supported by a duality view.
You can use PL/SQL function DBMS_JSON_SCHEMA.describe to
obtain a JSON schema that describes the JSON documents supported by a duality view.
(This same document is available in column JSON_SCHEMA of
static dictionary views DBA_JSON_DUALITY_VIEWS,
USER_JSON_DUALITY_VIEWS, and
ALL_JSON_DUALITY_VIEWS — see Static Dictionary Views For JSON Duality Views.)
This JSON schema includes three kinds of information:
-
Information about the duality view that supports the documents.
This includes the database schema (user) that owns the view (field
dbObject) and the allowed operations on the view (fielddbObjectProperties). -
Information about the columns of the tables that underlie the duality view.
This includes domain names (field
dbDomain), fields corresponding to identifying columns (fielddbPrimaryKey), fields corresponding to foreign-key columns (fielddbForeignKey), whether flex columns exist ( fieldadditionalProperties), and column data-type restrictions (for example, fieldmaxLengthfor strings and fieldsqlPrecisionfor numbers). -
Information about the allowed structure and JSON-language typing of the documents.
This information can be used to validate data to be added to, or changed in, the view. It's available as the value of top-level schema-field
properties, and it can be used as a JSON schema in its own right.
Example 5-22 uses DBMS_JSON_SCHEMA.describe to describe each
of the duality views of the car-racing example: driver_dv,
race_dv, and team_dv.
Example 5-22 Using DBMS_JSON_SCHEMA.DESCRIBE To Show JSON Schemas Describing Duality Views
This example shows, for each car-racing duality view, a JSON schema that describes the JSON documents supported by the view.
The value of top-level JSON-schema field
properties is itself a JSON schema that can be used to
validate data to be added to, or changed in, the view. The other top-level
properties describe the duality view that supports the documents.
The database schema/user that created, and thus owns, each view is
indicated with a placeholder value here (shown in italics).
This is reflected in the value of field dbObject, which for
a duality view is the view name qualified by the database-schema name of the view
owner. For example, assuming that database user/schema
team_dv_owner created duality view team_dv,
the value of field dbObject for that view is
team_dv_owner.team_dv.
(Of course, these duality views could be created, and thus owned, by the same database user/schema. But they need not be.)
Array field dbObjectProperties specifies the
allowed operations on the duality view itself:
-
checkmeans that at least one field in each document is markedCHECK, and thus contributes to ETAG computation. -
deletemeans you can delete existing documents from the view. -
insertmeans you can insert documents into the view. -
updatemeans you can update existing documents in the view.
Field type specifies a standard JSON-language
nonscalar type: object or array. Both fields
type and extendedType are used to
specify scalar JSON-language types.
Native binary JSON data (OSON format) extends the JSON language by
adding scalar types, such as date, that correspond to SQL data types and are not
part of the JSON standard. These Oracle-specific scalar types are always specified
with extendedType.
Field items specifies the element type for an
array value. The fields of each JSON object in a supported document are listed under
schema field properties for that object. All document fields
are underlined here.
(All you need to create the JSON schema is function
DBMS_JSON_SCHEMA.describe. It's use here is wrapped with
SQL/JSON function json_serialize just to pass keyword
PRETTY, which causes the output to be pretty-printed.)
-- Duality View TEAM_DV
SELECT json_serialize(DBMS_JSON_SCHEMA.describe('TEAM_DV') PRETTY)
AS team_dv_json_schema;TEAM_DV_JSON_SCHEMA
-------------------
{"title" : "TEAM_DV",
"dbObject" : "TEAM_DV_OWNER.TEAM_DV",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"type" : "object",
"properties" : {"_id" :
{"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"_metadata" : {"etag" : {"extendedType" : "string",
"maxLength" : 200},
"asof" : {"extendedType" : "string",
"maxLength" : 20}},
"dbPrimaryKey" : [ "_id" ],
"name" : {"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "update",
"check" ]},
"points" : {"extendedType" : "number",
"sqlScale" : 0,
"dbFieldProperties" : [ "update",
"check" ]},
"driver" :
{"type" : "array",
"items" :
{"type" : "object",
"properties" :
{"dbPrimaryKey" : [ "driverId" ],
"name :
{"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "update", "check" ]},
"points" :
{"extendedType" : "number",
"sqlScale" : 0,
"dbFieldProperties" : [ "update" ]},
"driverId" : {"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]}},
"required" : [ "name",
"points",
"driverId" ],
"additionalProperties" : false}}},
"required" : [ "name", "points", "_id" ],
"additionalProperties" : false}
1 row selected.
-- Duality View DRIVER_DV
SELECT json_serialize(DBMS_JSON_SCHEMA.describe('DRIVER_DV') PRETTY)
AS driver_dv_json_schema;
DRIVER_DV_JSON_SCHEMA
---------------------
{"title" : "DRIVER_DV",
"dbObject" : "DRIVER_DV_OWNER.DRIVER_DV",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"type" : "object",
"properties" : {"_id" : {"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"_metadata" : {"etag" : {"extendedType" : "string",
"maxLength" : 200},
"asof" : {"extendedType" : "string",
"maxLength" : 20}},
"dbPrimaryKey" : [ "_id" ],
"name" : {"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "update", "check" ]},
"points" : {"extendedType" : "number",
"sqlScale" : 0,
"dbFieldProperties" : [ "update", "check" ]},
"team" : {"extendedType" : "string",
"maxLength" : 255},
"teamId" : {"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"race" : {"type" : "array",
"items" :
{"type" : "object",
"properties" :
{"dbPrimaryKey" : [ "driverRaceMapId" ],
"finalPosition" :
{"extendedType" : [ "number",
"null" ],
"sqlScale" : 0,
"dbFieldProperties" : [ "update",
"check" ]},
"driverRaceMapId" :
{"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"name" :
{"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "check" ]},
"raceId" :
{"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ] }},
"required" :
[ "driverRaceMapId", "name", "raceId" ],
"additionalProperties" : false}}},
"required" : [ "name", "points", "_id", "team", "teamId" ],
"additionalProperties" : false}
1 row selected.-- Duality View RACE_DV
SELECT json_serialize(DBMS_JSON_SCHEMA.describe('RACE_DV') PRETTY)
AS race_dv_json_schema;RACE_DV_JSON_SCHEMA
-------------------
{"title" : "RACE_DV",
"dbObject" : "RACE_DV_OWNER.RACE_DV",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"type" : "object",
"properties" : {"_id" : {"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"_metadata" : {"etag" : {"extendedType" : "string",
"maxLength" : 200},
"asof" : {"extendedType" : "string",
"maxLength" : 20}},
"dbPrimaryKey" : [ "_id" ],
"laps" : {"extendedType" : "number",
"sqlScale" : 0,
"dbFieldProperties" : [ "check" ]},
"name" : {"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "update", "check" ]},
"podium" : {"dbFieldProperties" : [ "update" ]},
"date" : {"extendedType" : "date",
"dbFieldProperties" : [ "update", "check" ]},
"result" : {"type" : "array",
"items" :
{"type" : "object",
"properties" :
{"dbPrimaryKey" : [ "driverRaceMapId" ],
"position" :
{"extendedType" : "number",
"sqlScale" : 0,
"dbFieldProperties" : [ "update",
"check" ]},
"driverRaceMapId" :
{"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]},
"name" :
{"extendedType" : "string",
"maxLength" : 255,
"dbFieldProperties" : [ "update",
"check" ]},
"driverId" :
{"extendedType" : "number",
"sqlScale" : 0,
"generated" : true,
"dbFieldProperties" : [ "check" ]}},
"required" : [ "driverRaceMapId",
"name",
"driverId" ],
"additionalProperties" : false}}},
"required" : [ "laps", "name", "_id" ],
"additionalProperties" : false}
1 row selected.Related Topics
See Also:
-
JSON Data Guide in Oracle Database JSON Developer’s Guide
-
JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE in Oracle Database JSON Developer’s Guide
-
DESCRIBE Function in Oracle Database PL/SQL Packages and Types Reference
-
ALL_JSON_DUALITY_VIEWS in Oracle Database Reference
-
ALL_JSON_DUALITY_VIEW_TABS in Oracle Database Reference
-
ALL_JSON_DUALITY_VIEW_TAB_COLS in Oracle Database Reference
-
ALL_JSON_DUALITY_VIEW_LINKS in Oracle Database Reference
Parent topic: Using JSON-Relational Duality Views
Footnote Legend
Footnote 1: You can also use PL/SQL functionDBMS_JSON_SCHEMA.describe to obtain a
duality-view description.