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 statement types including SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET. You can query the DBA_OB_USER_DEFINED_RULES view for rewrite binding rules.
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}".
- When multiple matching rules exist, the first rule that matches is applied randomly.
- Exact match the database name 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 "?" 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 describes whether the examples of the SELECT statement match the preceding rewriting rules.
SQL |
Whether the rule is matched |
|---|---|
| select 1 + 1 from dual | Yes |
| select ? + 1 from dual | Yes |
| select 1 + 2 from dual | No |
| select 1 + ? from dual | No |
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 OceanBase AI Database and provides brief descriptions.
Subprogram |
Description |
|---|---|
| CREATE_RULE | Create a custom rewrite rule. |
| DISABLE_RULE | Disables a custom rewrite rule. |
| ENABLE_RULE | Enables a custom rewrite rule. |
| REMOVE_RULE | Deletes a custom rewrite rule. |