Skip Headers

Oracle® Streams Replication Administrator's Guide
10g Release 1 (10.1)

Part Number B10728-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

9
Managing Logical Change Records (LCRs)

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

This chapter contains these topics:

Requirements for Managing LCRs

This section describes requirements for creating or modifying LCRs. You may create an LCR using a constructor for an LCR type, and then enqueue the LCR into a SYS.AnyData queue. Such an LCR is a user-enqueued LCR event.

Also, you may modify an LCR using an apply handler or a rule-based transformation. You can modify both LCRs captured by a capture process and LCRs constructed and enqueued by a user or application.

Make sure you meet the following requirements when you manage an LCR:

Constructing and Enqueuing LCRs

Use the following LCR constructors to create LCRs:

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 a Streams administrator named strmadmin and granted this administrator DBA role.

  1. While connected as an administrative user, grant the 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 7. In this case, granting the privilege through a role is not sufficient.

  2. Create a SYS.AnyData queue in an Oracle database. This example assumes that the Streams administrator is strmadmin user.
    CONNECT strmadmin/strmadminpw
    
    BEGIN 
      DBMS_STREAMS_ADM.SET_UP_QUEUE(
        queue_table          =>  'strm04_queue_table',
        storage_clause       =>  NULL,
        queue_name           =>  'strm04_queue');
    END;
    /
    
    
  3. Create an apply process at the Oracle database to receive messages in the queue. Make sure the apply_captured parameter is set to false when you create the apply process, because the apply process will be applying user-enqueued events, not events captured by a capture process. Also, make sure 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;
    /
    
    
  4. 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.net 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.net',
        inclusion_rule      =>  true);
    END;
    /
    
    
  5. 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;
    /
    
    
  6. Start the apply process.
    EXEC DBMS_APPLY_ADM.START_APPLY('strm04_apply');
    
    
    
  7. Create a procedure called construct_row_lcr that constructs a row LCR and then enqueues it into the queue created in Step 2.
    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            =>  SYS.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:

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

  8. Create and enqueue LCRs using the construct_row_lcr procedure created in Step 3.
    1. Create a row LCR that inserts a row into the hr.regions table.
      CONNECT strmadmin/strmadminpw
      
      DECLARE
        newunit1  SYS.LCR$_ROW_UNIT;
        newunit2  SYS.LCR$_ROW_UNIT;
        newvals   SYS.LCR$_ROW_LIST;
      BEGIN
        newunit1 := SYS.LCR$_ROW_UNIT(
          'region_id', 
          SYS.AnyData.ConvertNumber(5),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        newunit2 := SYS.LCR$_ROW_UNIT(
          'region_name', 
          SYS.AnyData.ConvertVarchar2('Moon'),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        newvals := SYS.LCR$_ROW_LIST(newunit1,newunit2);
      construct_row_lcr(
        source_dbname  =>  'dbs1.net',
        cmd_type       =>  'INSERT',
        obj_owner      =>  'hr',
        obj_name       =>  'regions',
        old_vals       =>  NULL,
        new_vals       =>  newvals);
      END;
      /
      COMMIT;
      
      
    2. Connect as the hr user and 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.
      CONNECT hr/hr
      
      SELECT * FROM hr.regions;
      
      
    3. Create a row LCR that updates a row in the hr.regions table.
      CONNECT strmadmin/strmadminpw
      
      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', 
          SYS.AnyData.ConvertNumber(5),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        oldunit2 := SYS.LCR$_ROW_UNIT(
          'region_name', 
          SYS.AnyData.ConvertVarchar2('Moon'),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        oldvals := SYS.LCR$_ROW_LIST(oldunit1,oldunit2);
        newunit1 := SYS.LCR$_ROW_UNIT(
          'region_name', 
          SYS.AnyData.ConvertVarchar2('Mars'),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        newvals := SYS.LCR$_ROW_LIST(newunit1);
      construct_row_lcr(
        source_dbname  =>  'dbs1.net',
        cmd_type       =>  'UPDATE',
        obj_owner      =>  'hr',
        obj_name       =>  'regions',
        old_vals       =>  oldvals,
        new_vals       =>  newvals);
      END;
      /
      COMMIT;
      
      
    4. Connect as the hr user and 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.
      CONNECT hr/hr
      
      SELECT * FROM hr.regions;
      
      
    5. Create a row LCR that deletes a row from the hr.regions table.
      CONNECT strmadmin/strmadminpw
      
      DECLARE
        oldunit1  SYS.LCR$_ROW_UNIT;
        oldunit2  SYS.LCR$_ROW_UNIT;
        oldvals   SYS.LCR$_ROW_LIST;
      BEGIN
        oldunit1 := SYS.LCR$_ROW_UNIT(
          'region_id', 
          SYS.AnyData.ConvertNumber(5),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        oldunit2 := SYS.LCR$_ROW_UNIT(
          'region_name',
          SYS.AnyData.ConvertVarchar2('Mars'),
          DBMS_LCR.NOT_A_LOB,
          NULL,
          NULL);
        oldvals := SYS.LCR$_ROW_LIST(oldunit1,oldunit2);
      construct_row_lcr(
        source_dbname  =>  'dbs1.net',
        cmd_type       =>  'DELETE',
        obj_owner      =>  'hr',
        obj_name       =>  'regions',
        old_vals       =>  oldvals,
        new_vals       =>  NULL);
      END;
      /
      COMMIT;
      
      
    6. Connect as the hr user and query the hr.regions table to view the applied row change. The row with a region_id of 5 should have been deleted.
      CONNECT hr/hr
      
      SELECT * FROM hr.regions;
      

Managing LCRs Containing LONG, LONG RAW, or LOB Columns

LONG, LONG RAW and LOB datatypes all may be present in row LCRs captured by a capture process, but these datatypes are represented by other datatypes in row LCRs. LONG, LONG RAW and certain LOB datatypes cannot be present in user-created LCRs. Table 9-1 shows the LCR representation for these datatypes and whether these datatypes can be present in a user-created LCR.

Table 9-1 LONG, LONG RAW, and LOB Datatype Representations in Row LCRs
Datatype Row LCR Representation Can Be Present in a Captured LCR? Can Be Present in a User-Constructed LCR?

LONG

VARCHAR2

Yes

No

LONG RAW

RAW

Yes

No

Fixed-width CLOB

VARCHAR2

Yes

Yes

Variable-width CLOB

RAW in AL16UTF16 character set

Yes

No

NCLOB

RAW in AL16UTF16 character set

Yes

No

BLOB

RAW

Yes

Yes

The following are general considerations for row changes involving LONG, LONG RAW and LOB datatypes in a Streams environment:

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


Attention:

Do not modify LONG, LONG RAW or LOB column data in an LCR. This includes DML handlers, error handlers, and rule-based transformation functions.


Requirements for Processing LCRs Containing LONG and LONG RAW Columns

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 may use a rule-based transformation to process row LCRs that contain LONG or LONG RAW column data.

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

Requirements for Constructing and Processing LCRs Containing LOB Columns

If your environment uses LCRs that contain LOB columns, then you must meet the following requirements when you construct these LCRs or process them with an apply handler or a rule-based transformation:

All validation of these requirements is done by an apply process. If these requirements are not met, then an LCR containing a LOB column 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.

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

In addition, you cannot use the following row LCR member procedures on a LOB column when you are processing a row LCR with a rule-based transformation, DML handler, or error handler:

If you attempt to use any of these procedures on a row LCR that is being processed by a rule-based transformation, DML handler, or error handler, then error ORA-26679 is raised.

See Also:

Apply Process Behavior for LCRs Containing LOBs

An apply process behaves in the following way when it encounters an LCR that contains a LOB:

Example Script for Constructing and Enqueuing LCRs Containing LOBs

The example in this section illustrates creating a PL/SQL procedure for constructing and enqueuing LCRs containing LOBs. This example assumes that you have prepared your database for Streams by completing the necessary actions described in Oracle Streams Concepts and Administration.

  1. Show Output and Spool Results
  2. Grant the Streams Administrator EXECUTE Privilege on DBMS_STREAMS_MESSAGING
  3. Connect as the Streams Administrator
  4. Create a SYS.AnyData Queue
  5. Create and Start an Apply Process
  6. Create a Schema with Tables Containing LOB Columns
  7. Grant the Streams Administrator Necessary Privileges on the Tables
  8. Create a PL/SQL Procedure to Enqueue LCRs Containing LOBs
  9. Create the do_enq_clob Function to Enqueue CLOB Data
  10. Enqueue CLOB Data Using the do_enq_clob Function
  11. Check the Spool Results

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.


/************************* BEGINNING OF SCRIPT ******************************
Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL lob_construct.out

/*
Step 2 Grant the Streams Administrator EXECUTE Privilege on DBMS_STREAMS_MESSAGING

Explicit EXECUTE privilege on the package is required because a procedure in the package is called in within a PL/SQL procedure in Step 8.

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT EXECUTE ON DBMS_STREAMS_MESSAGING TO STRMADMIN;

/*
Step 3 Connect as the Streams Administrator
*/

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON SIZE 100000

CONNECT strmadmin/strmadminpw

/*
Step 4 Create a SYS.AnyData Queue
*/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE( 
    queue_table => 'lobex_queue_table', 
    queue_name  => 'lobex_queue');
END;
/

/*
Step 5 Create and Start an Apply Process
*/

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name      => 'strmadmin.lobex_queue',
    apply_name      => 'apply_lob',
    apply_captured  => false);
END;
/

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

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    'apply_lob');
END;
/

/*
Step 6 Create a Schema with Tables Containing LOB Columns
*/

CONNECT SYSTEM/MANAGER AS SYSDBA

CREATE USER lob_user IDENTIFIED BY Lob_user_pw;
GRANT CONNECT, RESOURCE TO lob_user;

CONNECT lob_user/lob_user_pw

CREATE TABLE with_clob (a  NUMBER PRIMARY KEY,
                        c1 CLOB,
                        c2 CLOB,
                        c3 CLOB);

CREATE TABLE with_blob (a NUMBER PRIMARY KEY,
                        b BLOB);

/*
Step 7 Grant the Streams Administrator Necessary Privileges on the Tables

Granting these privileges enables the Streams administrator to get the LOB length for offset and to perform DML operations on the tables.

*/

GRANT ALL ON with_clob TO strmadmin;
GRANT ALL ON with_blob TO strmadmin;
COMMIT;

/*
Step 8 Create a PL/SQL Procedure to Enqueue LCRs Containing LOBs
*/

CONNECT strmadmin/strmadminpw

CREATE OR REPLACE PROCEDURE enq_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
  xr_lcr         SYS.LCR$_ROW_RECORD;
BEGIN
  xr_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 a row lcr
  DBMS_STREAMS_MESSAGING.ENQUEUE(
        queue_name         => 'lobex_queue', 
        payload            => SYS.AnyData.ConvertObject(xr_lcr));
END enq_row_lcr;
/
SHOW ERRORS

/*
Step 9 Create the do_enq_clob Function to Enqueue CLOB Data
*/

-- Description of each variable:
-- src_dbname  : Source database name
-- tab_owner   : Table owner
-- tab_name    : Table name
-- col_name    : Name of the CLOB column
-- new_vals    : SYS.LCR$_ROW_LIST containing primary key and supplementally  
--               logged colums
-- clob_data   : CLOB that contains data to be sent
-- offset      : Offset from which data should be sent, default is 1
-- lsize       : Size of data to be sent, default is 0
-- chunk_size  : Size used for creating LOB chunks, default is 2048

CREATE OR REPLACE FUNCTION do_enq_clob(src_dbname     VARCHAR2,
                                       tab_owner      VARCHAR2,
                                       tab_name       VARCHAR2,
                                       col_name       VARCHAR2,
                                       new_vals       SYS.LCR$_ROW_LIST,
                                       clob_data      CLOB,
                                       offset         NUMBER default 1,
                                       lsize          NUMBER default 0,
                                       chunk_size     NUMBER default 2048) 
RETURN NUMBER IS
  lob_offset NUMBER; -- maintain lob offset
  newunit    SYS.LCR$_ROW_UNIT;
  tnewvals   SYS.LCR$_ROW_LIST;
  lob_flag   NUMBER;
  lob_data   VARCHAR2(32767);
  lob_size   NUMBER;
  unit_pos   NUMBER;
  final_size NUMBER;
  exit_flg   BOOLEAN;
  c_size     NUMBER;
  i          NUMBER;
BEGIN
  lob_size := DBMS_LOB.GETLENGTH(clob_data);
  unit_pos := new_vals.count + 1;
  tnewvals := new_vals;
  c_size   := chunk_size;
  i := 0;
  -- validate parameters
  IF (unit_pos <= 1) THEN
    DBMS_OUTPUT.PUT_LINE('Invalid new_vals list');
    RETURN 1;
  END IF;

  IF (c_size < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Invalid LOB chunk size');
    RETURN 1;
  END IF;

  IF (lsize < 0 OR lsize > lob_size) THEN
    DBMS_OUTPUT.PUT_LINE('Invalid LOB size');
    RETURN 1;
  END IF;

  IF (offset < 1 OR offset >= lob_size) THEN
    DBMS_OUTPUT.PUT_LINE('Invalid lob offset');
    RETURN 1;
  ELSE
    lob_offset := offset;
  END IF;

  -- calculate final size
  IF (lsize = 0) THEN
    final_size := lob_size;
  ELSE
    final_size := lob_offset + lsize;
  END IF;

  --  The following output lines are for debugging purposes only.
  -- DBMS_OUTPUT.PUT_LINE('Final size: ' || final_size);
  -- DBMS_OUTPUT.PUT_LINE('Lob size: ' || lob_size);

  IF (final_size < 1 OR final_size > lob_size) THEN
    DBMS_OUTPUT.PUT_LINE('Invalid lob size');
    RETURN 1;
  END IF;

  -- expand new_vals list for LOB column
  tnewvals.extend();

  exit_flg := false;

  -- Enqueue all LOB chunks
  LOOP
    --  The following output line is for debugging purposes only.
    DBMS_OUTPUT.PUT_LINE('About to write chunk#' || i);
    i := i + 1;
 
    -- check if last LOB chunk
    IF ((lob_offset + c_size) < final_size) THEN
      lob_flag := DBMS_LCR.LOB_CHUNK;
    ELSE
      lob_flag := DBMS_LCR.LAST_LOB_CHUNK;
      exit_flg := true;
      --  The following output line is for debugging purposes only.
      DBMS_OUTPUT.PUT_LINE('Last LOB chunk');
    END IF;

    --  The following output lines are for debugging purposes only.
    DBMS_OUTPUT.PUT_LINE('lob offset: ' || lob_offset);
    DBMS_OUTPUT.PUT_LINE('Chunk size: ' || to_char(c_size));

    lob_data := DBMS_LOB.SUBSTR(clob_data, c_size, lob_offset); 

    -- create row unit for clob
    newunit := SYS.LCR$_ROW_UNIT(col_name,
                                 SYS.AnyData.ConvertVarChar2(lob_data), 
                                 lob_flag, 
                                 lob_offset, 
                                 NULL);

    -- insert new LCR$_ROW_UNIT
    tnewvals(unit_pos) := newunit;  

    -- enqueue lcr
    enq_row_lcr(
          source_dbname => src_dbname,
          cmd_type      => 'LOB WRITE',
          obj_owner     => tab_owner,
          obj_name      => tab_name,
          old_vals      => NULL,
          new_vals      => tnewvals);

    -- calculate next chunk size 
    lob_offset := lob_offset + c_size;
    
    IF ((final_size - lob_offset) < c_size) THEN
      c_size := final_size - lob_offset + 1;
    END IF;

    --  The following output line is for debugging purposes only.
    DBMS_OUTPUT.PUT_LINE('Next chunk size : ' || TO_CHAR(c_size));

    IF (c_size < 1) THEN
      exit_flg := true;
    END IF;

    EXIT WHEN exit_flg;

  END LOOP;

  RETURN 0;
END do_enq_clob;
/

SHOW ERRORS

/*
Step 10 Enqueue CLOB Data Using the do_enq_clob Function

The DBMS_OUTPUT lines in the following example can be used for debugging purposes if necessary. If they are not needed, then they can be commented out or deleted.

*/

SET SERVEROUTPUT ON SIZE 100000
DECLARE
  c1_data CLOB;
  c2_data CLOB;
  c3_data CLOB;
  newunit1 SYS.LCR$_ROW_UNIT;
  newunit2 SYS.LCR$_ROW_UNIT;
  newunit3 SYS.LCR$_ROW_UNIT;
  newunit4 SYS.LCR$_ROW_UNIT;
  newvals  SYS.LCR$_ROW_LIST;
  big_data VARCHAR(22000);
  n        NUMBER;
BEGIN
  -- Create primary key for LCR$_ROW_UNIT
  newunit1 := SYS.LCR$_ROW_UNIT('A',
                                Sys.AnyData.ConvertNumber(3), 
                                NULL, 
                                NULL, 
                                NULL);
  -- Create empty CLOBs
  newunit2 := sys.lcr$_row_unit('C1',
                                Sys.AnyData.ConvertVarChar2(NULL),
                                DBMS_LCR.EMPTY_LOB, 
                                NULL, 
                                NULL);
  newunit3 := SYS.LCR$_ROW_UNIT('C2',
                                Sys.AnyData.ConvertVarChar2(NULL),
                                DBMS_LCR.EMPTY_LOB, 
                                NULL, 
                                NULL);
  newunit4 := SYS.LCR$_ROW_UNIT('C3',
                                Sys.AnyData.ConvertVarChar2(NULL),
                                DBMS_LCR.EMPTY_LOB, 
                                NULL, 
                                NULL);
  newvals := SYS.LCR$_ROW_LIST(newunit1,newunit2,newunit3,newunit4);

  -- Perform an insert
  enq_row_lcr(
    source_dbname => 'MYDB.NET',
    cmd_type      => 'INSERT',
    obj_owner     => 'LOB_USER',
    obj_name      => 'WITH_CLOB',
    old_vals      => NULL,
    new_vals      => newvals);

  -- construct clobs
  big_data := RPAD('Hello World', 1000, '_');
  big_data := big_data || '#';
  big_data := big_data || big_data || big_data || big_data || big_data;
  DBMS_LOB.CREATETEMPORARY(
    lob_loc => c1_data, 
    cache   => true);
  DBMS_LOB.WRITEAPPEND(
    lob_loc => c1_data, 
    amount  => length(big_data), 
    buffer  => big_data);

  big_data := RPAD('1234567890#', 1000, '_');
  big_data := big_data || big_data || big_data || big_data;
  DBMS_LOB.CREATETEMPORARY(
    lob_loc => c2_data, 
    cache   => true);
  DBMS_LOB.WRITEAPPEND(
    lob_loc => c2_data, 
    amount  => length(big_data), 
    buffer  => big_data);

  big_data := RPAD('ASDFGHJKLQW', 2000, '_');
  big_data := big_data || '#';
  big_data := big_data || big_data || big_data || big_data || big_data;
  DBMS_LOB.CREATETEMPORARY(
    lob_loc => c3_data, 
    cache   => true);
  DBMS_LOB.WRITEAPPEND(
    lob_loc => c3_data, 
    amount  => length(big_data), 
    buffer  => big_data);

  -- pk info
  newunit1 := SYS.LCR$_ROW_UNIT('A',
                                SYS.AnyData.ConvertNumber(3), 
                                NULL, 
                                NULL, 
                                NULL);
  newvals  := SYS.LCR$_ROW_LIST(newunit1); 

  -- write c1 clob
  n := do_enq_clob(
         src_dbname => 'MYDB.NET',
         tab_owner  => 'LOB_USER',
         tab_name   => 'WITH_CLOB',
         col_name   => 'C1',
         new_vals   => newvals,
         clob_data  => c1_data,
         offset     => 1,
         chunk_size => 1024);
  DBMS_OUTPUT.PUT_LINE('n=' || n);
 
  -- write c2 clob
  newvals  := SYS.LCR$_ROW_LIST(newunit1); 
  n := do_enq_clob(
         src_dbname => 'MYDB.NET',
         tab_owner  => 'LOB_USER',
         tab_name   => 'WITH_CLOB',
         col_name   => 'C2',
         new_vals   => newvals,
         clob_data  => c2_data,
         offset     => 1,
         chunk_size => 2000);
  DBMS_OUTPUT.PUT_LINE('n=' || n);
 
  -- write c3 clob
  newvals  := SYS.LCR$_ROW_LIST(newunit1); 
  n := do_enq_clob(src_dbname=>'MYDB.NET',
         tab_owner  => 'LOB_USER',
         tab_name   => 'WITH_CLOB',
         col_name   => 'C3',
         new_vals   => newvals,
         clob_data  => c3_data,
         offset     => 1,
         chunk_size => 500);
  DBMS_OUTPUT.PUT_LINE('n=' || n);
 
  COMMIT;

END;
/

/*
Step 11 Check the Spool Results

Check the lob_construct.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

After you run the script, you can check the lob_user.with_clob table to list the rows applied by the apply process. The DBMS_LOCK.SLEEP statement is used to give the apply process time to apply the enqueued rows.

CONNECT lob_user/lob_user_pw

EXECUTE DBMS_LOCK.SLEEP(10);

SELECT a, c1, c2, c3 FROM with_clob ORDER BY a;

SELECT a, LENGTH(c1), LENGTH(c2), LENGTH(c3) FROM with_clob ORDER BY a;