248 Logical Change Record TYPEs

Using Logical Change Record Types

This section contains topics that relate to using the logical change record (LCR) types.

• Overview

• Security Model

Overview

In Oracle Streams, logical change records (LCRs) are message payloads that contain information about changes to a database. These changes can include changes to the data, which are data manipulation language (DML) changes, and changes to database objects, which are data definition language (DDL) changes.

When you use Oracle Streams, the capture process captures changes in the form of LCRs and enqueues them into a queue. These LCRs can be propagated from a queue in one database to a queue in another database. Finally, the apply process can apply LCRs at a destination database. You also have the option of creating, enqueuing, and dequeuing LCRs manually.

Security Model

PUBLIC is granted EXECUTE privilege on the types described in this chapter.

Summary of Logical Change Record Types

These logical change record (LCR) types can be used with the following Oracle-supplied PL/SQL packages:

• DBMS_APPLY_ADM

• DBMS_AQ

• DBMS_AQADM

• DBMS_CAPTURE_ADM

• DBMS_PROPAGATION_ADM

• DBMS_RULE

• DBMS_RULE_ADM

• DBMS_STREAMS

• DBMS_STREAMS_ADM

• DBMS_TRANSFORM

LCR$_DDL_RECORD Type

This type represents a data definition language (DDL) change to a database object.

