6 Rule-Based Transformations

Custom Rule-Based Transformations and Action Contexts

You use the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package to specify a custom rule-based transformation for a rule. This procedure modifies the action context of a rule to specify the transformation. A rule action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE for a message. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Oracle Streams. The information in an action context is an object of type SYS.RE$NV_LIST, which consists of a list of name-value pairs.

A custom rule-based transformation in Oracle Streams always consists of the following name-value pair in an action context:

• If the function is a one-to-one transformation function, then the name is STREAMS$_TRANSFORM_FUNCTION. If the function is a one-to-many transformation function, then the name is STREAMS$_ARRAY_TRANS_FUNCTION.

• The value is an ANYDATA instance containing a PL/SQL function name specified as a VARCHAR2. This function performs the transformation.

You can display the existing custom rule-based transformations in a database by querying the DBA_STREAMS_TRANSFORM_FUNCTION data dictionary view.

When a rule in a positive rule set evaluates to TRUE for a message in an Oracle Streams environment, and an action context that contains a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION or STREAMS$_ARRAY_TRANS_FUNCTION is returned, the PL/SQL function is run, taking the message as an input parameter. Other names in an action context beginning with STREAMS$_ are used internally by Oracle and must not be directly added, modified, or removed. Oracle Streams ignores any name-value pair that does not begin with STREAMS$_ or APPLY$_.

When a rule evaluates to FALSE for a message in an Oracle Streams environment, the rule is not returned to the client, and any PL/SQL function appearing in a name-value pair in the action context is not run. Different rules can use the same or different transformations. For example, different transformations can be associated with different operation types, tables, or schemas for which messages are being captured, propagated, applied, or dequeued.

Required Privileges for Custom Rule-Based Transformations

The user who calls the transformation function must have EXECUTE privilege on the function. The following list describes which user calls the transformation function:

• If a transformation is specified for a rule used by a capture process, then the capture user for the capture process calls the transformation function.

• If a transformation is specified for a rule used by a synchronous capture, then the capture user for the synchronous capture calls the transformation function.

• If a transformation is specified for a rule used by a propagation, then the owner of the source queue for the propagation calls the transformation function.

• If a transformation is specified on a rule used by an apply process, then the apply user for the apply process calls the transformation function.

• If a transformation is specified on a rule used by a messaging client, then the user who invokes the messaging client calls the transformation function.

Rule-Based Transformations and Capture Processes

For a transformation to be performed during capture by a capture process, a rule that is associated with a rule-based transformation in the positive rule set for the capture process must evaluate to TRUE for a particular change found in the redo log.

If the transformation is a declarative rule-based transformation, then Oracle transforms the captured LCR internally when the rule in a positive rule set evaluates to TRUE for the message. If the transformation is a custom rule-based transformation, then an action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION or STREAMS$_ARRAY_TRANS_FUNCTION is returned to the capture process when the rule in a positive rule set evaluates to TRUE for the captured LCR.

The capture process completes the following steps to perform a rule-based transformation:

1. Formats the change in the redo log into an LCR.

2. Converts the LCR into an ANYDATA object.

3. Transforms the LCR. If the transformation is a declarative rule-based transformation, then Oracle transforms the ANYDATA object internally based on the specifications of the declarative transformation. If the transformation is a custom rule-based transformation, then the capture user for the capture process runs the PL/SQL function in the name-value pair to transform the ANYDATA object.

4. Enqueues one or more transformed ANYDATA objects into the queue associated with the capture process, or discards the LCR if an array that contains zero elements is returned by the transformation function.

All actions are performed by the capture user for the capture process. Figure 6-1 shows a transformation during capture by a capture process.

Figure 6-1 Transformation During Capture by a Capture Process

Description of "Figure 6-1 Transformation During Capture by a Capture Process"

For example, if an LCR is transformed during capture by a capture process, then the transformed LCR is enqueued into the queue used by the capture process. Therefore, if such a captured LCR is propagated from the dbs1.example.com database to the dbs2.example.com and the dbs3.example.com databases, then the queues at dbs2.example.com and dbs3.example.com will contain the transformed LCR after propagation.

The advantages of performing transformations during capture by a capture process are the following:

• Security can be improved if the transformation removes or changes private information, because this private information does not appear in the source queue and is not propagated to any destination queue.

• Space consumption can be reduced, depending on the type of transformation performed. For example, a transformation that reduces the amount of data results in less data to enqueue, propagate, and apply.

• Transformation overhead is reduced when there are multiple destinations for a transformed LCR, because the transformation is performed only once at the source, not at multiple destinations.

• A capture process transformation can transform a single message into multiple messages.

The possible disadvantages of performing transformations during capture by a capture process are the following:

• The transformation overhead occurs in the source database if the capture process is a local capture process. However, if the capture process is a downstream capture process, then this overhead occurs at the downstream database, not at the source database.

