Creating Validation Directives

You can create a validation directive using SQL CREATE DIRECTIVE statement.

Syntax:

CREATE (OR REPLACE) DIRECTIVE directive_name
  FOR json_relational_duality_view_name
  VALIDATE
  ON ( SELECT | INSERT | UPDATE )+
  Processing_Stage_Clause
  [ Validate_Enable_Clause ]
  USING validation_logic;

Where:

Table 1 Validation directive clauses and options

Clause What it specifies Options Notes
ON Which operations trigger validation SELECT, INSERT, UPDATE (one or more) SELECT validation runs when the full object is selected; it does not run for queries that select only a single field from the JSON object. For example: a select * from <duality_view> or select data from <duality_view> will invoke the SELECT validation.
Processing stage When validation is evaluated BEFORE OBJECT, AFTER OBJECT, ON COMMIT Failures lead to different outcomes (see “Validation failure behavior”).
VALIDATE / NOVALIDATE Whether existing data is validated when the directive is created VALIDATE or NOVALIDATE If you specify VALIDATE and validation of the existing data fails, creation of the directive fails. Default is NOVALIDATE.
ENABLE / DISABLE Whether validation is active after creation ENABLE or DISABLE Default is ENABLE. ALTER DIRECTIVE can also be used to enable/disable a validation directive.
USING The validation logic Expression or PL/SQL function or PL/SQL anonymous block or scalar subquery Logic must be deterministic and side-effect free. DML, ALTER SESSION, COMMIT/ROLLBACK/SAVEPOINT, PRAGMA AUTONOMOUS_TRANSACTION, package state mutation, and references to packages such as UTL_HTTP, UTL_TCP, and UTL_SMTP are disallowed.

Validation failure behavior

The effect of a validation failure depends on when the validation directive is evaluated:

Note:

The following restrictions apply to validation directives: Unsupported NLS properties: Session-level settings for the following NLS properties can result in unexpected behavior with validation directives: NLS_SORT, NLS_TERRITORY, and NLS_TIMESTAMP_FORMAT. To help keep directive execution invariant across sessions, ALTER SESSION statements are disallowed inside directive validation logic. SYS schema not supported: Validation directives cannot be created under the SYS schema. Parallel and direct-load DML downgrade: If a directive exists on a duality view, parallel DML (PDML) and direct-load operations (IDL/ODL) are downgraded to serial, conventional DML.

Example setup

Run the following statements before the validation directive examples. They create the tables and duality views used in this topic.

CREATE TABLE employee
  (employee_id   INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   employee_name VARCHAR2(255) NOT NULL,
   salary        NUMBER(10,2) NOT NULL,
   CONSTRAINT employee_pk PRIMARY KEY(employee_id));

CREATE JSON RELATIONAL DUALITY VIEW employee_dv AS
  SELECT JSON {'_id'          : e.employee_id,
               'employeeName' : e.employee_name,
               'salary'       : e.salary}
    FROM employee e WITH INSERT UPDATE DELETE;

CREATE TABLE customer_order
  (order_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   status   VARCHAR2(20) NOT NULL,
   CONSTRAINT customer_order_pk PRIMARY KEY(order_id),
   CONSTRAINT customer_order_status_ck
     CHECK (status IN ('pending', 'shipped')));

CREATE TABLE order_item
  (order_item_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   order_id      INTEGER NOT NULL,
   item_name     VARCHAR2(255) NOT NULL,
   CONSTRAINT order_item_pk PRIMARY KEY(order_item_id),
   CONSTRAINT order_item_fk FOREIGN KEY(order_id)
     REFERENCES customer_order(order_id));

CREATE JSON RELATIONAL DUALITY VIEW Orders_DualV AS
  SELECT JSON {'_id'       : o.order_id,
               'status'    : o.status,
               'orderItems' :
                 [ SELECT JSON {'orderItemId' : i.order_item_id,
                                'itemName'    : i.item_name}
                     FROM order_item i WITH INSERT UPDATE DELETE
                     WHERE i.order_id = o.order_id ]}
    FROM customer_order o WITH INSERT UPDATE DELETE;