If you create or modify a DDL logical change record (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.

This section contains information about the constructor for DDL LCRs and information about the member subprograms for this type:

• LCR$_DDL_RECORD Constructor

• Summary of LCR$_DDL_RECORD Subprograms, which also include the subprograms described in "Common Subprograms for LCR$_DDL_RECORD and LCR$_ROW_RECORD"

  Note:

  • When passing a name as a parameter to an LCR constructor, you can enclose the name in double quotes to handle names that use mixed case or lower case for database objects. For example, if a name contains any lower case characters, then you must enclose it in double quotes.

  • 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.

LCR$_DDL_RECORD Constructor

Creates a SYS.LCR$_DDL_RECORD object with the specified information.

STATIC FUNCTION CONSTRUCT(
   source_database_name  IN  VARCHAR2,
   command_type          IN  VARCHAR2,
   object_owner          IN  VARCHAR2,
   object_name           IN  VARCHAR2,
   object_type           IN  VARCHAR2,
   ddl_text              IN  CLOB,
   logon_user            IN  VARCHAR2,
   current_schema        IN  VARCHAR2,
   base_table_owner      IN  VARCHAR2,
   base_table_name       IN  VARCHAR2,
   tag                   IN  RAW       DEFAULT NULL,
   transaction_id        IN  VARCHAR2  DEFAULT NULL,
   scn                   IN  NUMBER    DEFAULT NULL,
   position              IN  RAW       DEFAULT NULL,
   edition_name          IN  VARCHAR2  DEFAULT NULL)
RETURN SYS.LCR$_DDL_RECORD;

LCR$_DDL_RECORD Constructor Function Parameters

ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SUMMARY
CREATE SCHEMA
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SUMMARY
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
DROP SUMMARY
RENAME

CLUSTER
FUNCTION
INDEX
LINK
OUTLINE
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
TYPE
USER
VIEW

Summary of LCR$_DDL_RECORD Subprograms

EXECUTE Member Procedure

Executes the DDL LCR under the security domain of the current user. Apply handlers are not run when the LCR is applied using this procedure.

Syntax

MEMBER PROCEDURE EXECUTE;

GET_BASE_TABLE_NAME Member Function

Gets the base (dependent) table name.

Syntax

MEMBER FUNCTION GET_BASE_TABLE_NAME()
RETURN VARCHAR2;

GET_BASE_TABLE_OWNER Member Function

Gets the base (dependent) table owner.

Syntax

MEMBER FUNCTION GET_BASE_TABLE_OWNER() 
RETURN VARCHAR2;

GET_CURRENT_SCHEMA Member Function

Gets the current schema name.

Syntax

MEMBER FUNCTION GET_CURRENT_SCHEMA() 
RETURN VARCHAR2;

GET_DDL_TEXT Member Procedure

Gets the DDL text in a CLOB.

For example, the following PL/SQL code uses this procedure to get the DDL text in a DDL LCR:

CREATE OR REPLACE PROCEDURE ddl_in_lcr (ddl_lcr in SYS.LCR$_DDL_RECORD)
IS
  ddl_text   CLOB;
BEGIN
  DBMS_OUTPUT.PUT_LINE( '  -----------------------------------------' ); 
  DBMS_OUTPUT.PUT_LINE( '  Displaying DDL text in a DDL LCR: ' );
  DBMS_OUTPUT.PUT_LINE( '  -----------------------------------------' ); 
  DBMS_LOB.CREATETEMPORARY(ddl_text, true);
  ddl_lcr.GET_DDL_TEXT(ddl_text);
  DBMS_OUTPUT.PUT_LINE('DDL text:' || ddl_text);
  DBMS_LOB.FREETEMPORARY(ddl_text);
END;
/

Syntax

MEMBER FUNCTION GET_DDL_TEXT(
  ddl_text  IN/OUT  CLOB);

Parameter

GET_EDITION_NAME Member Function

Gets the name of the edition in which the DDL statement was executed.

Syntax

MEMBER FUNCTION GET_EDITION_NAME() 
RETURN VARCHAR2;

GET_LOGON_USER Member Function

Gets the logon user name.

Syntax

MEMBER FUNCTION GET_LOGON_USER() 
RETURN VARCHAR2;

GET_OBJECT_TYPE Member Function

Gets the type of the object involved for the DDL.

Syntax

MEMBER FUNCTION GET_OBJECT_TYPE() 
RETURN VARCHAR2;

SET_BASE_TABLE_NAME Member Procedure

Sets the base (dependent) table name.

Syntax

MEMBER PROCEDURE SET_BASE_TABLE_NAME(
   base_table_name  IN  VARCHAR2);

Parameter

SET_BASE_TABLE_OWNER Member Procedure

Sets the base (dependent) table owner.

Syntax

MEMBER PROCEDURE SET_BASE_TABLE_OWNER(
   base_table_owner  IN  VARCHAR2);

Parameter

SET_CURRENT_SCHEMA Member Procedure

Sets the default schema (user) name.

Syntax

MEMBER PROCEDURE SET_CURRENT_SCHEMA(
   current_schema  IN  VARCHAR2);

Parameter

SET_DDL_TEXT Member Procedure

Sets the DDL text.

Syntax

MEMBER PROCEDURE SET_DDL_TEXT(
   ddl_text  IN  CLOB);

Parameter

SET_EDITION_NAME Member Procedure

Sets the name of the edition in which the DDL statement was executed.

Syntax

MEMBER PROCEDURE SET_EDITION_NAME(
   edition_name  IN  VARCHAR2);

Parameter

SET_LOGON_USER Member Procedure

Sets the logon user name.

Syntax

MEMBER PROCEDURE SET_LOGON_USER(
   logon_user  IN  VARCHAR2);

Parameter

SET_OBJECT_TYPE Member Procedure

Sets the object type.

Syntax

MEMBER PROCEDURE SET_OBJECT_TYPE(
   object_type  IN  VARCHAR2);

Parameter

CLUSTER
FUNCTION
INDEX
LINK
OUTLINE
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
TYPE
USER
VIEW

LCR$_ROW_RECORD Type

This type represents a data manipulation language (DML) change to a row in a table. This type uses the LCR$_ROW_LIST type.

If you create or modify a row logical change record (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.

This section contains information about the constructor for DDL LCRs and information about the member subprograms for this type:

• LCR$_ROW_RECORD Constructor

• Summary of LCR$_ROW_RECORD Subprograms, which also include the subprograms described in Common Subprograms for LCR$_DDL_RECORD and LCR$_ROW_RECORD

  Note:

  • When passing a name as a parameter to an LCR constructor, you can enclose the name in double quotes to handle names that use mixed case or lower case for database objects. For example, if a name contains any lower case characters, then you must enclose it in double quotes.

  • 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:

  LCR$_ROW_LIST Type

LCR$_ROW_RECORD Constructor

Creates a SYS.LCR$_ROW_RECORD object with the specified information.

STATIC FUNCTION CONSTRUCT(
   source_database_name  IN  VARCHAR2,
   command_type          IN  VARCHAR2,
   object_owner          IN  VARCHAR2,
   object_name           IN  VARCHAR2,
   tag                   IN  RAW                DEFAULT NULL,
   transaction_id        IN  VARCHAR2           DEFAULT NULL,
   scn                   IN  NUMBER             DEFAULT NULL,
   old_values            IN  SYS.LCR$_ROW_LIST  DEFAULT NULL,
   new_values            IN  SYS.LCR$_ROW_LIST  DEFAULT NULL,
   position              IN  RAW                DEFAULT NULL,
   statement             IN  VARCHAR2           DEFAULT NULL,
   bind_variables        IN  SYS.LCR$_ROW_LIST  DEFAULT NULL,
   bind_by_position      IN  VARCHAR2           DEFAULT 'N')
RETURN SYS.LCR$_ROW_RECORD;

LCR$_ROW_RECORD Constructor Function Parameters

INSERT
UPDATE
DELETE
LOB ERASE
LOB WRITE
LOB TRIM

Summary of LCR$_ROW_RECORD Subprograms

ADD_COLUMN Member Procedure

Adds the value as old or new, depending on the value type specified, for the column. An error is raised if a value of the same type already exists for the column.

Considerations for LOB Columns

When processing a row LCR with LOB columns with a procedure DML handler or error handler and the handler is using LOB assembly (the assemble_lobs parameter is set to TRUE for the handler), you use this member procedure in the handler procedure to add a LOB column to a row LCR. If assemble_lobs is set to FALSE for the handler, then you cannot use this member procedure to add a LOB column to a row LCR.

To use a DML or error handler to add a LOB column, specify the LOB locator for the column_value parameter in the member procedure. The ADD_COLUMN member procedure verifies that an ANYDATA encapsulated LOB locator is processed with a DML or error handler that is using LOB assembly. An error is raised under the following conditions:

• The handler attempts to enqueue a row LCR with an ANYDATA encapsulated LOB locator.

• An attempt is made to add an LOB column that is set incorrectly.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the LOB is represented by the original (nonassembled) row LCRs.

Syntax

MEMBER PROCEDURE ADD_COLUMN(
   value_type    IN  VARCHAR2,
   column_name   IN  VARCHAR2,
   column_value  IN  ANYDATA);

Parameters

CONVERT_LONG_TO_LOB_CHUNK Member Procedure

Converts LONG data in a row LCR into a CLOB, or converts LONG RAW data in a row LCR into a BLOB.

This procedure can change the operation code from LONG WRITE to LOB WRITE for the row LCR.

This member procedure can be used in rule-based transformations.

The following restrictions apply to this member procedure:

• This member procedure cannot be used in apply handlers.

• LONG data can be sent as a part of a row LCR with one of the following operation codes: INSERT, UPDATE, or LONG_WRITE. Because LONG data can be sent in multiple pieces, make sure that this method is invoked on either none or all LONG pieces.

• LOB to LONG conversion is not supported.

• A row LCR on which this procedure is executed must have been created by a capture process. That is, this procedure does not support persistent row LCRs.

Syntax

MEMBER PROCEDURE CONVERT_LONG_TO_LOB_CHUNK;

DELETE_COLUMN Member Procedure

Deletes the old value, the new value, or both, for the specified column, depending on the value type specified.

Syntax

MEMBER PROCEDURE DELETE_COLUMN(
   column_name  IN  VARCHAR2,
   value_type   IN  VARCHAR2  DEFAULT '*');

Parameters

EXECUTE Member Procedure

Executes the row LCR under the security domain of the current user. Any apply handlers that would be run for an LCR are not run when the LCR is applied using this procedure.

This 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 a mechanism other than an Oracle Streams capture process, such as an Oracle Streams apply process or an application.

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

• The LCR is in the error queue.

Considerations for LOB Columns

When processing a row LCR with LOB columns with a procedure DML handler or error handler, and the handler is using LOB assembly (the assemble_lobs parameter is set to TRUE for the handler), this member procedure executes the assembled row LCR. An assembled row LCR represents a LOB value with a LOB locator or NULL.

If assemble_lobs is set to FALSE for the handler, then this member procedure executes the nonassembled row LCRs. Nonassembled row LCRs represent LOB values with VARCHAR2 and RAW data types. These nonassembled row LCRs might have been modified by the handler.

An error is raised under the following conditions:

• A DML or error handler configured with assemble_lobs set to FALSE attempts to execute a row LCR that contains a LOB locator.

• A DML or error handler configured with assemble_lobs set to TRUE attempts to execute a row LCR that contains one or more LOB values represented with VARCHAR2 or RAW data types.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the LOB is represented by the original (nonassembled) row LCRs.

Syntax

MEMBER PROCEDURE EXECUTE(
   conflict_resolution  IN  BOOLEAN);

Parameters

GET_LOB_INFORMATION Member Function

Gets the LOB information for the column.

The return value can be one of the following:

DBMS_LCR.NOT_A_LOB        CONSTANT NUMBER := 1;
DBMS_LCR.NULL_LOB         CONSTANT NUMBER := 2;
DBMS_LCR.INLINE_LOB       CONSTANT NUMBER := 3;
DBMS_LCR.EMPTY_LOB        CONSTANT NUMBER := 4;
DBMS_LCR.LOB_CHUNK        CONSTANT NUMBER := 5;
DBMS_LCR.LAST_LOB_CHUNK   CONSTANT NUMBER := 6;

Returns NULL if the specified column does not exist.

If the command type of the row LCR is UPDATE, then specifying 'Y' for the use_old parameter is a convenient way to get the value of the columns.

Syntax

MEMBER FUNCTION GET_LOB_INFORMATION(
  value_type   IN  VARCHAR2,
  column_name  IN  VARCHAR2,
  use_old      IN  VARCHAR2  DEFAULT 'Y') 
RETURN NUMBER;

Parameters

GET_LOB_OFFSET Member Function

Gets the LOB offset for the specified column in the number of characters for CLOB columns and the number of bytes for BLOB columns. Returns a non-NULL value only if all of the following conditions are met:

• The value exists for the column

• The column value is an out-of-line LOB. That is, the information is DBMS_LCR.LAST_LOB_CHUNK or DBMS_LCR.LOB_CHUNK

• The command type is LOB ERASE or LOB WRITE

Otherwise, returns NULL.

Syntax

GET_LOB_OFFSET(
   value_type   IN  VARCHAR2,
   column_name  IN  VARCHAR2) 
RETURN NUMBER;

Parameters

GET_LOB_OPERATION_SIZE Member Function

Gets the operation size for the LOB column in the number of characters for CLOB columns and the number of bytes for BLOB columns. Returns a non-NULL value only if all of the following conditions are met:

• The value exists for the column

• The column value is an out-of-line LOB

• The command type is LOB ERASE or LOB TRIM

• The information is DBMS_LCR.LAST_LOB_CHUNK

Otherwise, returns NULL.

Syntax

MEMBER FUNCTION GET_LOB_OPERATION_SIZE(
  value_type   IN  VARCHAR2,
  column_name  IN  VARCHAR2) 
RETURN NUMBER,

Parameters

GET_LONG_INFORMATION Member Function

Gets the LONG information for the column.

The return value can be one of the following:

DBMS_LCR.NOT_A_LONG        CONSTANT NUMBER := 1;
DBMS_LCR.NULL_LONG         CONSTANT NUMBER := 2;
DBMS_LCR.INLINE_LONG       CONSTANT NUMBER := 3;
DBMS_LCR.LONG_CHUNK        CONSTANT NUMBER := 4;
DBMS_LCR.LAST_LONG_CHUNK   CONSTANT NUMBER := 5;

Returns NULL if the specified column does not exist.

If the command type of the row LCR is UPDATE, then specifying 'Y' for the use_old parameter is a convenient way to get the value of the columns.

Syntax

MEMBER FUNCTION GET_LONG_INFORMATION(
  value_type   IN  VARCHAR2,
  column_name  IN  VARCHAR2,
  use_old      IN  VARCHAR2  DEFAULT 'Y') 
RETURN NUMBER;

Parameters

GET_ROW_TEXT Member Procedure

Gets the SQL statement for the change that is encapsulated in the row LCR. This method performs SQL generation in PL/SQL.

This method is overloaded. The different functionality of each form of syntax is presented along with the definitions.

Syntax

The following procedure returns the SQL statement in a CLOB data type.

MEMBER PROCEDURE GET_ROW_TEXT(
   row_text  IN/OUT  CLOB);

The following procedure returns the SQL statement with bind variables in a CLOB data type.

MEMBER PROCEDURE GET_ROW_TEXT(
   row_text         IN/OUT  CLOB,
   variable_list    IN/OUT  LCR$_ROW_LIST,
   bind_var_syntax  IN      VARCHAR2  DEFAULT ':');

Parameters

GET_VALUE Member Function

Gets the old or new value for the specified column, depending on the value type specified.

If the command type of the row LCR is UPDATE, then specifying 'Y' for the use_old parameter is a convenient way to get the value of a column.

Syntax

MEMBER FUNCTION GET_VALUE(
   value_type   IN  VARCHAR2,
   column_name  IN  VARCHAR2,
   use_old      IN  VARCHAR2  DEFAULT 'Y') 
RETURN ANYDATA;

Parameters

GET_VALUES Member Function

Gets a list of old or new values, depending on the value type specified.

If the command type of the row LCR is UPDATE, then specifying 'Y' for the use_old parameter is a convenient way to get the values of all columns.

Syntax

MEMBER FUNCTION GET_VALUES(
   value_type  IN  VARCHAR2,
   use_old     IN  VARCHAR2  DEFAULT 'Y')
RETURN SYS.LCR$_ROW_LIST;

Parameters

GET_WHERE_CLAUSE Member Procedure

Gets a WHERE clause for the change that is encapsulated in the row LCR.

Use the WHERE clause returned by GET_WHERE_CLAUSE instead of using the ROWID, because the ROWID is not ANSI compatible. The generated WHERE clause might not match the WHERE clause in the original DML operation.

The ROWID of an INSERT statement is the ROWID of the new row created by the INSERT. The WHERE clause generated for an INSERT operation identifies the new row. Therefore, the generated WHERE clause includes all of the new values inserted.

For example, consider the following insert into the hr.departments table:

INSERT INTO hr.departments (
   department_id, department_name, manager_id, location_id) 
   VALUES (10, 'HR', 20, 40);

The generated WHERE clause represents the row with the values 10, 'HR', 20, and 40. Hence, the generated WHERE clause is the following:

WHERE "DEPARTMENT_ID" = 10 AND "DEPARTMENT_NAME" = 'HR' AND
      "MANAGER_ID" = 20 AND "LOCATION_ID" = 40

The ROWID of an UPDATE statement is the ROWID of the row that was updated. The WHERE clause generated for an UPDATE operation identifies the row after the UPDATE executes. The generated WHERE clause is based on the old and new values of the UPDATE.

For example, consider the following update to the hr.departments table:

UPDATE hr.departments SET department_name='Management'
 WHERE department_name='Administration' AND location_id = 20 AND
       manager_id = 30 AND department_id = 10;

The values of the row after the UPDATE are 10, 'Management', 30, and 20. Hence, the generated WHERE clause to identify the row is the following:

WHERE "DEPARTMENT_ID" = 10 AND "DEPARTMENT_NAME" = 'MANAGEMENT' AND
      "MANAGER_ID" = 30 AND "LOCATION_ID" = 20

Notice that the new value is used for "DEPARTMENT_NAME", because the new value is the value of the column after the UPDATE. For the rest of the columns, the old values are used.

The ROWID of a DELETE operation is the row that existed before it was deleted. The generated WHERE clause consists of all the old column values present in the DELETE operation.

LOB columns do not appear in generated WHERE clauses. The generated WHERE clause is not affected by the presence of LOB columns in the LCR.

This method is overloaded. The different functionality of each form of syntax is presented along with the definitions.

Syntax

The following procedure returns the WHERE clause of a SQL statement in a CLOB data type.

MEMBER PROCEDURE GET_WHERE_CLAUSE(
   where_clause  IN/OUT  CLOB);

The following procedure returns the WHERE clause of a SQL statement with bind variables in a CLOB data type.

MEMBER PROCEDURE GET_WHERE_CLAUSE(
   where_clause     IN/OUT  CLOB,
   variable_list    IN/OUT  LCR$_ROW_LIST,
   bind_var_syntax  IN      VARCHAR2  DEFAULT ':');

Parameters

GET_XML_INFORMATION Member Function

Gets the XML information for the specified column.

The return value can be one of the following:

DBMS_LCR.NOT_XML   CONSTANT NUMBER := 1;
DBMS_LCR.XML_DOC   CONSTANT NUMBER := 2;
DBMS_LCR.XML_DIFF  CONSTANT NUMBER := 3;

DBMS_LCR.NOT_XML indicates that the column is not an XMLType column. DBMS_LCR.XML_DOC indicates that the column contains an XML document. DBMS_LCR.XML_DIFF indicates that the column contains differences between old and new XML documents for an update operation.

Returns NULL if the specified column does not exist.

Syntax

MEMBER FUNCTION GET_XML_INFORMATION(
   column_name  IN  VARCHAR2)
RETURN NUMBER;

Parameter

IS_STATEMENT_LCR Member Function

This function is reserved for internal use only.

KEEP_COLUMNS Member Procedure

This procedure keeps a list of columns in a row LCR. The procedure deletes columns that are not in the list from the row LCR.

Syntax

MEMBER PROCEDURE KEEP_COLUMNS(
   column_list  IN  VARCHAR2,
   value_type   IN  VARCHAR2  DEFAULT '*');

Parameters

RENAME_COLUMN Member Procedure

Renames a column in a row LCR.

Syntax

MEMBER PROCEDURE RENAME_COLUMN(
   from_column_name  IN  VARCHAR2,
   to_column_name    IN  VARCHAR2,
   value_type        IN  VARCHAR2  DEFAULT '*');

Parameters

SET_LOB_INFORMATION Member Procedure

Sets LOB information for the column.

Syntax

MEMBER PROCEDURE SET_LOB_INFORMATION(
  value_type       IN  VARCHAR2,
  column_name      IN  VARCHAR2,
  lob_information  IN  NUMBER);

Parameters

DBMS_LCR.NOT_A_LOB        CONSTANT NUMBER := 1;
  DBMS_LCR.NULL_LOB         CONSTANT NUMBER := 2;
  DBMS_LCR.INLINE_LOB       CONSTANT NUMBER := 3;
  DBMS_LCR.EMPTY_LOB        CONSTANT NUMBER := 4;
  DBMS_LCR.LOB_CHUNK        CONSTANT NUMBER := 5;
  DBMS_LCR.LAST_LOB_CHUNK   CONSTANT NUMBER := 6;

SET_LOB_OFFSET Member Procedure

Sets the LOB offset for the specified column in the number of characters for CLOB columns and the number of bytes for BLOB columns.

Syntax

MEMBER PROCEDURE SET_LOB_OFFSET(
   value_type   IN  VARCHAR2,
   column_name  IN  VARCHAR2,
   lob_offset   IN  NUMBER);

Parameters

SET_LOB_OPERATION_SIZE Member Procedure

Sets the operation size for the LOB column in the number of characters for CLOB columns and bytes for BLOB columns.

Syntax

MEMBER PROCEDURE SET_LOB_OPERATION_SIZE(
  value_type          IN  VARCHAR2,
  column_name         IN  VARCHAR2,
  lob_operation_size  IN  NUMBER);

Parameters

SET_ROW_TEXT Member Procedure

This procedure is reserved for internal use only.

SET_VALUE Member Procedure

Overwrites the old or new value of the specified column.

One reason to overwrite an old value for a column is to resolve an error that resulted from a conflict.

Considerations for LOB Columns

When processing a row LCR with LOB columns with a procedure DML handler or error handler, and the handler is using LOB assembly (the assemble_lobs parameter is set to TRUE for the handler), you can use this member procedure in the handler procedure on a LOB column in a row LCR. If assemble_lobs is set to FALSE for the handler, then you cannot use this member procedure on a LOB column.

To use a DML or error handler to set the value of a LOB column, specify the LOB locator for the column_value parameter in the member procedure. The SET_VALUE member procedure verifies that an ANYDATA encapsulated LOB locator is processed with a DML or error handler that is using LOB assembly. An error is raised under the following conditions:

• The handler attempts to enqueue a row LCR with an ANYDATA encapsulated LOB locator.

• An attempt is made to set a LOB column incorrectly.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the LOB is represented by the original (nonassembled) row LCRs.

Considerations for XMLType Columns

When processing a row LCR with XMLType columns with a procedure DML handler or error handler, any XMLType columns and LOB columns in the LCR are always assembled using LOB assembly. You can use this member procedure in the handler procedure on a row LCR that contains one or more XMLType columns.

To use a DML or error handler to set the value an XMLType column, specify the XMLType for the column_value parameter. The SET_VALUE member procedure verifies that an ANYDATA encapsulated XMLType is processed with a DML or error handler. An error is raised under the following conditions:

• The handler attempts to enqueue a row LCR with an ANYDATA encapsulated XMLType.

• An attempt is made to set a XMLType column incorrectly.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the XMLType column is represented by the original (nonassembled) row LCRs.

Syntax

MEMBER PROCEDURE SET_VALUE(
   value_type    IN  VARCHAR2,
   column_name   IN  VARCHAR2,
   column_value  IN  ANYDATA);

Parameters

SET_VALUES Member Procedure

Replaces all old values or all new values for the LCR, depending on the value type specified.

Considerations for LOB Columns

You can use this procedure when processing a row LCR with LOB columns with a procedure DML handler or error handler. If the handler is using LOB assembly (the assemble_lobs parameter is set to TRUE for the handler), then you can use this member procedure in the handler procedure. If assemble_lobs is set to FALSE for the handler, then you cannot use this member procedure on a row LCR.

To use a DML or error handler to set the value of one or more LOB columns in a row LCR, specify a LOB locator for each LOB column in the value_list parameter. The SET_VALUES member procedure verifies that an ANYDATA encapsulated LOB locator is processed with a DML or error handler that is using LOB assembly. An error is raised under the following conditions:

• The handler attempts to enqueue a row LCR with an ANYDATA encapsulated LOB locator.

• An attempt is made to set a LOB column incorrectly.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the LOB columns are represented by the original (nonassembled) row LCRs.

Considerations for XMLType Columns

When processing a row LCR with XMLType columns with a procedure DML handler or error handler, any XMLType and LOB columns in the LCR are always assembled using LOB assembly. You can use this member procedure in the handler procedure on a row LCR that contains one or more XMLType columns.

To use a DML or error handler to set the value of one or more XMLType columns in a row LCR, specify an XMLType for each XMLType column in the value_list parameter. The SET_VALUES member procedure verifies that an ANYDATA encapsulated XMLType is processed with a DML or error handler. An error is raised under the following conditions:

• The handler attempts to enqueue a row LCR with an ANYDATA encapsulated XMLType.

• An attempt is made to set a XMLType incorrectly.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the XMLType columns are represented by the original (nonassembled) row LCRs.

Syntax

MEMBER PROCEDURE SET_VALUES(
   value_type  IN  VARCHAR2,
   value_list  IN  SYS.LCR$_ROW_LIST);

Parameters

SET_XML_INFORMATION Member Procedure

Sets the XML information for the column.

Syntax

MEMBER PROCEDURE SET_XML_INFORMATION(
   column_name      IN  VARCHAR2,
   xml_information  IN  NUMBER);

Parameters

DBMS_LCR.NOT_XML   CONSTANT NUMBER := 1;
  DBMS_LCR.XML_DOC   CONSTANT NUMBER := 2;
  DBMS_LCR.XML_DIFF  CONSTANT NUMBER := 3;

Common Subprograms for LCR$_DDL_RECORD and LCR$_ROW_RECORD

The following functions and procedures are common to both the LCR$_DDL_RECORD and LCR$_ROW_RECORD type.

GET_COMMAND_TYPE Member Function

Gets the command type of the LCR.

Syntax

MEMBER FUNCTION GET_COMMAND_TYPE() 
RETURN VARCHAR2;

GET_COMMIT_SCN Member Function

Gets the commit system change number (SCN) of the transaction to which the current LCR belongs.

The commit SCN for a transaction is available only during apply or during error transaction execution. This function can be used only in a procedure DML handler, DDL handler, or error handler.

The commit SCN might not be available for an LCR that is part of an incomplete transaction. For example, persistent LCRs might not have a commit SCN. If the commit SCN is not available for an LCR, then this function returns NULL.

Syntax

MEMBER FUNCTION GET_COMMIT_SCN() 
RETURN NUMBER;

GET_COMMIT_SCN_FROM_POSITION Static Function

Gets the commit system change number (SCN) of a transaction from the input position, which is generated by an XStream outbound server.

Syntax

STATIC FUNCTION GET_COMMIT_SCN_FROM_POSITION(
   position  IN  RAW) 
RETURN NUMBER;

Parameters

GET_COMMIT_TIME

Gets the commit time of the transaction to which the current LCR belongs.

The commit time for a transaction is available only during apply or during error transaction execution. This function can be used only in a procedure DML handler, DDL handler, or error handler.

The commit time might not be available for an LCR that is part of an incomplete transaction. For example, persistent LCRs might not have a commit time. If the commit time is not available for an LCR, then this function returns NULL.

Syntax

MEMBER FUNCTION GET_COMMIT_TIME() 
RETURN DATE;

GET_COMPATIBLE Member Function

Gets the minimal database compatibility required to support the LCR. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

The return value for this function can be one of the following:

DDL LCRs always return DBMS_STREAMS.COMPATIBLE_9_2.

You can use the following functions in the DBMS_STREAMS package for constant compatibility return values:

• The COMPATIBLE_9_2 function returns the DBMS_STREAMS.COMPATIBLE_9_2 constant.

• The COMPATIBLE_10_1 function returns DBMS_STREAMS.COMPATIBLE_10_1 constant.

• The COMPATIBLE_10_2 function returns DBMS_STREAMS.COMPATIBLE_10_2 constant.

• The COMPATIBLE_11_1 function returns DBMS_STREAMS.COMPATIBLE_11_1 constant.

• The COMPATIBLE_11_2 function returns DBMS_STREAMS.COMPATIBLE_11_2 constant.

• The MAX_COMPATIBLE function returns an integer that is greater than the highest possible compatibility constant for the current release of Oracle Database.

You can use these functions with the GET_COMPATIBLE member function for an LCR in rule conditions and apply handlers.

Syntax

MEMBER FUNCTION GET_COMPATIBLE() 
RETURN NUMBER;

GET_EXTRA_ATTRIBUTE Member Function

Gets the value for the specified extra attribute in the LCR. The returned extra attribute is contained within an ANYDATA instance. You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to capture one or more extra attributes.

Syntax

MEMBER FUNCTION GET_EXTRA_ATTRIBUTE(
   attribute_name  IN  VARCHAR2) 
RETURN ANYDATA;

Parameters

GET_OBJECT_NAME Member Function

Gets the name of the object that is changed by the LCR.

Syntax

MEMBER FUNCTION GET_OBJECT_NAME()
RETURN VARCHAR2;

GET_OBJECT_OWNER Member Function

Gets the owner of the object that is changed by the LCR.

Syntax

MEMBER FUNCTION GET_OBJECT_OWNER() 
RETURN VARCHAR2;

GET_POSITION Member Function

Gets the position of the current LCR. The position uniquely identifies each LCR. The position strictly increases within each transaction and across transactions.

LCR position is commonly used in XStream configurations.

Syntax

MEMBER FUNCTION GET_POSITION() 
RETURN RAW;

GET_SCN Member Function

Gets the system change number (SCN) of the LCR.

Syntax

MEMBER FUNCTION GET_SCN() 
RETURN NUMBER;

GET_SCN_FROM_POSITION Static Function

Gets the system change number (SCN) from the input position, which is generated by an XStream outbound server.

Syntax

STATIC FUNCTION GET_SCN_FROM_POSITION(
   position  IN  RAW) 
RETURN NUMBER;

Parameters

GET_SOURCE_DATABASE_NAME Member Function

Gets the global name of the source database. The source database is the database where the change occurred.

Syntax

MEMBER FUNCTION GET_SOURCE_DATABASE_NAME() 
RETURN VARCHAR2;

GET_SOURCE_TIME Member Function

Gets the time when the change in an LCR captured by a capture process was generated in the redo log of the source database, or the time when a persistent LCR was created.

Syntax

MEMBER FUNCTION GET_SOURCE_TIME() 
RETURN DATE;

GET_TAG Member Function

Gets the tag for the LCR. An LCR tag is a binary tag that enables tracking of the LCR. For example, this tag can be used to determine the original source database of the DML or DDL change when apply forwarding is used.

Syntax

MEMBER FUNCTION GET_TAG() 
RETURN RAW;

GET_THREAD_NUMBER Member Function

Gets the thread number of the database instance that made the change that is encapsulated in the LCR. Typically, the thread number is relevant in an Oracle Real Application Clusters configuration.

Syntax

MEMBER FUNCTION GET_THREAD_NUMBER()
RETURN NUMBER;

GET_TRANSACTION_ID Member Function

Gets the transaction identifier of the LCR.

Syntax

MEMBER FUNCTION GET_TRANSACTION_ID() 
RETURN VARCHAR2;

IS_NULL_TAG Member Function

Returns Y if the tag for the LCR is NULL, or returns N if the tag for the LCR is not NULL.

Syntax

MEMBER FUNCTION IS_NULL_TAG() 
RETURN VARCHAR2;

SET_COMMAND_TYPE Member Procedure

Sets the command type in the LCR. If the command type specified cannot be interpreted, then this procedure raises an error. For example, changing INSERT to GRANT would raise an error.

Syntax

MEMBER PROCEDURE SET_COMMAND_TYPE(
   command_type  IN  VARCHAR2);

Parameter

SET_EXTRA_ATTRIBUTE Member Procedure

Sets the value for the specified extra attribute in the LCR. You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to capture one or more extra attributes.

Syntax

MEMBER PROCEDURE SET_EXTRA_ATTRIBUTE(
   attribute_name   IN  VARCHAR2,
   attribute_value  IN  ANYDATA);

Parameters

SET_OBJECT_NAME Member Procedure

Sets the name of the object that is changed by the LCR.

Syntax

MEMBER PROCEDURE SET_OBJECT_NAME(
   object_name  IN  VARCHAR2);

Parameter

SET_OBJECT_OWNER Member Procedure

Sets the owner of the object that is changed by the LCR.

Syntax

MEMBER PROCEDURE SET_OBJECT_OWNER(
   object_owner  IN  VARCHAR2);

Parameter

SET_SOURCE_DATABASE_NAME Member Procedure

Sets the source database name of the object that is changed by the LCR.

Syntax

MEMBER PROCEDURE SET_SOURCE_DATABASE_NAME(
   source_database_name  IN  VARCHAR2);

Parameter

SET_TAG Member Procedure

Sets the tag for the LCR. An LCR tag is a binary tag that enables tracking of the LCR. For example, this tag can be used to determine the original source database of the change when apply forwarding is used.

Syntax

MEMBER PROCEDURE SET_TAG(
   tag  IN  RAW);

Parameter

LCR$_ROW_LIST Type

Identifies a list of column values for a row in a table.

This type uses the LCR$_ROW_UNIT type and is used in the LCR$_ROW_RECORD type.

Syntax

CREATE TYPE SYS.LCR$_ROW_LIST AS TABLE OF SYS.LCR$_ROW_UNIT
/

LCR$_ROW_UNIT Type

Identifies the value for a column in a row.

This type is used in the LCR$_ROW_LIST type.

Syntax

CREATE TYPE LCR$_ROW_UNIT AS OBJECT (
  column_name         VARCHAR2(4000),
  data                ANYDATA,
  lob_information     NUMBER,
  lob_offset          NUMBER,
  lob_operation_size  NUMBER
  long_information    NUMBER);
/

Attributes

DBMS_LCR.NOT_A_LOB        CONSTANT NUMBER := 1;
  DBMS_LCR.NULL_LOB         CONSTANT NUMBER := 2;
  DBMS_LCR.INLINE_LOB       CONSTANT NUMBER := 3;
  DBMS_LCR.EMPTY_LOB        CONSTANT NUMBER := 4;
  DBMS_LCR.LOB_CHUNK        CONSTANT NUMBER := 5;
  DBMS_LCR.LAST_LOB_CHUNK   CONSTANT NUMBER := 6;