Restrictions for JavaScript Object Notation and JSON-Relational Duality Views

The following are general restrictions for JavaScript Object Notation (JSON) and restrictions for JSON-relational duality views in Oracle AI Database 26ai.

Restrictions for Oracle GoldenGate Replication of JSON-Relational Duality Views

• Direct loads (INSERT /*+APPEND*/) are downgraded to conventional or serial inserts.

• Oracle Data Pump EXPORT does not export the ENABLE LOGICAL REPLICATION clause in the CREATE DUALITY VIEW and ALTER DUALITY VIEW statements.

• Executing DBMS_METADATA.GET_DDL on a duality view does not show the ENABLE or DISABLE LOGICAL REPLICATION clause.

• On base tables of a duality view with replication enabled:

  • Oracle Data Pump direct path load is downgraded to conventional inserts.

  • Direct load (INSERT /*+APPEND*/) is downgraded to conventional or serial inserts.

  • Parallel DMLs (PDMLs) are downgraded to conventional inserts.

  • Bulk DDLs, like TRUNCATE TABLE and ALTER TABLE PARTITION, return an error.

• Logical replication cannot be enabled on a duality view that has generated fields or a sub-object without an identifier (for example, primary key, unique key, identity column, or the field _id).

Restrictions for JSON-Relational Duality Views

• The following tables can only participate in a JSON-relational duality view using read-only generated fields:

  • System-partitioned tables

  • Partitioned-extended tables

  • Sharded tables

  • Views (except editioning views). Views include materialized views and duality views.

  • External tables

  • Hybrid partitioned tables

  • Global or private temporary tables

  • Remote tables (for example, tables over database links)

• Updates of duality views across database links are not supported.

• You cannot create a functional index, JSON search index, or JSON multivalue index on the DATA column of a duality view.

• The use of a JSON search index on the column of an underlying table is not supported.

• Virtual private database (VPD) and Oracle Real Application Security (RAS) on duality views are not supported.

• VPD on underlying tables are supported only if all statements (INSERT, UPDATE, DELETE, or SELECT) are included in the policy. However, when all statement types are not included in the VPD policy, there is no error returned but DML and query results may be unexpected or may fail.

• Transparent Sensitive Data Protection is not supported with duality views and underlying tables.

• Table columns of a duality view cannot be redacted if the redacted columns are part of the ETAG.

• Because JSON-relational duality views rely on JSON type, a 19c or earlier SQL*Plus client cannot be used for queries, DML or other SQL operations that use the DATA column which is of data type JSON.

• Duality views cannot be created on base tables with textual JSON columns (VC2/CLOB/BLOB with IS-JSON constraint) in the base table.

• The same subquery cannot be used multiple times to define nested JSON structures (object or array) in the same parent object.

• Fine-grained auditing policies are not supported with duality views.

• DML error logging is not supported with duality views.