1. Replication Administrator’s Guide
  2. Administering Oracle Streams Replication
  3. Managing Logical Change Records (LCRs)

14 Managing Logical Change Records (LCRs)

This chapter contains instructions for managing logical change records (LCRs) in an Oracle Streams replication environment.

This chapter contains these topics:

See Also:

Oracle Database PL/SQL Packages and Types Reference and Oracle Streams Concepts and Administration for more information about LCRs

14.1 Requirements for Managing LCRs

This section describes requirements for creating or modifying logical change records (LCRs). You can create an LCR using a constructor for an LCR type, and then enqueue the LCR into an persistent queue portion of an ANYDATA queue. Such an LCR is a persistent LCR.

Also, you can modify an LCR using an apply handler or a rule-based transformation. You can modify captured LCRs or persistent LCRs.

Ensure that you meet the following requirements when you manage an LCR:

  • If you create or modify a row LCR, then ensure that the command_type attribute is consistent with the presence or absence of old column values and the presence or absence of new column values.

  • If you create or modify a DDL LCR, then ensure that the ddl_text is consistent with the base_table_name, base_table_owner, object_type, object_owner, object_name, and command_type attributes.

  • The following data types are allowed for columns in a user-constructed row LCR:

    • CHAR

    • VARCHAR2

    • NCHAR

    • NVARCHAR2

    • NUMBER

    • DATE

    • BINARY_FLOAT

    • BINARY_DOUBLE

    • RAW

    • TIMESTAMP

    • TIMESTAMP WITH TIME ZONE

    • TIMESTAMP WITH LOCAL TIME ZONE

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND

    These data types are the only data types allowed for columns in a user-constructed row LCR. However, you can use certain techniques to construct LCRs that contain LOB information. Also, LCRs captured by a capture process support more data types, while LCRs captured by a synchronous capture support fewer data types.

See Also:

14.2 Constructing and Enqueuing LCRs

Use the following LCR constructors to create LCRs:

  • To create a row LCR that contains a change to a row that resulted from a data manipulation language (DML) statement, use the SYS.LCR$_ROW_RECORD constructor.

  • To create a DDL LCR that contains a data definition language change, use the SYS.LCR$_DDL_RECORD constructor. Ensure that the DDL text specified in the ddl_text attribute of each DDL LCR conforms to Oracle SQL syntax.

The following example creates a queue in an Oracle database and an apply process associated with the queue. Next, it creates a PL/SQL procedure that constructs a row LCR based on information passed to it and enqueues the row LCR into the queue. This example assumes that you have configured an Oracle Streams administrator named strmadmin and granted this administrator DBA role.

Complete the following steps:

  1. In SQL*Plus, connect to the database as an administrative user.

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

  2. Grant the Oracle Streams administrator EXECUTE privilege on the DBMS_STREAMS_MESSAGING package. For example: 

    GRANT EXECUTE ON DBMS_STREAMS_MESSAGING TO strmadmin;

    Explicit EXECUTE privilege on the package is required because a procedure in the package is called within a PL/SQL procedure in Step 9. In this case, granting the privilege through a role is not sufficient.

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

  4. Create an ANYDATA queue in an Oracle database. 

    BEGIN 
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table          =>  'strm04_queue_table',
    storage_clause       =>  NULL,
    queue_name           =>  'strm04_queue');
END;
/

  5. Create an apply process at the Oracle database to receive messages in the queue. Ensure that the apply_captured parameter is set to FALSE when you create the apply process, because the apply process will be applying persistent LCRs, not captured LCRs. Also, ensure that the apply_user parameter is set to hr, because changes will be applied in to the hr.regions table, and the apply user must have privileges to make DML changes to this table. 

    BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
     queue_name      => 'strm04_queue',
     apply_name      => 'strm04_apply',
     apply_captured  => FALSE,
     apply_user      => 'hr');
END;
/

  6. Create a positive rule set for the apply process and add a rule that applies DML changes to the hr.regions table made at the dbs1.example.com source database. 

    BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.regions',
    streams_type        =>  'apply',
    streams_name        =>  'strm04_apply',
    queue_name          =>  'strm04_queue',
    include_dml         =>  TRUE,
    include_ddl         =>  FALSE,
    include_tagged_lcr  =>  FALSE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  TRUE);
