DBMS_UDR overview

The DBMS_UDR package provides the rewrite binding feature, which allows you to rewrite SQL statements received by the database based on matching rules before the SQL statements are executed. This feature supports rewriting statements of the SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET types. You can query the DBA_OB_USER_DEFINED_RULES view to obtain information about rewrite binding rules.

The tenant-level parameter enable_user_defined_rewrite_rules specifies whether to enable the rewrite binding feature. By default, this parameter is set to OFF. If the rewrite binding feature is enabled, it checks whether to rewrite each SQL statement received by the server based on user-defined rewrite rules.

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

Rewrite binding rules and matching principles

The rewrite binding rules and matching principles are as follows:

• Use the same syntax as that for prepared statements to define rewrite rules.
• In the rewrite rule templates, use "?" or ":name" as placeholders for matching constant parameters.
• Constant parameter placeholders cannot be used for SQL keywords, identifiers, or functions.
• For constant values that are not marked with "?" or ":name", perform an exact match.
• When multiple matching rules are available, the first matching rule is randomly selected.
• The user specified when creating the rule is strictly matched.

In the following example, the ":" placeholder is used in the rewrite rule template to match constant parameters.

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

In the following example, the "?" placeholder is used in the rewrite rule template to match constant parameters.

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

The following table describes whether the SELECT statements match the rewrite rules mentioned above.

Privilege requirements

The tenant-level parameter enable_user_defined_rewrite_rules specifies whether to enable the rewrite binding feature. When creating a rule, specify a database to control the privileges.

DBMS_UDR subprograms

The following table lists the DBMS_UDR subprograms supported in the current version of OceanBase Database and their brief descriptions.