CREATE DIRECTIVE (VALIDATE)

Purpose

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

Prerequisites

You must have the CREATE DIRECTIVE system privilege to create a directive in your schema.

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

Syntax


Description of create_directive_validate.eps follows
Description of the illustration create_directive_validate.eps

action_list::=


Description of action_list.eps follows
Description of the illustration action_list.eps

processing_clause::=


Description of processing_clause.eps follows
Description of the illustration processing_clause.eps

validate_enable_option::=


Description of validate_enable_option.eps follows
Description of the illustration validate_enable_option.eps

directive_using_clause::=


Description of directive_using_clause.eps follows
Description of the illustration directive_using_clause.eps

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