END;
/

  7. Set the disable_on_error parameter for the apply process to n. 

    BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'strm04_apply', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/

  8. Start the apply process.

    EXEC DBMS_APPLY_ADM.START_APPLY('strm04_apply');

  9. Create a procedure called construct_row_lcr that constructs a row LCR and enqueues it into the queue created in Step 4. 

    CREATE OR REPLACE PROCEDURE construct_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
  row_lcr        SYS.LCR$_ROW_RECORD;
BEGIN
  -- Construct the LCR based on information passed to procedure
  row_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);
  -- Enqueue the created row LCR
  DBMS_STREAMS_MESSAGING.ENQUEUE(
    queue_name         =>  'strm04_queue',
    payload            =>  ANYDATA.ConvertObject(row_lcr));
END construct_row_lcr;
/

    Note:

    The application does not need to specify a transaction identifier or SCN when it creates an LCR because the apply process generates these values and stores them in memory. If a transaction identifier or SCN is specified in the LCR, then the apply process ignores it and assigns a new value.

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information about LCR constructors

  10. Create and enqueue LCRs using the construct_row_lcr procedure created in Step 5.

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

    2. Create a row LCR that inserts a row into the hr.regions table. 

      DECLARE
  newunit1  SYS.LCR$_ROW_UNIT;
  newunit2  SYS.LCR$_ROW_UNIT;
  newvals   SYS.LCR$_ROW_LIST;