Validate an INSERT before writing the document (BEFORE OBJECT)

This example ensures that an employee salary in duality view employee_dv is below 10,000 for inserts.

CREATE DIRECTIVE salary_less_10k FOR employee_dv
  VALIDATE
  ON INSERT
  BEFORE OBJECT
  USING json_value(new.data, '$.salary') < 10000;

If validation fails, the insert statement fails and is rolled back.

Validate a document when it is selected (SELECT, AFTER OBJECT)

This example validates a rule on reads. Read-time validation can be useful when you want to avoid additional DML latency and instead pay validation cost when documents are fetched.

CREATE OR REPLACE DIRECTIVE name_only_king FOR employee_dv
  VALIDATE
  ON SELECT
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING json_value(new.data, '$.employeeName') = 'KING';

If validation fails, Oracle raises an error and the document is not returned.

Validation Using a PL/SQL Function (BEFORE OBJECT, SCOPE OBJECT)

This example shows how to define validation logic in a PL/SQL function and attach it to a duality view using a validation directive. It enforces the business rule: salary must be at least 2000 for new employee objects inserted into duality view.

Step 1: Create a PL/SQL validation function:

CREATE OR REPLACE FUNCTION isThisSalaryOK(old_data JSON, new_data JSON)
  RETURN BOOLEAN
IS
  f BOOLEAN;
BEGIN
  IF json_value(new_data, '$.salary') < 2000 THEN
    f := false;
  ELSE
    f := true;
  END IF;

  RETURN f;
END;
/

The function takes two JSON arguments: old_data - the “before” version of the document. new_data - the “after” (proposed) version of the document being inserted/updated. For an INSERT validation, the rule is typically expressed using new_data (since there is no prior version in the same sense as an update). The function returns a SQL BOOLEAN: TRUE means validation passes. FALSE means validation fails, and the database raises a validation error for the directive.

Step 2: Create the validation directive on the duality view

This directive uses the function created in step 1 and enforces that salary should be at least 2000.

CREATE DIRECTIVE validate_salary FOR employee_dv
  VALIDATE
  ON INSERT
  BEFORE OBJECT
  NOVALIDATE
  USING isThisSalaryOK;

Here is another example that prevents users from adding items to an order document after the order status is shipped:

Example 9-1 Order-status validation directive

The validation logic runs at commit time, compares the old and new item counts, and rejects the transaction if the item count changes while the existing status is shipped. Because the directive runs ON COMMIT, it also protects related documents that share the same underlying tables.

Step 1: Create a PL/SQL Validation Function

This function checks whether the item count has changed while the status is SHIPPED.

CREATE OR REPLACE FUNCTION ChkItemCntChg(
  p_old_json IN JSON,
  p_new_json IN JSON
) RETURN BOOLEAN
AS
  l_old_obj   JSON_OBJECT_T;
  l_new_obj   JSON_OBJECT_T;
  l_old_cnt   PLS_INTEGER := 0;
  l_new_cnt   PLS_INTEGER := 0;
  l_status    VARCHAR2(20);
BEGIN
  -- Read the old and new order objects
  l_old_obj := json_object_t(p_old_json.data);
  l_new_obj := json_object_t(p_new_json.data);

  -- Get the previous order status and previous and latest item counts
  l_status  := l_old_obj.GET_STRING('status');
  l_old_cnt := l_old_obj.GET_ARRAY('orderItems').GET_SIZE;
  l_new_cnt := l_new_obj.GET_ARRAY('orderItems').GET_SIZE;

  IF l_old_cnt != l_new_cnt AND l_status = 'shipped' THEN
    RETURN FALSE; -- reject additions after shipment
  END IF;

  RETURN TRUE;
END;
/

Step 2: Create a directive to ensure that a new item is not added to an order that has already shipped.

CREATE DIRECTIVE NewItemValidate FOR Orders_DualV
  VALIDATE
  ON UPDATE INSERT
  ON COMMIT
  USING ChkItemCntChg;

Note: Ensure the identifier named in the USING clause matches the helper function you compile in your environment.

For examples that apply validation directives to the car-racing duality views, see Car-Racing Validation Directive Examples.