Overview

The DBMS_UDR system package provides the rewrite binding feature, which allows you to rewrite SQL statements received by the database before the SQL statements are executed. This feature supports rewriting SQL 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 feature is disabled. If the rewrite binding feature is enabled, it checks 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 binding rules and matching principles

The rewrite binding rules and matching principles are as follows:

• Use the same syntax as prepared statements to define rewrite rules.
• Use "?" or ":name" as placeholders for matching constant parameters in the rewrite rule templates.
• 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.
• If multiple matching rules are available, the first one that matches is randomly selected.
• The database name specified when creating the rule (which is the current user in Oracle mode) is matched strictly.

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

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 to match constant parameters in the rewrite rule template.

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

The following table shows 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 for the tenant. When creating a rule, you must specify the 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.