• All sites receive the transformed LCR.

Rule-Based Transformations and Synchronous Captures

For a transformation to be performed during capture by a synchronous capture, a rule that is associated with a rule-based transformation in the positive rule set for the synchronous capture must evaluate to TRUE for a particular DML change made to a table.

If the transformation is a declarative rule-based transformation, then Oracle transforms the persistent LCR internally when the rule in a positive rule set evaluates to TRUE for the message. If the transformation is a custom rule-based transformation, then an action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the capture process when the rule in a positive rule set evaluates to TRUE for the persistent LCR.

The synchronous capture completes the following steps to perform a rule-based transformation:

1. Formats the change in the redo log into a row LCR.

2. Converts the row LCR into an ANYDATA object.

3. Transforms the LCR. If the transformation is a declarative rule-based transformation, then Oracle transforms the ANYDATA object internally based on the specifications of the declarative transformation. If the transformation is a custom rule-based transformation, then the capture user for the synchronous capture runs the PL/SQL function in the name-value pair to transform the ANYDATA object.

4. Enqueues the transformed ANYDATA object into the queue associated with the synchronous capture.

All actions are performed by the capture user for the synchronous capture. Figure 6-2 shows a transformation during capture.

Figure 6-2 Transformation During Capture by a Synchronous Capture

Description of "Figure 6-2 Transformation During Capture by a Synchronous Capture"

For example, if a row LCR is transformed during capture by a synchronous capture, then the transformed row LCR is enqueued into the queue used by the synchronous capture. Therefore, if such a captured LCR is propagated from the dbs1.example.com database to the dbs2.example.com and the dbs3.example.com databases, then the queues at dbs2.example.com and dbs3.example.com will contain the transformed row LCR after propagation.

The advantages of performing transformations during capture by a synchronous capture are the following:

• Security can be improved if the transformation removes or changes private information, because this private information does not appear in the source queue and is not propagated to any destination queue.

• Space consumption can be reduced, depending on the type of transformation performed. For example, a transformation that reduces the amount of data results in less data to enqueue, propagate, and apply.

• Transformation overhead is reduced when there are multiple destinations for a transformed row LCR, because the transformation is performed only once at the source, not at multiple destinations.

The possible disadvantages of performing transformations during capture by a synchronous capture are the following:

• The transformation overhead occurs in the source database.

• All sites receive the transformed LCR.

Rule-Based Transformations and Propagations

For a transformation to be performed during propagation, a rule that is associated with a rule-based transformation in the positive rule set for the propagation must evaluate to TRUE for a message in the source queue for the propagation. This message can be a captured LCR, buffered LCR, buffered user message, persistent LCR, and persistent user message.

If the transformation is a declarative rule-based transformation, then Oracle transforms the message internally when the rule in a positive rule set evaluates to TRUE for the message. If the transformation is a custom rule-based transformation, then an action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the propagation when the rule in a positive rule set evaluates to TRUE for the message.

The propagation completes the following steps to perform a rule-based transformation:

1. Starts dequeuing the message from the source queue.

2. Transforms the message. If the transformation is a declarative rule-based transformation, then Oracle transforms the message internally based on the specifications of the declarative transformation. If the transformation is a custom rule-based transformation, then the source queue owner runs the PL/SQL function in the name-value pair to transform the message.

3. Completes dequeuing the transformed message.

4. Propagates the transformed message to the destination queue.

Figure 6-3 shows a transformation during propagation.

Figure 6-3 Transformation During Propagation

Description of "Figure 6-3 Transformation During Propagation"

For example, suppose you use a rule-based transformation for a propagation that propagates messages from the dbs1.example.com database to the dbs2.example.com database, but you do not use a rule-based transformation for a propagation that propagates messages from the dbs1.example.com database to the dbs3.example.com database.

In this case, a message in the queue at dbs1.example.com can be transformed before it is propagated to dbs2.example.com, but the same message can remain in its original form when it is propagated to dbs3.example.com. In this case, after propagation, the queue at dbs2.example.com contains the transformed message, and the queue at dbs3.example.com contains the original message.

The advantages of performing transformations during propagation are the following:

• Security can be improved if the transformation removes or changes private information before messages are propagated.

• Some destination queues can receive a transformed message, while other destination queues can receive the original message.

• Different destinations can receive different variations of the same transformed message.

The possible disadvantages of performing transformations during propagation are the following:

• Once a message is transformed, any database to which it is propagated after the first propagation receives the transformed message. For example, if dbs2.example.com propagates the message to dbs4.example.com, then dbs4.example.com receives the transformed message.

• When the first propagation in a directed network performs the transformation, and a local capture process captured the message, the transformation overhead occurs on the source database. However, if the capture process is a downstream capture process, then this overhead occurs at the downstream database, not at the source database.

