As stated in the Oracle Streams manuals in 9iR2 and 10gR1, the apply process is not able to detect conflicts on LOB columns and update conflict resolution methods are not supported for LOB or LONG columns. However, it is possible to configure apply handlers to achieve the same result.
The following sample code demonstrates a technique to apply a row change that includes an update to a LOB column after a conflict has been detected in one of the scalar (i.e., non-LOB/LONG) columns for that row. This example assumes that the LOB changes are performed with SQL DML statements and not the DBMS_LOB procedures.
The following general steps are necessary to implement this
technique:
1. Create a tracking table for the replicated table with LOB columns at the target (apply) database. This tracking table is used to identify transactions for the replicated table that are not applied when the LCR timestamp is less than or equal to the timestamp in the existing row of the table.
2. Create an error handler procedure for UPDATEs performed on the replicated table. The error handler will check for the error 1403 (No Data Found). The resolving column is identified within the column, typically a timestamp or date column. Using the resolving column timestamp, determine whether the row should be applied (timestamp > existing value) or not. If the timestamp is less than the existing value then the tracking table is updated with information about transactions and LCRs that are NOT to be applied. However, if the timestamp is equal to the existing value, (the criteria is not deterministic), the error 1403 will be returned. If there is any possibility that the timestamp could be equal to the existing value, another criteria should be used or added. Otherwise error 1403 will cause the transaction to be placed in the apply error queue and the resolution will need to be done manually. LCRs that should be applied are modified to eliminate the conflicting column information and applied to the table.
If the error was not 1403, then reapply the LCR to return the error.
3. Create a dml handler for LOB_UPDATEs for the replicated table. The dml handler will handle all lob column changes for every LCR. The tracking table will identify the transactions where an LCR should not be applied because the LCR timestamp precedes the existing data timestamp.
The dml handler checks if the row exists in the table using the primary key. If the row exists, a further check is performed to ensure that the scalar columns (including the timestamp column) in the existing row exactly match the NEW values specified in the LCR. If this is true, then the update is applied. If the values do not match, then an update is made to the tracking table. If the tracking table update is successful, (i.e., the transaction_id is identified in the tracking table) the LCR is skipped. If the tracking table has not identified this transaction to be skipped, then an ORA-1403 is returned and the transaction is placed in the apply error queue for manual resolution.
If DBMS_LOB procedures are used, the LOB_UPDATE handler will have the same behavior. The LOB_UPDATE LCR will be applied if the checks for row existence both succeed (primary key and all scalar columns). If the checks fail but the entry is successfully updated in the tracking table the LCR will be skipped. An ORA-1403 will be returned if the tracking table update is unsuccessful. It is best in a scenario where conflict resolution is needed on a table with LOBs, that UPDATE rather than DBMS_LOB is used to update the LOBs. If UPDATE is used, then the timestamp can be updated, allowing conflict resolution and preventing LOB modifications from being skipped or receiving an ORA-1403 unintentionally. Since the DBMS_LOB procedure does not update the scalars, allowing conflict resolution, divergence on the LOB column is possible between sites.
4. Configure Streams to use the error handler procedure (Step 2) for UPDATE operations and the dml handler procedure (Step 3) for LOB_UPDATE operations on the specific replicated table.
1. In 10gR2, a new feature LOB ASSEMBLY allows some simplification of conflict resolution. With ASSEMBLE LOBs, the conflict resolution mechanism can be used and a tracking table is not necessary.
2. Create a simple error handler procedure that returns error 1403. If it receives error 1403 and reapplies the LCR for any other error. A handler must be defined to turn on LOB ASSEMBLY.
3. If there is any possibility that DBMS_LOB operations will be done on this table, then a dml handler for LOB_UPDATEs should be specified. As in the non-assembled case, the handler will check whether row exists (primary key and scalar columns). If the row does not exist, error1403 will be returned. While the possibility of divergence for the LOB column still exists, this will prevent unwanted LOB updates.
4. Configure Streams to use conflict resolution, the error handler procedure (Step 2) for UPDATE operations (turns on LOB ASSEMBLY) and the recommended dml handler procedure (Step 3) for LOB_UPDATE operations on the specific replicated table.
Downloadable zip file of this webpage and the sql file
Restrictions:
1. Modifications to LOB columns must be performed with DML operations (Insert/Update/Delete) and at least one scalar (non-LOB) column is updated, including the resolution column. As explained above there are potentially unintended skipped and failed DBMS_LOB operations which could lead to divergence between sites.
2. A single timestamp column is used to determine whether the change is applied or not. If this column is not available in all LCRs, the update to the LOB will not occur.
3. A primary key column exists on both the source and target (replicated) tables.
Note: The handler procedures reference a specific replicated table and tracking table (when LOB ASSEMBLY is not used). Be sure to modify the explicit references to these tables for your particular tables.