Oracle® Streams Replication Administrator's Guide 10g Release 2 (10.2) Part Number B14228-02 |
|
|
View PDF |
This chapter contains instructions for managing logical change records (LCRs) in a 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 LCRsThis section describes requirements for creating or modifying LCRs. You can create an LCR using a constructor for an LCR type, and then enqueue the LCR into an ANYDATA
queue. Such an LCR is a user-enqueued LCR.
Also, you can 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:
If you create or modify a row LCR, then make sure 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 make sure 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 datatypes 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 datatypes are the only datatypes 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 datatypes.
See Also:
"Apply Processing Options for LCRs" for more information about apply handlers
Oracle Streams Concepts and Administration for information about the datatypes captured by a capture process and for information about rule-based transformations
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. 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.
Complete the following steps:
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.
Create an 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; /
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 LCRs, not LCRs 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; /
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; /
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; /
Start the apply process.
EXEC DBMS_APPLY_ADM.START_APPLY('strm04_apply');
Create a procedure called construct_row_lcr
that constructs a row LCR and 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 => 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 constructorsCreate and enqueue LCRs using the construct_row_lcr
procedure created in Step 3.
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', 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.net', cmd_type => 'INSERT', obj_owner => 'hr', obj_name => 'regions', old_vals => NULL, new_vals => newvals); END; / COMMIT;
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;
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', 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.net', cmd_type => 'UPDATE', obj_owner => 'hr', obj_name => 'regions', old_vals => oldvals, new_vals => newvals); END; / COMMIT;
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;
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', 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.net', cmd_type => 'DELETE', obj_owner => 'hr', obj_name => 'regions', old_vals => oldvals, new_vals => NULL); END; / COMMIT;
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;
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:
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 theEXECUTE
member procedure on a row LCR. Doing so could execute the row LCR outside of its transactional context.See Also:
"Managing a DML Handler" for an example of a DML handler that runs the EXECUTE
member procedure on row LCRs
Oracle Database PL/SQL Packages and Types Reference for more information about row LCRs and the LCR$_ROW_RECORD
type
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 into a queue or processed by an apply process. This example assumes that you have configured a Streams administrator named strmadmin
and granted this administrator DBA
role.
Complete the following steps:
Create a PL/SQL procedure named execute_row_lcr
that executes a row LCR:
CONNECT strmadmin/strmadminpw 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; /
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.NET', cmd_type => 'INSERT', obj_owner => 'HR', obj_name => 'JOBS', old_vals => NULL, new_vals => newvals); END insert_job_lcr; /
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.NET', cmd_type => 'UPDATE', obj_owner => 'HR', obj_name => 'JOBS', old_vals => oldvals, new_vals => newvals); END update_max_salary_lcr; /
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.NET', cmd_type => 'DELETE', obj_owner => 'HR', obj_name => 'JOBS', old_vals => oldvals, new_vals => NULL); END delete_job_lcr; /
Insert a row into the hr.jobs
table using the insert_job_lcr
procedure:
EXEC insert_job_lcr('BN_CNTR','BEAN COUNTER',5000,10000);
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
Update the max_salary
value for the row inserted into the hr.jobs
table in Step 5 using the update_max_salary_lcr
procedure:
EXEC update_max_salary_lcr('BN_CNTR',10000,12000);
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
Delete the row inserted into the hr.jobs
table in Step 5 using the delete_job_lcr
procedure:
EXEC delete_job_lcr('BN_CNTR');
Select the deleted row in the hr.jobs
table:
SELECT * FROM hr.jobs WHERE job_id = 'BN_CNTR';
no rows selected
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:
"Managing a DDL Handler" for an example of a DDL handler that runs the EXECUTE
member procedure on DDL LCRs
Oracle Database PL/SQL Packages and Types Reference for more information about DDL LCRs and the LCR$_DDL_RECORD
type
LOB datatypes can be present in row LCRs captured by a capture process, but these datatypes are represented by other datatypes. Certain LOB datatypes cannot be present in user-constructed LCRs. Table 11-1 shows the LCR representation for these datatypes and whether these datatypes can be present in a user-constructed LCR.
Table 11-1 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? |
---|---|---|---|
Fixed-width |
|
Yes |
Yes |
Variable-width |
|
Yes |
No |
|
|
Yes |
No |
|
|
Yes |
Yes |
The following are general considerations for row changes involving LOB datatypes in a 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.
See Also:
Oracle Database Application Developer's Guide - Large Objects for more information about LOBsAn 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.
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, prior to 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, LOB assembly simplifies custom processing of captured row LCRs with LOB columns. 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
datatype values. To enable LOB assembly for a DML or error handler, set the assemble_lobs
parameter to true
in the DBMS_APPLY_ADM.SET_DML_HANDLER
procedure.
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 11-2 shows Streams behavior when LOB assembly is disabled. Specifically, the table shows the LCRs passed to a DML handler or error handler resulting from a change to a single row with LOB columns.
Table 11-2 Streams Behavior with LOB Assembly Disabled
Original Row Change | First Set of LCRs | Second Set of LCRs | Third Set of LCRs | Final LCR |
---|---|---|---|---|
|
One |
One or more |
One or more |
|
|
One |
One or more |
One or more |
|
|
One |
N/A |
N/A |
N/A |
|
One or more |
N/A |
N/A |
N/A |
|
One |
N/A |
N/A |
N/A |
|
One |
N/A |
N/A |
N/A |
Table 11-3 shows 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 11-3 Streams Behavior with LOB Assembly Enabled
Original Row Change | Single LCR |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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 user-enqueued LCRs into LOB locators. However, LOB assembly does not combine multiple user-enqueued row LCRs into a single row LCR. For example, for user-enqueued 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:
"Apply Processing Options for LCRs" for more information about apply handlers
Oracle Database Application Developer's Guide - Large Objects and Oracle Database PL/SQL Packages and Types Reference for more information about using the DBMS_LOB
package
Oracle Database PL/SQL Packages and Types Reference for more information about the ADD_COLUMN
, SET_VALUE
, and SET_VALUES
member procedures for row LCRs
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 prior to Oracle Database 10g Release 2 cannot be assembled by LOB assembly.
Row LCRs captured on a database running Oracle Database 10g Release 2 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.
See Also:
Oracle Database Reference and Oracle Database Upgrade Guide for more information database compatibility
Oracle Database PL/SQL Packages and Types Reference for more information about the subprograms in the DBMS_APPLY_ADM
package
This section contains an example that uses LOB assembly with a 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 DML handler at a catalog destination database can add a column named photo
of datatype 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 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
datatype 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 DML handler updates the product_long_desc
column so that the long description is in the correct language.
The following steps configure a 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 DML Handler
This example creates the convert_product_information
procedure. This procedure will be used for the 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 DML Handler for the Apply Process
This step sets the convert_product_information
procedure as the 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 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.
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:
Requirements for Constructing and Processing LCRs Without LOB Assembly
Requirements for Apply Handler Processing of LCRs with LOB Assembly
Requirements for Rule-Based Transformation Processing of LCRs with LOBs
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 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:
"Apply Processing Options for LCRs" for more information about apply handlers
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 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:
"Apply Processing Options for LCRs" for more information about apply handlers
Oracle Database PL/SQL Packages and Types Reference for more information about member procedures for row LCRs and for information about the SET_ENQUEUE_DESTINATION
procedure
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:
"Apply Processing Options for LCRs" for more information about apply handlers
Oracle Streams Concepts and Administration for information about rule-based transformations
Oracle Database PL/SQL Packages and Types Reference for more information about member procedures for row LCRs
Oracle Database SQL Reference for more information about deterministic functions
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.
Grant the Streams Administrator EXECUTE Privilege on DBMS_STREAMS_MESSAGING
Grant the Streams Administrator Necessary Privileges on the Tables
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 an 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 TABLESPACE lob_user_tbs DATAFILE 'lob_user_tbs.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; CREATE USER lob_user IDENTIFIED BY Lob_user_pw DEFAULT TABLESPACE lob_user_tbs QUOTA UNLIMITED ON lob_user_tbs; GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE 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 => 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, 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', ANYDATA.ConvertNumber(3), NULL, NULL, NULL); -- Create empty CLOBs newunit2 := sys.lcr$_row_unit('C1', ANYDATA.ConvertVarChar2(NULL), DBMS_LCR.EMPTY_LOB, NULL, NULL); newunit3 := SYS.LCR$_ROW_UNIT('C2', ANYDATA.ConvertVarChar2(NULL), DBMS_LCR.EMPTY_LOB, NULL, NULL); newunit4 := SYS.LCR$_ROW_UNIT('C3', 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', 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;
LONG
and LONG
RAW
datatypes all can be present in row LCRs captured by a capture process, but these datatypes are represented by the following datatypes in row LCRs.
LONG
datatype is represented as VARCHAR2
datatype in row LCRs.
LONG
RAW
datatype is represented as RAW
datatype 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 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 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
datatypes cannot be present in user-constructed LCRs.See Also:
"Apply Processing Options for LCRs" for more information about apply handlers
Oracle Streams Concepts and Administration for information about rule-based transformations
Oracle Database SQL Reference for more information about deterministic functions