CREATE_RULE

The CREATE_RULE procedure creates a custom rewrite rule.

Syntax

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

Parameters

Considerations

The limits on the rewrite and binding rules are as follows:

• Supported SQL statement types are SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET.
• The two parameter markers "?" and ":{name}" cannot coexist in a rule template.
• If ":{name}" is used as a parameter marker in a pattern definition, name must be unique.
• pattern and replacement cannot be empty.
• pattern and replacement do not support multi-queries.
• rule_name must be unique within a tenant.
• In a rule template, pattern and replacement must have the same number of parameter markers.
• In a rule template, pattern and replacement must use the same parameter marker, which can be "?" or ":{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