1. In SQL*Plus, connect to the database as the Oracle Streams administrator.

   See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

2. Create a PL/SQL procedure named execute_row_lcr that executes a row LCR:

   CREATE OR REPLACE PROCEDURE execute_row_lcr(
                    source_dbname  VARCHAR2,
                    cmd_type       VARCHAR2,
                    obj_owner      VARCHAR2,
                    obj_name       VARCHAR2,
                    old_vals       SYS.LCR$_ROW_LIST,
                    new_vals       SYS.LCR$_ROW_LIST) as
     xrow_lcr  SYS.LCR$_ROW_RECORD;
   BEGIN
     -- Construct the row LCR based on information passed to procedure
     xrow_lcr := SYS.LCR$_ROW_RECORD.CONSTRUCT(
       source_database_name => source_dbname,
       command_type         => cmd_type,
       object_owner         => obj_owner,
       object_name          => obj_name,
       old_values           => old_vals,
       new_values           => new_vals);
     -- Execute the row LCR
     xrow_lcr.EXECUTE(FALSE);
   END execute_row_lcr;
   /
   

3. Create a PL/SQL procedure named insert_job_lcr that executes a row LCR that inserts a row into the hr.jobs table:

   CREATE OR REPLACE PROCEDURE insert_job_lcr(
                    j_id     VARCHAR2,
                    j_title  VARCHAR2,
                    min_sal  NUMBER,
                    max_sal  NUMBER) AS
     xrow_lcr   SYS.LCR$_ROW_RECORD;
     col1_unit  SYS.LCR$_ROW_UNIT;
     col2_unit  SYS.LCR$_ROW_UNIT;
     col3_unit  SYS.LCR$_ROW_UNIT;
     col4_unit  SYS.LCR$_ROW_UNIT;
     newvals    SYS.LCR$_ROW_LIST;
   BEGIN
     col1_unit := SYS.LCR$_ROW_UNIT(
       'job_id', 
       ANYDATA.ConvertVarchar2(j_id),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     col2_unit := SYS.LCR$_ROW_UNIT(
       'job_title', 
       ANYDATA.ConvertVarchar2(j_title),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     col3_unit := SYS.LCR$_ROW_UNIT(
       'min_salary', 
       ANYDATA.ConvertNumber(min_sal),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     col4_unit := SYS.LCR$_ROW_UNIT(
       'max_salary', 
       ANYDATA.ConvertNumber(max_sal),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     newvals := SYS.LCR$_ROW_LIST(col1_unit,col2_unit,col3_unit,col4_unit);
     -- Execute the row LCR
     execute_row_lcr(
       source_dbname => 'DB1.EXAMPLE.COM',
       cmd_type      => 'INSERT',
       obj_owner     => 'HR',
       obj_name      => 'JOBS',
       old_vals      => NULL,
       new_vals      => newvals);  
   END insert_job_lcr;
   /
   

4. Create a PL/SQL procedure named update_max_salary_lcr that executes a row LCR that updates the max_salary value for a row in the hr.jobs table:

   CREATE OR REPLACE PROCEDURE update_max_salary_lcr(
                    j_id         VARCHAR2,
                    old_max_sal NUMBER,
                    new_max_sal NUMBER) AS
     xrow_lcr      SYS.LCR$_ROW_RECORD;
     oldcol1_unit  SYS.LCR$_ROW_UNIT;
     oldcol2_unit  SYS.LCR$_ROW_UNIT;
     newcol1_unit  SYS.LCR$_ROW_UNIT;
     oldvals       SYS.LCR$_ROW_LIST;
     newvals       SYS.LCR$_ROW_LIST;
   BEGIN
     oldcol1_unit := SYS.LCR$_ROW_UNIT(
       'job_id', 
       ANYDATA.ConvertVarchar2(j_id),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     oldcol2_unit := SYS.LCR$_ROW_UNIT(
       'max_salary', 
       ANYDATA.ConvertNumber(old_max_sal),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     oldvals := SYS.LCR$_ROW_LIST(oldcol1_unit,oldcol2_unit);
     newcol1_unit := SYS.LCR$_ROW_UNIT(
       'max_salary', 
       ANYDATA.ConvertNumber(new_max_sal),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     newvals := SYS.LCR$_ROW_LIST(newcol1_unit);
     -- Execute the row LCR
     execute_row_lcr(
       source_dbname => 'DB1.EXAMPLE.COM',
       cmd_type      => 'UPDATE',
       obj_owner     => 'HR',
       obj_name      => 'JOBS',
       old_vals      => oldvals,
       new_vals      => newvals);  
   END update_max_salary_lcr;
   /
   

5. Create a PL/SQL procedure named delete_job_lcr that executes a row LCR that deletes a row from the hr.jobs table:

   CREATE OR REPLACE PROCEDURE delete_job_lcr(j_id VARCHAR2) AS
     xrow_lcr   SYS.LCR$_ROW_RECORD;
     col1_unit  SYS.LCR$_ROW_UNIT;
     oldvals    SYS.LCR$_ROW_LIST;
   BEGIN
     col1_unit := SYS.LCR$_ROW_UNIT(
       'job_id',
       ANYDATA.ConvertVarchar2(j_id),
       DBMS_LCR.NOT_A_LOB,
       NULL,
       NULL);
     oldvals := SYS.LCR$_ROW_LIST(col1_unit); 
     -- Execute the row LCR
     execute_row_lcr(
       source_dbname => 'DB1.EXAMPLE.COM',
       cmd_type      => 'DELETE',
       obj_owner     => 'HR',
       obj_name      => 'JOBS',
       old_vals      => oldvals,
       new_vals      => NULL);
   END delete_job_lcr;
   /
   

6. Insert a row into the hr.jobs table using the insert_job_lcr procedure:

   EXEC insert_job_lcr('BN_CNTR','BEAN COUNTER',5000,10000);
   

7. Select the inserted row in the hr.jobs table:

   SELECT * FROM hr.jobs WHERE job_id = 'BN_CNTR';
   
   JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
   ---------- ----------------------------------- ---------- ----------
   BN_CNTR    BEAN COUNTER                              5000      10000
   

8. Update the max_salary value for the row inserted into the hr.jobs table in Step 6 using the update_max_salary_lcr procedure:

   EXEC update_max_salary_lcr('BN_CNTR',10000,12000);
   

9. Select the updated row in the hr.jobs table:

   SELECT * FROM hr.jobs WHERE job_id = 'BN_CNTR';
   
   JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
   ---------- ----------------------------------- ---------- ----------
   BN_CNTR    BEAN COUNTER                              5000      12000
   

10. Delete the row inserted into the hr.jobs table in Step 6 using the delete_job_lcr procedure:

    EXEC delete_job_lcr('BN_CNTR');
    

11. Select the deleted row in the hr.jobs table:

    SELECT * FROM hr.jobs WHERE job_id = 'BN_CNTR';
    
    no rows selected
    

The following are issues to consider when you use LOB assembly:

• To use a DML or error handler to process assembled LOBs at multiple destination databases, LOB assembly must assemble the LOBs separately on each destination database.

• Row LCRs captured on a database running a release of Oracle before Oracle Database 10g Release 2 cannot be assembled by LOB assembly.

• Row LCRs captured on a database running Oracle Database 10g Release 2 or later with a compatibility level lower than 10.2.0 cannot be assembled by LOB assembly.

• The compatibility level of the database running an apply handler must be 10.2.0 or higher to specify LOB assembly for the apply handler.

• Row LCRs from a table containing any LONG or LONG RAW columns cannot be assembled by LOB assembly.

• The SET_ENQUEUE_DESTINATION and the SET_EXECUTE procedures in the DBMS_APPLY_ADM package always operate on original, nonassembled row LCRs. Therefore, for row LCRs that contain LOB columns, the original, nonassembled row LCRs are enqueued or executed, even if these row LCRs are assembled separately for an apply handler at the destination database.

• If rule-based transformations were performed on row LCRs that contain LOB columns during capture, propagation, or apply, then an apply handler operates on the transformed row LCRs. If there are LONG or LONG RAW columns at a source database, and a rule-based transformation uses the CONVERT_LONG_TO_LOB_CHUNK member function for row LCRs to convert them to LOBs, then LOB assembly can be enabled for apply handlers that operate on these row LCRs.

• When a row LCR contains one or more XMLType columns, any XMLType and LOB columns in the row LCR are always assembled, even if the assemble_lobs parameter is set to FALSE for a DML or error handler.

The following requirements must be met when you are constructing LCRs with LOB columns and when you are processing LOB columns with a DML or error handler that has LOB assembly disabled:

• Do not modify LOB column data in a row LCR with a procedure DML handler or error handler that has LOB assembly disabled. However, you can modify non-LOB columns in row LCRs with a DML or error handler.

• Do not allow LCRs from a table that contains LOB columns to be processed by an apply handler that is invoked only for specific operations. For example, an apply handler that is invoked only for INSERT operations should not process LCRs from a table with one or more LOB columns.

• The data portion of the LCR LOB column must be of type VARCHAR2 or RAW. A VARCHAR2 is interpreted as a CLOB, and a RAW is interpreted as a BLOB.

• A LOB column in a user-constructed row LCR must be either a BLOB or a fixed-width CLOB. You cannot construct a row LCR with the following types of LOB columns: NCLOB or variable-width CLOB.

• LOB WRITE, LOB ERASE, and LOB TRIM are the only valid command types for out-of-line LOBs.

• For LOB WRITE, LOB ERASE, and LOB TRIM LCRs, the old_values collection should be empty or NULL, and new_values should not be empty.

• The lob_offset should be a valid value for LOB WRITE and LOB ERASE LCRs. For all other command types, lob_offset should be NULL, under the assumption that LOB chunks for that column will follow.

• The lob_operation_size should be a valid value for LOB ERASE and LOB TRIM LCRs. For all other command types, lob_operation_size should be NULL.

• LOB TRIM and LOB ERASE are valid command types only for an LCR containing a LOB column with lob_information set to LAST_LOB_CHUNK.

• LOB WRITE is a valid command type only for an LCR containing a LOB column with lob_information set to LAST_LOB_CHUNK or LOB_CHUNK.

• For LOBs with lob_information set to NULL_LOB, the data portion of the column should be a NULL of VARCHAR2 type (for a CLOB) or a NULL of RAW type (for a BLOB). Otherwise, it is interpreted as a non-NULL inline LOB column.

• Only one LOB column reference with one new chunk is allowed for each LOB WRITE, LOB ERASE, and LOB TRIM LCR.

• The new LOB chunk for a LOB ERASE and a LOB TRIM LCR should be a NULL value encapsulated in an ANYDATA.

An apply process performs all validation of these requirements. If these requirements are not met, then a row LCR containing LOB columns cannot be applied by an apply process nor processed by an apply handler. In this case, the LCR is moved to the error queue with the rest of the LCRs in the same transaction.

The following requirements must be met when you are processing LOB columns with a DML or error handler that has LOB assembly enabled:

• Do not use the following row LCR member procedures on LOB columns in row LCRs that contain assembled LOBs:

  • SET_LOB_INFORMATION

  • SET_LOB_OFFSET

  • SET_LOB_OPERATION_SIZE

  An error is raised if one of these procedures is used on a LOB column in a row LCR.

• Row LCRs constructed by LOB assembly cannot be enqueued by a procedure DML handler or error handler. However, even when LOB assembly is enabled for one or more handlers at a destination database, the original, nonassembled row LCRs with LOB columns can be enqueued using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package.

An apply process performs all validation of these requirements. If these requirements are not met, then a row LCR containing LOB columns cannot be applied by an apply process nor processed by an apply handler. In this case, the LCR is moved to the error queue with the rest of the LCRs in the same transaction. For row LCRs with LOB columns, the original, nonassembled row LCRs are placed in the error queue.

The following requirements must be met when you are processing row LCRs that contain LOB columns with a rule-based transformation:

• Do not modify LOB column data in a row LCR with a custom rule-based transformation. However, a custom rule-based transformation can modify non-LOB columns in row LCRs that contain LOB columns.

• You cannot use the following row LCR member procedures on a LOB column when you are processing a row LCR with a custom rule-based transformation:

  • ADD_COLUMN

  • SET_LOB_INFORMATION

  • SET_LOB_OFFSET

  • SET_LOB_OPERATION_SIZE

  • SET_VALUE

  • SET_VALUES

• A declarative rule-based transformation created by the ADD_COLUMN procedure in the DBMS_STREAMS_ADM package cannot add a LOB column to a row LCR.

• Rule-based transformation functions that are run on row LCRs with LOB columns must be deterministic, so that all row LCRs corresponding to the row change are transformed in the same way.

• Do not allow LCRs from a table that contains LOB columns to be processed by an a custom rule-based transformation that is invoked only for specific operations. For example, a custom rule-based transformation that is invoked only for INSERT operations should not process LCRs from a table with one or more LOB columns.