Overview

The DBMS_UDR package provides the rewrite binding feature, which rewrites an SQL statement received by the database before execution based on the rewrite binding rule that the statement matches. At present, the package supports the rewrite of the following types of statement: SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET. You can query the DBA_OB_USER_DEFINED_RULES view for rewrite binding rules.

OceanBase Database provides the tenant-level parameter enable_user_defined_rewrite_rules for you to enable or disable the rewrite binding feature. By default, the feature is disabled. After you enable the rewrite binding feature, it checks whether to rewrite each SQL statement received by the server based on user-defined rules.

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

Considerations and matching principles for rewrite binding rules

Take note of the following considerations and principles:

• Define a rule with the same syntax as prepared statements.
• Use a question mark (?) or :{name} in a rule pattern as a placeholder to match a constant parameter.
• Do not use constant parameter placeholders in SQL keywords, identifiers, or functions.
• Perform an exact match for constant values that are not represented by a question mark (?) or :{name}.
• Use the first randomly matched rule if multiple rules are matched at the same time.
• Perform an exact match for the database name specified when a rule was created. In Oracle mode, you do not need to specify this parameter, and the rule applies to the current user.

The following example uses :{name} as the placeholder in a rule pattern to match constant parameters:

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

The following example uses a question mark (?) as the placeholder in a rule pattern to match constant parameters:

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

The following table describes sample SELECT statements that match and do not match the preceding rules.

Privileges

You can set the tenant-level parameter enable_user_defined_rewrite_rules to enable or disable the rewrite binding feature for a tenant. You can also specify a database when you create a rule for privilege control.

Subprograms

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