| Oracle® Streams Replication Administrator's Guide 10g Release 1 (10.1) Part Number B10728-01 |
|
|
View PDF |
| Oracle® Streams Replication Administrator's Guide 10g Release 1 (10.1) Part Number B10728-01 |
|
|
View PDF |
This chapter contains instructions for managing logical change records (LCRs) in a Streams replication environment.
This chapter contains these topics:
| See Also:
PL/SQL Packages and Types Reference and Oracle Streams Concepts and Administration for more information about 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:
command_type attribute is consistent with the presence or absence of old column values and the presence or absence of new column values.ddl_text is consistent with the base_table_name, base_table_owner, object_type, object_owner, object_name, and command_type attributes.CHARVARCHAR2NCHARNVARCHAR2NUMBERDATEBINARY_FLOATBINARY_DOUBLERAWTIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTHINTERVAL DAY TO SECONDThese datatypes are the only datatypes allowed for columns in a user-constructed row LCR. However, you may use certain techniques to construct LCRs that contain LOB information. Also, LCRs captured by a capture process support more datatypes.
See Also:
|
Use the following LCR constructors to create LCRs:
SYS.LCR$_ROW_RECORD constructor.SYS.LCR$_DDL_RECORD constructor. Make sure 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 a Streams administrator named strmadmin and granted this administrator DBA role.
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.
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; /
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; /
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; /
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; /
EXEC DBMS_APPLY_ADM.START_APPLY('strm04_apply');
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; /
| See Also:
PL/SQL Packages and Types Reference for more information about LCR constructors |
construct_row_lcr procedure created in Step 3.
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;
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;
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;
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;
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;
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;
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.
The following are general considerations for row changes involving LONG, LONG RAW and LOB datatypes in a Streams environment:
LONG, LONG RAW or LOB may be captured, propagated, and applied as several LCRs.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 |
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:
SET_VALUE or SET_VALUES row LCR member procedures in a rule-based transformation that is processing a row LCR that contains LONG or LONG RAW data. Doing so raises the ORA-26679 error.LONG or LONG RAW column data.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.
See Also:
|
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:
VARCHAR2 or RAW. A VARCHAR2 is interpreted as a CLOB, and a RAW is interpreted as a BLOB.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.LOB WRITE, LOB ERASE, and LOB TRIM LCRs, the old_values collection should be empty or NULL, and new_values should not be empty.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.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.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.LOB WRITE, LOB ERASE, and LOB TRIM LCR.LOB ERASE and a LOB TRIM LCR should be a NULL value encapsulated in a SYS.AnyData.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:
|
An apply process behaves in the following way when it encounters an LCR that contains a LOB:
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.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.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.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.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.UPDATE or DELETE, old LOB values are ignored.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.
/************************* BEGINNING OF SCRIPT ******************************
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 /*
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; /*
*/ 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 /*
*/ BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'lobex_queue_table', queue_name => 'lobex_queue'); END; / /*
*/ 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; / /*
*/ 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); /*
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; /*
*/ 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 /*
*/ -- 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 /*
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; / /*
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;
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|