CREATE DIRECTIVE (VALIDATE)

Purpose

Use CREATE DIRECTIVE to create a validate directive on a JSON relational duality view.

Prerequisites

You must have the CREATE ANY VALIDATION DIRECTIVE system privilege to create a directive in any schema, other than your own.

The CREATE ANY VALIDATION DIRECTIVE is a sensitive privilege because it allows a user to create validation directives on duality views in other schemas. A BEFORE and AFTER validation directive can access the affected row values through :OLD and :NEW pseudorecords during INSERT, UPDATE and SELECT operations. As a result, although CREATE ANY VALIDATION DIRECTIVE does not directly grant unrestricted SELECT access to a table, it can enable indirect read access to table data contained in the payloads when those validation directives are executed.

Syntax

Description of the illustration create_directive_validate.gif

action_list::=

Description of the illustration action_list.gif

processing_clause::=

Description of the illustration processing_clause.gif

validate_enable_option::=

Description of the illustration validate_enable_option.gif

directive_using_clause::=

Description of the illustration directive_using_clause.gif

Semantics

action_list

action_list specifies what actions on a duality view would lead to the directive being executed. You can use UPDATE, INSERT, and SELECT. You will not incur a latency penalty on DMLs on the duality view when you specify SELECT in the action_list. The overhead of validating an object is incurred at the time of read.

processing_clause

The processing_clause specifies when the validation logic is invoked.

BEFORE OBJECT and AFTER OBJECT processing is equivalent to before and after row processing for triggers.

• BEFORE OBJECT processing allows validating input object for the following:

  • For INSERTs into duality view, the validation logic can validate input object from the application.

  • For updates that input the full document, validation logic will validate the same set of fields as for inserts.

  • For UPDATEs that do not input the full document, e.g. updates using JSON_TRANSFORM, the validation logic validates the input object, and inline read augmented fields and fields. This is because there is an implicit read of the object before JSON_TRANSFORM is performed to update a select list of fields.

  • For SELECTs, no BEFORE OBJECT processing is possible.

• AFTER OBJECT processing validates object read from the database after the DML is complete.

  • Validation on INSERT and UPDATE runs for all DML operations on the duality view.

  • Validation for SELECT action only runs if the entire object selected. For example, if a query selects just a single field from the JSON object, the validation directive is not run.

  • For SELECT action, the only valid option is AFTER OBJECT. Specifying other processing options result in an error.

• BEFORE OBJECT and AFTER OBJECT processing are therefore performed only at the SCOPE of the object. They are not feasible to be run for DMLs that modify a duality view by virtue of modifying underlying tables.

• ON COMMIT processing allows validation to be run only after the transaction is finalized, i.e. no more modifications are possible. Validating on commit incurs only a small penalty during statement execution, in order to journal the list of modified objects, while the majority of overhead of the validation logic is incurred at the time of commit.

  Note that ON COMMIT validation also uses certain locks and enqueues to prevent concurrent modifications on the objects being validated. In contrast, AFTER OBJECT validation only validates the object after the object has been modified. Concurrent transactions could still modify the object between the time the validation logic runs and before the transaction commits, and violate the validation logic.

validate_enable_option

validate_enable_option specifies whether application objects in the duality view should be validated when the directive is created.

• This clause applies only to validation directives.

• ENABLE or DISABLE keywords specify whether the directive should be enabled or disabled at the time of creation.

• Existing data can be validated using the validate_enable_option by specifying VALIDATE in this clause.

• The default for validate_enable_option is NOVALIDATE (existing data is not validated) .

• The validate_enable_option can be changed using ALTER DIRECTIVE (VALIDATE).

directive_using_clause

directive_using_clause specifies the validation logic to be invoked for validating objects in a duality view.

• Validation logic may be specified either as a PL/SQL anonymous block, or a PL/SQL function, or as a SQL or JSON expression.

• There are certain statements and constructs that cannot be used in the USING clause in order to keep the USING clause deterministic and repeatable as follows:

  • DMLs

  • ALTER SESSION statements

  • ROLLBACK

  • COMMIT

  • SAVEPOINT

  • PRAGMA AUTONOMOUS_TRANSACTION

  • Any reference to UTL_HTTPS, UTL_TCP, UTL_SMTP

  • USING clause which changes the package state is also not allowed