CREATE_RULE

The CREATE_RULE procedure is used to create custom rewrite rules.

Syntax

PROCEDURE CREATE_RULE (
    rule_name          IN VARCHAR,
    pattern            IN CLOB,
    replacement        IN CLOB,
    enabled            IN VARCHAR  := 'YES'
  );

Parameters

Considerations

The rewrite binding rules are subject to the following restrictions:

• Supported SQL statement types: SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET.
• The question mark (?) and colon-name notation (:{name}) cannot be mixed in the rule templates.
• When using colon-name notation (:{name}) as the parameter marker in the pattern definition, the name must be unique.
• The pattern and replacement cannot be defined as empty.
• The pattern and replacement do not support Multi Query.
• The rule_name must be unique at the tenant level.
• The number of parameter markers in the pattern and replacement must be the same.
• The parameter markers in the pattern and replacement must be represented in the same way (either both using ? or both using :{name}).

Examples

obclient> CALL DBMS_UDR.CREATE_RULE('rule1', 'select ? from dual','select ? + 1 from dual');
Query OK, 0 rows affected

obclient> SELECT 1 FROM DUAL;
+-----+
| ?+1 |
+-----+
|   2 |
+-----+
1 row in set