Overview of DBMS_UDR

The DBMS_UDR system package provides the rewrite binding feature, which can rewrite SQL statements received by the database based on matching rules before they are executed. Currently, rewriting is supported for statement types SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET. 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 principles

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 "?" or ":{name}", by equality.
• When multiple matching rules exist, the first rule that matches is randomly selected.
• The database name specified when the rule was created (or the current user in Oracle-compatible mode) must be strictly matched.

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 "?" is used as a marker for matching 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 examples of SELECT statements match the preceding rewriting rules.

Privileges

The enable_user_defined_rewrite_rules tenant-level parameter controls whether the rewrite binding feature is enabled 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.