Overview of DBMS_UDR

The DBMS_UDR system package provides rewrite binding functionality, which can rewrite SQL statements received by the database based on matching rules before they are executed. Currently, rewriting is supported for the SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET statement types. Rewrite binding rules can be queried through the DBA_OB_USER_DEFINED_RULES view.

The tenant-level parameter enable_user_defined_rewrite_rules controls whether to enable the rewrite binding feature, which is disabled by default. 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 principle

The rewriting, binding rules and matching principle are as follows:

• Defines rewrite rules using the same syntax as prepared statements.
• Use "?" or "{name}" in the rewrite rule template to mark the matching constant parameters.
• Constant parameter markers cannot be used as SQL keywords, identifiers, or functions.
• Matches constant values, except for those marked with a "?" or "{name}", by equality.
• When multiple matching rules exist, the first rule that matches is applied randomly.
• Strictly match the user specified when the rule was created.

In the following example, ":{name}" is used as a marker for matching 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, a question mark (?) is used in the rewrite rule template to represent a matching constant parameter.

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

The following table shows whether the examples of SELECT statements match the preceding rewriting rules.

Privileges

The tenant-level parameter enable_user_defined_rewrite_rules controls whether to enable the rewrite binding feature for a tenant. When creating a rule, you can specify a database to control permissions.

Overview of DBMS_UDR Subprograms

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