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:

Processing_Stage_Clause is one of:
- BEFORE OBJECT
- AFTER OBJECT
- ON COMMIT
Validate_Enable_Clause is:
(VALIDATE | NOVALIDATE) (ENABLE | DISABLE)
validation_logic is:
a SQL/JSON expression, a PL/SQL function, or a PL/SQL anonymous block

Table 9-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	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:

SELECT: Oracle raises an error and does not return the document that fails validation.
BEFORE OBJECT: Oracle rejects the operation before applying it. The statement is not executed, and any partial work for the statement is rolled back
AFTER OBJECT: Oracle rolls back the statement and raises an error.
ON COMMIT: Oracle rejects the commit. The commit fails and the transaction is rolled back.

Note:

Validation Directives Restrictions:

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.

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 greater than 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 if the item count has changed and the status is not 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 CheckItemCntChg;

Note:

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