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.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
-
WHEREclause expression (if aWHEREclause is used, elseNULL). -
Whether each of the operations insert, delete, and update is allowed on the table. (For update, it is the default behavior for column updates that’s indicated; you can override this for specific columns.)
-
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 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, outer, or inner
-
The name of the JSON key associated with the link
See Also: Static Data Dictionary Views in Oracle AI 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. Its 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 AI Database JSON Developer’s Guide
-
JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE in Oracle AI Database JSON Developer’s Guide
-
DESCRIBE Function in Oracle AI Database PL/SQL Packages and Types Reference
-
ALL_JSON_DUALITY_VIEWS in Oracle AI Database Reference
-
ALL_JSON_DUALITY_VIEW_TABS in Oracle AI Database Reference
-
ALL_JSON_DUALITY_VIEW_TAB_COLS in Oracle AI Database Reference
-
ALL_JSON_DUALITY_VIEW_LINKS in Oracle AI Database Reference
-
You can also use PL/SQL function
DBMS_JSON_SCHEMA.describeto obtain a duality-view description. ↩