BEGIN
  newunit1 := SYS.LCR$_ROW_UNIT(
    'region_id', 
    ANYDATA.ConvertNumber(5),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  newunit2 := SYS.LCR$_ROW_UNIT(
    'region_name', 
    ANYDATA.ConvertVarchar2('Moon'),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  newvals := SYS.LCR$_ROW_LIST(newunit1,newunit2);
construct_row_lcr(
  source_dbname  =>  'dbs1.example.com',
  cmd_type       =>  'INSERT',
  obj_owner      =>  'hr',
  obj_name       =>  'regions',
  old_vals       =>  NULL,
  new_vals       =>  newvals);
END;
/
COMMIT;

    3. In SQL*Plus, connect to the database as the hr user.

    4. Query the hr.regions table to view the applied row change. The row with a region_id of 5 should have Moon for the region_name. 

      SELECT * FROM hr.regions;

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

    6. Create a row LCR that updates a row in the hr.regions table. 

      DECLARE
  oldunit1  SYS.LCR$_ROW_UNIT;
  oldunit2  SYS.LCR$_ROW_UNIT;
  oldvals   SYS.LCR$_ROW_LIST;
  newunit1  SYS.LCR$_ROW_UNIT;
  newvals   SYS.LCR$_ROW_LIST;
BEGIN
  oldunit1 := SYS.LCR$_ROW_UNIT(
    'region_id', 
    ANYDATA.ConvertNumber(5),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  oldunit2 := SYS.LCR$_ROW_UNIT(
    'region_name', 
    ANYDATA.ConvertVarchar2('Moon'),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  oldvals := SYS.LCR$_ROW_LIST(oldunit1,oldunit2);
  newunit1 := SYS.LCR$_ROW_UNIT(
    'region_name', 
    ANYDATA.ConvertVarchar2('Mars'),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  newvals := SYS.LCR$_ROW_LIST(newunit1);
construct_row_lcr(
  source_dbname  =>  'dbs1.example.com',
  cmd_type       =>  'UPDATE',
  obj_owner      =>  'hr',
  obj_name       =>  'regions',
  old_vals       =>  oldvals,
  new_vals       =>  newvals);
END;
/
COMMIT;

    7. In SQL*Plus, connect to the database as the hr user.

    8. Query the hr.regions table to view the applied row change. The row with a region_id of 5 should have Mars for the region_name. 

      SELECT * FROM hr.regions;

    9. Create a row LCR that deletes a row from the hr.regions table. 

      DECLARE
  oldunit1  SYS.LCR$_ROW_UNIT;
  oldunit2  SYS.LCR$_ROW_UNIT;
  oldvals   SYS.LCR$_ROW_LIST;
BEGIN
  oldunit1 := SYS.LCR$_ROW_UNIT(
    'region_id', 
    ANYDATA.ConvertNumber(5),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  oldunit2 := SYS.LCR$_ROW_UNIT(
    'region_name',
    ANYDATA.ConvertVarchar2('Mars'),
    DBMS_LCR.NOT_A_LOB,
    NULL,
    NULL);
  oldvals := SYS.LCR$_ROW_LIST(oldunit1,oldunit2);
construct_row_lcr(
  source_dbname  =>  'dbs1.example.com',
  cmd_type       =>  'DELETE',
  obj_owner      =>  'hr',
  obj_name       =>  'regions',
  old_vals       =>  oldvals,
  new_vals       =>  NULL);
END;
/
COMMIT;

    10. In SQL*Plus, connect to the database as the hr user.

    11. Query the hr.regions table to view the applied row change. The row with a region_id of 5 should have been deleted. 

      SELECT * FROM hr.regions;

14.3 Executing LCRs

There are separate EXECUTE member procedures for row LCRs and DDL LCRs. These member procedures execute an LCR under the security domain of the current user. When an LCR is executed successfully, the change recorded in the LCR is made to the local database. The following sections describe executing row LCRs and DDL LCRs:

14.3.1 Executing Row LCRs

The EXECUTE member procedure for row LCRs is a subprogram of the LCR$_ROW_RECORD type. When the EXECUTE member procedure is run on a row LCR, the row LCR is executed. If the row LCR is executed by an apply process, then any apply process handlers that would be run for the LCR are not run.

The EXECUTE member procedure can be run on a row LCR under any of the following conditions:

  • The LCR is being processed by an apply handler.

  • The LCR is in a queue and was last enqueued by an apply process, an application, or a user.

  • The LCR has been constructed using the LCR$_ROW_RECORD constructor function but has not been enqueued.

  • The LCR is in the error queue.

When you run the EXECUTE member procedure on a row LCR, the conflict_resolution parameter controls whether conflict resolution is performed. Specifically, if the conflict_resolution parameter is set to TRUE, then any conflict resolution defined for the table being changed is used to resolve conflicts resulting from the execution of the LCR. If the conflict_resolution parameter is set to FALSE, then conflict resolution is not used. If the conflict_resolution parameter is not set or is set to NULL, then an error is raised.

Note:

A custom rule-based transformation should not run the EXECUTE member procedure on a row LCR. Doing so could execute the row LCR outside of its transactional context.

See Also:

14.3.1.1 Example of Constructing and Executing Row LCRs

The example in this section creates PL/SQL procedures to insert, update, and delete rows in the hr.jobs table by constructing and executing row LCRs. The row LCRs are executed without being enqueued or processed by an apply process. This example assumes that you have configured an Oracle Streams administrator named strmadmin and granted this administrator DBA role.

Complete the following steps:

  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

14.3.2 Executing DDL LCRs

The EXECUTE member procedure for DDL LCRs is a subprogram of the LCR$_DDL_RECORD type. When the EXECUTE member procedure is run on a DDL LCR, the LCR is executed, and any apply process handlers that would be run for the LCR are not run. The EXECUTE member procedure for DDL LCRs can be invoked only in an apply handler for an apply process.

All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

See Also:

14.4 Managing LCRs Containing LOB Columns

LOB data types can be present in row LCRs captured by a capture process, but these data types are represented by other data types. LOB data types cannot be present in row LCRs captured by synchronous captures. Certain LOB data types cannot be present in row LCRs constructed by users. Table 14-1 shows the LCR representation for these data types and whether these data types can be present in row LCRs.

Table 14-1 LOB Data Type Representations in Row LCRs

Data Type Row LCR Representation Can Be Present in a Row LCR Captured by a Capture Process? Can Be Present in a Row LCR Captured by a Synchronous Capture? Can Be Present in a Row LCR Constructed by a User?

Fixed-width CLOB

VARCHAR2

Yes

No

Yes

Variable-width CLOB

RAW in AL16UTF16 character set

Yes

No

No

NCLOB

RAW in AL16UTF16 character set

Yes

No

No

BLOB

RAW

Yes

No

Yes

XMLType stored as CLOB

RAW

Yes

No

No

The following are general considerations for row changes involving LOB data types in an Oracle Streams environment:

  • A row change involving a LOB column can be captured, propagated, and applied as several row LCRs.

  • Rules used to evaluate these row LCRs must be deterministic, so that either all of the row LCRs corresponding to the row change cause a rule in a rule set to evaluate to TRUE, or none of them do.

The following sections contain information about the requirements you must meet when constructing or processing LOB columns, about apply process behavior for LCRs containing LOB columns, and about LOB assembly. There is also an example that constructs and enqueues LCRs containing LOB columns.

Note:

XMLType stored as a CLOB is deprecated in this release.

This section contains the following topics:

See Also:

14.4.1 Apply Process Behavior for Direct Apply of LCRs Containing LOBs

An apply process behaves in the following ways when it applies an LCR that contains a LOB column directly (without the use of an apply handler):

  • If an LCR whose command type is INSERT or UPDATE has a new LOB that contains data, and the lob_information is not DBMS_LCR.LOB_CHUNK or DBMS_LCR.LAST_LOB_CHUNK, then the data is applied.

  • If an LCR whose command type is INSERT or UPDATE has a new LOB that contains no data, and the lob_information is DBMS_LCR.EMPTY_LOB, then it is applied as an empty LOB.

  • If an LCR whose command type is INSERT or UPDATE has a new LOB that contains no data, and the lob_information is DBMS_LCR.NULL_LOB or DBMS_LCR.INLINE_LOB, then it is applied as a NULL.

  • If an LCR whose command type is INSERT or UPDATE has a new LOB and the lob_information is DBMS_LCR.LOB_CHUNK or DBMS_LCR.LAST_LOB_CHUNK, then any LOB value is ignored. If the command type is INSERT, then an empty LOB is inserted into the column under the assumption that LOB chunks will follow. If the command type is UPDATE, then the column value is ignored under the assumption that LOB chunks will follow.

  • If all of the new columns in an LCR whose command type is UPDATE are LOBs whose lob_information is DBMS_LCR.LOB_CHUNK or DBMS_LCR.LAST_LOB_CHUNK, then the update is skipped under the assumption that LOB chunks will follow.

  • For any LCR whose command type is UPDATE or DELETE, old LOB values are ignored.

14.4.2 LOB Assembly and Custom Apply of LCRs Containing LOB Columns

A change to a row in a table that does not include any LOB columns results in a single row LCR, but a change to a row that includes one or more LOB columns can result in multiple row LCRs. An apply process that does not send row LCRs that contain LOB columns to an apply handler can apply these row LCRs directly. However, before Oracle Database 10g Release 2, custom processing of row LCRs that contain LOB columns was complicated because apply handlers had to be configured to process multiple LCRs correctly for a single row change.

In Oracle Database 10g Release 2 and later, LOB assembly simplifies custom processing of row LCRs with LOB columns that were captured by a capture process. LOB assembly automatically combines multiple captured row LCRs resulting from a change to a row with LOB columns into one row LCR. An apply process passes this single row LCR to a DML handler or error handler when LOB assembly is enabled. Also, after LOB assembly, the LOB column values are represented by LOB locators, not by VARCHAR2 or RAW data type values. To enable LOB assembly for a procedure DML or error handler, set the assemble_lobs parameter to TRUE in the DBMS_APPLY_ADM.SET_DML_HANDLER procedure. LOB assembly is always enabled for statement DML handlers.

If the assemble_lobs parameter is set to FALSE for a DML or error handler, then LOB assembly is disabled and multiple row LCRs are passed to the handler for a change to a single row with LOB columns. Table 14-2 shows Oracle Streams behavior when LOB assembly is disabled. Specifically, the table shows the LCRs passed to a procedure DML handler or error handler resulting from a change to a single row with LOB columns.

Table 14-2 Oracle Streams Behavior with LOB Assembly Disabled

Original Row Change First Set of LCRs Second Set of LCRs Third Set of LCRs Final LCR

INSERT

One INSERT LCR

One or more LOB WRITE LCRs

One or more LOB TRIM LCRs

UPATE

UPDATE

One UPDATE LCR

One or more LOB WRITE LCRs

One or more LOB TRIM LCRs

UPATE

DELETE

One DELETE LCR

N/A

N/A

N/A

DBMS_LOB.WRITE

One or more LOB WRITE LCRs

N/A

N/A

N/A

DBMS_LOB.TRIM

One LOB TRIM LCR

N/A

N/A

N/A

DBMS_LOB.ERASE

One LOB ERASE LCR

N/A

N/A

N/A

Table 14-3 shows Oracle Streams behavior when LOB assembly is enabled. Specifically, the table shows the row LCR passed to a DML handler or error handler resulting from a change to a single row with LOB columns.

Table 14-3 Oracle Streams Behavior with LOB Assembly Enabled

Original Row Change Single LCR

INSERT

INSERT

UPDATE

UPDATE

DELETE

DELETE

DBMS_LOB.WRITE

LOB WRITE

DBMS_LOB.TRIM

LOB TRIM

DBMS_LOB.ERASE

LOB ERASE

When LOB assembly is enabled, a DML or error handler can modify LOB columns in a row LCR. Within the PL/SQL procedure specified as a DML or error handler, the preferred way to perform operations on a LOB is to use a subprogram in the DBMS_LOB package. If a row LCR contains a LOB column that is NULL, then a new LOB locator must replace the NULL. If a row LCR will be applied with the EXECUTE member procedure, then use the ADD_COLUMN, SET_VALUE, and SET_VALUES member procedures for row LCRs to make changes to a LOB.

When LOB assembly is enabled, LOB assembly converts non-NULL LOB columns in persistent LCRs into LOB locators. However, LOB assembly does not combine multiple persistent row LCRs into a single row LCR. For example, for persistent row LCRs, LOB assembly does not combine multiple LOB WRITE row LCRs following an INSERT row LCR into a single INSERT row LCR.

See Also:

14.4.2.1 LOB Assembly Considerations

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.

See Also:

14.4.2.2 LOB Assembly Example

This section contains an example that uses LOB assembly with a procedure DML handler. The example scenario involves a company that shares the oe.production_information table at several databases, but only some of these databases are used for the company's online World Wide Web catalog. The company wants to store a photograph of each product in the catalog databases, but, to save space, it does not want to store these photographs at the non catalog databases.

To accomplish this goal, a procedure DML handler at a catalog destination database can add a column named photo of data type BLOB to each INSERT and UPDATE made to the product_information table at a source database. The source database does not include the photo column in the table. The procedure DML handler is configured to use an existing photograph at the destination for updates and inserts.The company also wants to add a product_long_desc to the oe.product_information table at all databases. This table already has a product_description column that contains short descriptions. The product_long_desc column is of CLOB data type and contains detailed descriptions. The detailed descriptions are in English, but one of the company databases is used to display the company catalog in Spanish. Therefore, the procedure DML handler updates the product_long_desc column so that the long description is in the correct language.

The following steps configure a procedure DML handler that uses LOB assembly to accomplish the goals described previously:

Step 1   Add the photo Column to the product_information Table

The following statement adds the photo column to the product_information table at the destination database: 

ALTER TABLE oe.product_information ADD(photo BLOB);
Step 2   Add the product_long_desc Column to the product_information Table

The following statement adds the product_long_desc column to the product_information table at all of the databases in the environment: 

ALTER TABLE oe.product_information ADD(product_long_desc CLOB);
Step 3   Create the PL/SQL Procedure for the Procedure DML Handler

This example creates the convert_product_information procedure. This procedure will be used for the procedure DML handler. This procedure assumes that the following user-created PL/SQL subprograms exist:

  • The get_photo procedure obtains a photo in BLOB format from a URL or table based on the product_id and updates the BLOB locator that has been passed in as an argument.

  • The get_product_long_desc procedure has an IN argument of product_id and an IN OUT argument of product_long_desc and translates the product_long_desc into Spanish or obtains the Spanish replacement description and updates product_long_desc.

The following code creates the convert_product_information procedure: 

CREATE OR REPLACE PROCEDURE convert_product_information(in_any IN ANYDATA)
IS
  lcr                      SYS.LCR$_ROW_RECORD;
  rc                       PLS_INTEGER;
  product_id_anydata       ANYDATA;
  photo_anydata            ANYDATA;
  long_desc_anydata        ANYDATA;
  tmp_photo                BLOB;
  tmp_product_id           NUMBER;
  tmp_prod_long_desc       CLOB;
  tmp_prod_long_desc_src   CLOB;
  tmp_prod_long_desc_dest  CLOB;
  t                        PLS_INTEGER;
BEGIN
  -- Access LCR
  rc := in_any.GETOBJECT(lcr);
  product_id_anydata := lcr.GET_VALUE('OLD', 'PRODUCT_ID');
  t := product_id_anydata.GETNUMBER(tmp_product_id);
  IF ((lcr.GET_COMMAND_TYPE = 'INSERT') or (lcr.GET_COMMAND_TYPE = 'UPDATE')) THEN
    -- If there is no photo column in the lcr then it must be added
    photo_anydata := lcr.GET_VALUE('NEW', 'PHOTO');
    -- Check if photo has been sent and if so whether it is NULL
    IF (photo_anydata is NULL) THEN
      tmp_photo := NULL;
      ELSE
      t := photo_anydata.GETBLOB(tmp_photo);
    END IF;
    -- If tmp_photo is NULL then a new temporary LOB must be created and
    -- updated with the photo if it exists
    IF (tmp_photo is NULL) THEN
      DBMS_LOB.CREATETEMPORARY(tmp_photo, TRUE);
      get_photo(tmp_product_id, tmp_photo);
    END IF;
    -- If photo column did not exist then it must be added
    IF (photo_anydata is NULL) THEN
      lcr.ADD_COLUMN('NEW', 'PHOTO', ANYDATA.CONVERTBLOB(tmp_photo));
      -- Else the existing photo column must be set to the new photo
      ELSE
        lcr.SET_VALUE('NEW', 'PHOTO', ANYDATA.CONVERTBLOB(tmp_photo));
    END IF;
    long_desc_anydata := lcr.GET_VALUE('NEW', 'PRODUCT_LONG_DESC');
    IF (long_desc_anydata is NULL) THEN
      tmp_prod_long_desc_src := NULL;
      ELSE
      t := long_desc_anydata.GETCLOB(tmp_prod_long_desc_src);
    END IF;
    IF (tmp_prod_long_desc_src IS NOT NULL) THEN
      get_product_long_desc(tmp_product_id, tmp_prod_long_desc);
    END IF;
    -- If tmp_prod_long_desc IS NOT NULL, then use it to update the LCR
    IF (tmp_prod_long_desc IS NOT NULL) THEN
      lcr.SET_VALUE('NEW', 'PRODUCT_LONG_DESC',
                    ANYDATA.CONVERTCLOB(tmp_prod_long_desc_dest));
    END IF;
  END IF;
  -- DBMS_LOB operations also are executed 
  -- Inserts and updates invoke all changes
  lcr.EXECUTE(TRUE);
END;
/
Step 4   Set the Procedure DML Handler for the Apply Process

This step sets the convert_product_information procedure as the procedure DML handler at the destination database for INSERT, UPDATE, and LOB_UPDATE operations. Notice that the assemble_lobs parameter is set to TRUE each time the SET_DML_HANDLER procedure is run. 

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'oe.product_information',
    object_type         => 'TABLE',
    operation_name      => 'INSERT',
    error_handler       => FALSE,
    user_procedure      => 'strmadmin.convert_product_information',
    apply_database_link => NULL,
    assemble_lobs       => TRUE);
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'oe.product_information',
    object_type         => 'TABLE',
    operation_name      => 'UPDATE',
    error_handler       => FALSE,
    user_procedure      => 'strmadmin.convert_product_information',
    apply_database_link => NULL,
    assemble_lobs       => TRUE);
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'oe.product_information',
    object_type         => 'TABLE',
    operation_name      => 'LOB_UPDATE',
    error_handler       => FALSE,
    user_procedure      => 'strmadmin.convert_product_information',
    apply_database_link => NULL,
    assemble_lobs       => TRUE);
END;
/
Step 5   Query the DBA_APPLY_DML_HANDLERS View

To ensure that the procedure DML handler is set properly for the oe.product_information table, run the following query: 

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A10
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A25
COLUMN ASSEMBLE_LOBS HEADING 'LOB Assembly?' FORMAT A15

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       OPERATION_NAME, 
       USER_PROCEDURE,
       ASSEMBLE_LOBS
  FROM DBA_APPLY_DML_HANDLERS;

Your output looks similar to the following: 

Table
Owner Table Name           Operation  Handler Procedure         LOB Assembly?
----- -------------------- ---------- ------------------------- ---------------
OE    PRODUCT_INFORMATION  INSERT     "STRMADMIN"."CONVERT_PROD Y
                                      UCT_INFORMATION"
 
OE    PRODUCT_INFORMATION  UPDATE     "STRMADMIN"."CONVERT_PROD Y
                                      UCT_INFORMATION"
 
OE    PRODUCT_INFORMATION  LOB_UPDATE "STRMADMIN"."CONVERT_PROD Y
                                      UCT_INFORMATION"

Notice that the correct procedure, convert_product_information, is used for each operation on the table. Also, notice that each handler uses LOB assembly.

14.4.3 Requirements for Constructing and Processing LCRs Containing LOB Columns

If your environment produces row LCRs that contain LOB columns, then you must meet the requirements in the following sections when you construct or process these LCRs:

See Also:

Oracle Streams Extended Examples for an example that constructs and enqueues LCRs that contain LOBs

14.4.3.1 Requirements for Constructing and Processing LCRs Without LOB Assembly

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.

See Also:

14.4.3.2 Requirements for Apply Handler Processing of LCRs with LOB Assembly

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.

See Also:

14.4.3.3 Requirements for Rule-Based Transformation Processing of LCRs with LOBs

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.

Note:

If row LCRs contain LOB columns, then rule-based transformations always operate on the original, nonassembled row LCRs.

See Also:

14.5 Managing LCRs Containing LONG or LONG RAW Columns

LONG and LONG RAW data types all can be present in row LCRs captured by a capture process, but these data types are represented by the following data types in row LCRs.

  • LONG data type is represented as VARCHAR2 data type in row LCRs.

  • LONG RAW data type is represented as RAW data type in row LCRs.

A row change involving a LONG or LONG RAW column can be captured, propagated, and applied as several LCRs. If your environment uses LCRs that contain LONG or LONG RAW columns, then the data portion of the LCR LONG or LONG RAW column must be of type VARCHAR2 or RAW. A VARCHAR2 is interpreted as a LONG, and a RAW is interpreted as a LONG RAW.

You must meet the following requirements when you are processing row LCRs that contain LONG or LONG RAW column data in Oracle Streams:

  • Do not modify LONG or LONG RAW column data in an LCR using a custom rule-based transformation. However, you can use a rule-based transformation to modify non LONG and non LONG RAW columns in row LCRs that contain LONG or LONG RAW column data.

  • Do not use the SET_VALUE or SET_VALUES row LCR member procedures in a custom rule-based transformation that is processing a row LCR that contains LONG or LONG RAW data. Doing so raises the ORA-26679 error.

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

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

  • You cannot use a procedure DML handler or error handler to process row LCRs that contain LONG or LONG RAW column data.

  • Rules used to evaluate LCRs that contain LONG or LONG RAW columns must be deterministic, so that either all of the LCRs corresponding to the row change cause a rule in a rule set to evaluate to TRUE, or none of them do.

  • You cannot use an apply process to enqueue LCRs that contain LONG or LONG RAW column data into a destination queue. The SET_DESTINATION_QUEUE procedure in the DBMS_APPLY_ADM package sets the destination queue for LCRs that satisfy a specified apply process rule.

Note:

LONG and LONG RAW data types cannot be present in row LCRs captured by synchronous captures or constructed by users.

See Also: