Overview

The DBMS_UDR package provides the rewrite and binding feature that allows you to rewrite the SQL statement received by the database based on matched rules before the SQL statement is executed. At present, you can rewrite the following types of statements: SELECT, INSERT, REPLACE, UPDATE ,DELETE, MERGE, and SET. You can query the DBA_OB_USER_DEFINED_RULES view for the rewrite and binding rules.

The tenant-level parameter enable_user_defined_rewrite_rules specifies whether to enable the rewrite and binding feature, which is disabled by default. If the rewrite and binding feature is enabled, it will determine whether to rewrite each SQL statement received by the server based on the user-defined rewrite rules.

ALTER SYSTEM SET enable_user_defined_rewrite_rules = 'True';
ALTER SYSTEM SET enable_user_defined_rewrite_rules = 'False';

Rewrite and binding rules and matching principles

Rewrite and binding rules and matching principles:

• Rewrite rules are defined by using the same syntax as prepared statements.
• In the rewrite rule template, the question mark (?) or ":{name}" is a mark used to match a constant parameter.
• Constant parameter markers cannot be used for SQL keywords, identifiers, or functions.
• Except the parameters marked with a question mark (?) or ":{name}", other constants are matched against the rules by value.
• If multiple rules are matched, the first randomly matched rule prevails.
• db_name (which is the current user in Oracle mode) specified during rule creation is strictly matched.

In the following example, the ":{name}" string in the rewrite rule template is a mark used to match a constant parameter.

CALL DBMS_UDR.CREATE_RULE('rule1',
                          'test',
                          'select :A + 1 from dual',
                          'select :A + 10, 20 from dual');

In the following example, the question mark (?) in the rewrite rule template is a mark used to match a constant parameter.

CALL DBMS_UDR.CREATE_RULE('rule1',
                          'test',
                          'select ? + 1 from dual',
                          'select ? + 10, 20 from dual');

The following table describes whether the sample SELECT statements match the preceding rewrite rules.

Privileges

The tenant-level parameter enable_user_defined_rewrite_rules specifies whether to enable the rewrite and binding feature for the tenant. When rules are created, a database is specified to control the privileges.

Subprograms

The following table describes the DBMS_UDR subprograms supported by the current OceanBase Database version.