Overview of DBMS_UDR

The DBMS_UDR system 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 SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET statements. You can query the DBA_OB_USER_DEFINED_RULES view to obtain information about rewrite binding rules.

The enable_user_defined_rewrite_rules parameter specifies whether to enable the rewrite binding feature. By default, this feature is disabled. If this 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 described as follows:

• Use the same syntax as that for 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 other than those 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 a rule is matched exactly.

In the following example, the "{:name}" 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 describes whether the SELECT statements match the rewrite rules described above.

SQL	Whether the rule matches
select 1 + 1 from dual	Yes
select ? + 1 from dual	Yes
select 1 + 2 from dual	No
select 1 + ? from dual	No

Privilege

The enable_user_defined_rewrite_rules parameter specifies whether to enable the rewrite binding feature. When you create a rule, you must specify a database to control the privilege.

Subprograms of DBMS_UDR

The following table lists the subprograms supported by seekdb and their brief descriptions.

Subprogram	Description
CREATE_RULE	Creates a custom rewrite rule.
DISABLE_RULE	Disables a custom rewrite rule.
ENABLE_RULE	Enables a custom rewrite rule.
REMOVE_RULE	Removes a custom rewrite rule.