• When the first propagation in a directed network performs the transformation, and a synchronous capture captured the message, the transformation overhead occurs on the source database.

• The same transformation can be done multiple times on a message when different propagations send the message to multiple destination databases.

Rule-Based Transformations and an Apply Process

For a transformation to be performed during apply, a rule that is associated with a rule-based transformation in the positive rule set for the apply process must evaluate to TRUE for a message in the queue for the apply process. This message can be a captured LCR, a persistent LCR, or a persistent user message.

If the transformation is a declarative rule-based transformation, then Oracle transforms the message internally when the rule in a positive rule set evaluates to TRUE for the message. If the transformation is a custom rule-based transformation, then an action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the apply process when the rule in a positive rule set evaluates to TRUE for the message.

The apply process completes the following steps to perform a rule-based transformation:

1. Starts to dequeue the message from the queue.

2. Transforms the message. If the transformation is a declarative rule-based transformation, then Oracle transforms the message internally based on the specifications of the declarative transformation. If the transformation is a custom rule-based transformation, then the apply user runs the PL/SQL function in the name-value pair to transform the message.

3. Completes dequeuing the transformed message.

4. Applies the transformed message, which can entail changing database objects at the destination database or sending the transformed message to an apply handler.

All actions are performed by the apply user.

Figure 6-4 shows a transformation during apply.

Figure 6-4 Transformation During Apply

Description of "Figure 6-4 Transformation During Apply"

For example, suppose a message is propagated from the dbs1.example.com database to the dbs2.example.com database in its original form. When the apply process dequeues the message at dbs2.example.com, the message is transformed.

The possible advantages of performing transformations during apply are the following:

• Any database to which the message is propagated after the first propagation can receive the message in its original form. For example, if dbs2.example.com propagates the message to dbs4.example.com, then dbs4.example.com can receive the original message.

• The transformation overhead does not occur on the source database when the source and destination database are different.

The possible disadvantages of performing transformations during apply are the following:

• Security might be a concern if the messages contain private information, because all databases to which the messages are propagated receive the original messages.

• The same transformation can be done multiple times when multiple destination databases need the same transformation.

Rule-Based Transformations and a Messaging Client

For a transformation to be performed during dequeue by a messaging client, a rule that is associated with a rule-based transformation in the positive rule set for the messaging client must evaluate to TRUE for a message in the queue for the messaging client.

If the transformation is a declarative rule-based transformation, then Oracle transforms the message internally when the rule in a positive rule set evaluates to TRUE for the message. If the transformation is a custom rule-based transformation, then an action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the messaging client when the rule in a positive rule set evaluates to TRUE for the message.

The messaging client completes the following steps to perform a rule-based transformation:

1. Starts to dequeue the message from the queue.

2. Transforms the message. If the transformation is a declarative rule-based transformation, then the message must be a persistent LCR, and Oracle transforms the row LCR internally based on the specifications of the declarative transformation. If the transformation is a custom rule-based transformation, then the message can be a persistent LCR or a persistent user message. The user who invokes the messaging client runs the PL/SQL function in the name-value pair to transform the message during dequeue.

3. Completes dequeuing the transformed message.

All actions are performed by the user who invokes the messaging client.

Figure 6-5 shows a transformation during messaging client dequeue.

Figure 6-5 Transformation During Messaging Client Dequeue

Description of "Figure 6-5 Transformation During Messaging Client Dequeue"

For example, suppose a message is propagated from the dbs1.example.com database to the dbs2.example.com database in its original form. When the messaging client dequeues the message at dbs2.example.com, the message is transformed.

One possible advantage of performing transformations during dequeue in a messaging environment is that any database to which the message is propagated after the first propagation can receive the message in its original form. For example, if dbs2.example.com propagates the message to dbs4.example.com, then dbs4.example.com can receive the original message.

The possible disadvantages of performing transformations during dequeue in a messaging environment are the following:

• Security might be a concern if the messages contain private information, because all databases to which the messages are propagated receive the original messages.

• The same transformation can be done multiple times when multiple destination databases need the same transformation.

Multiple Rule-Based Transformations

You can transform a message during capture, propagation, apply, or dequeue, or during any combination of capture, propagation, apply, and dequeue. For example, if you want to hide sensitive data from all recipients, then you can transform a message during capture. If some recipients require additional custom transformations, then you can transform the previously transformed message during propagation, apply, or dequeue.

Declarative Rule-Based Transformation Ordering

If more than one declarative rule-based transformation is specified for a single rule, then Oracle must perform the transformations in a particular order. You can use the default ordering for declarative transformations, or you can specify the order.

This section contains the following topics:

• Default Declarative Transformation Ordering

• User-Specified Declarative Transformation Ordering