PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
This chapter describes the logical change record (LCR) types. In Streams, 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 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.
See Also:
Oracle Streams Concepts and Administration for more information about LCRs |
This chapter contains these topics:
These 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
This type represents a data definition language (DDL) change to a database object.
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.
This section contains information about the constructor for DDL LCRs and information about the member subprograms for this type:
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) RETURN SYS.LCR$_DDL_RECORD;
Parameter | Description |
---|---|
|
The database where the DDL statement occurred. If you do not include the domain name, then the local domain is appended to the database name automatically. For example, if you specify |
|
The type of command executed in the DDL statement. This parameter should be set to a non- See Also: The "SQL Command Codes" table in the Oracle Call Interface Programmer's Guide for a complete list of command types The following command types are not supported in DDL LCRs: 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 The snapshot equivalents of the materialized view command types are also not supported. |
|
The user who owns the object on which the DDL statement was executed |
|
The database object on which the DDL statement was executed |
|
The type of object on which the DDL statement was executed. The following are valid object types: CLUSTER FUNCTION INDEX LINK OUTLINE PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYM TABLE TRIGGER TYPE USER VIEW
|
|
The text of the DDL statement. This parameter should be set to a non- |
|
The user whose session executed the DDL statement |
|
The schema that is used if no schema is specified explicitly for the modified database objects in This parameter should be set to a non- |
|
If the DDL statement is a table related DDL (such as |
|
If the DDL statement is a table related DDL (such as |
|
A binary tag that enables tracking of the LCR. For example, this tag may be used to determine the original source database of the DDL statement if apply forwarding is used. See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The identifier of the transaction |
|
The SCN at the time when the change record for a captured LCR was written to the redo log. The SCN value is meaningless for a user-created LCR. |
Subprogram | Description |
---|---|
Executes the LCR under the security domain of the current user | |
Returns the base (dependent) table name | |
Returns the base (dependent) table owner | |
Returns the default schema (user) name | |
Gets the DDL text in a | |
Returns the logon user name | |
Returns the type of the object involved for the DDL | |
Sets the base (dependent) table name | |
Sets the base (dependent) table owner | |
Sets the default schema (user) name | |
Sets the DDL text | |
Sets the logon user name | |
Sets the object type | |
Common Subprograms |
See "Common Subprograms for LCR$_DDL_RECORD and LCR$_ROW_RECORD" for a list of subprograms common to the |
Executes the DDL LCR under the security domain of the current user. Any apply process handlers that would be run for an LCR are not run when the LCR is applied using this procedure.
MEMBER PROCEDURE EXECUTE();
Returns the base (dependent) table name.
MEMBER FUNCTION GET_BASE_TABLE_NAME() RETURN VARCHAR2;
Returns the base (dependent) table owner.
MEMBER FUNCTION GET_BASE_TABLE_OWNER() RETURN VARCHAR2;
Returns the current schema name.
MEMBER FUNCTION GET_CURRENT_SCHEMA() RETURN VARCHAR2;
Gets the DDL text in a CLOB
.
The following is an example of a PL/SQL procedure that 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; /
MEMBER FUNCTION GET_DDL_TEXT ddl_text IN/OUT CLOB);
Parameter | Description |
---|---|
|
The DDL text in the DDL LCR |
Returns the logon user name.
MEMBER FUNCTION GET_LOGON_USER() RETURN VARCHAR2;
Returns the type of the object involved for the DDL.
MEMBER FUNCTION GET_OBJECT_TYPE() RETURN VARCHAR2;
Sets the base (dependent) table name.
MEMBER PROCEDURE SET_BASE_TABLE_NAME( base_table_name IN VARCHAR2);
Parameter | Description |
---|---|
|
The name of the base table |
Sets the base (dependent) table owner.
MEMBER PROCEDURE SET_BASE_TABLE_OWNER( base_table_owner IN VARCHAR2);
Parameter | Description |
---|---|
|
The name of the base owner |
Sets the default schema (user) name.
MEMBER PROCEDURE SET_CURRENT_SCHEMA( current_schema IN VARCHAR2);
Parameter | Description |
---|---|
|
The name of the schema to set as the current schema. This parameter should be set to a non- |
Sets the DDL text.
MEMBER PROCEDURE SET_DDL_TEXT( ddl_text IN CLOB);
Parameter | Description |
---|---|
|
The DDL text. This parameter should be set to a non- |
Sets the logon user name.
MEMBER PROCEDURE SET_LOGON_USER( logon_user IN VARCHAR2);
Parameter | Description |
---|---|
|
The name of the schema to set as the logon user |
Sets the object type.
MEMBER PROCEDURE SET_OBJECT_TYPE( object_type IN VARCHAR2);
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 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:
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) RETURN SYS.LCR$_ROW_RECORD;
Parameter | Description |
---|---|
|
The database where the row change occurred. If you do not include the domain name, then the local domain is appended to the database name automatically. For example, if you specify |
|
The type of command executed in the DML statement. This parameter should be set to a non- Valid values are the following: INSERT UPDATE DELETE LOB ERASE LOB WRITE LOB TRIM If If If If |
|
The user who owns the table on which the row change occurred. This parameter should be set to a non- |
|
The table on which the DML statement was executed. This parameter should be set to a non- |
|
A binary tag that enables tracking of the LCR. For example, this tag may be used to determine the original source database of the DML change when apply forwarding is used. See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The identifier of the transaction |
|
The SCN at the time when the change record was written to the redo log. The SCN value is meaningless for a user-created LCR. |
|
The column values for the row before the DML change. If the DML statement is an |
|
The column values for the row after the DML change. If the DML statement is an If the LCR reflects a LOB operation, then the supplementally logged columns and any relevant LOB information. |
Subprogram | Description |
---|---|
Adds the value as old or new, depending on the value type specified, for the column | |
Converts | |
Deletes the old value, the new value, or both, for the specified column, depending on the value type specified | |
Executes the LCR under the security domain of the current user | |
Gets the LOB information for the column | |
Returns the LOB offset for the specified column | |
Gets the operation size for the | |
Gets the | |
Returns the old or new value for the specified column, depending on the value type specified | |
Returns a list of old or new values, depending on the value type specified | |
Renames a column in an LCR | |
Sets LOB information for the column | |
Sets the LOB offset for the specified column | |
Sets the operation size for the LOB column | |
Replaces the existing old or new values for the LCR, depending on the value type specified | |
Common Subprograms |
See "Common Subprograms for LCR$_DDL_RECORD and LCR$_ROW_RECORD" for a list of subprograms common to the |
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.
To set a column value that already exists, run SET_VALUE
.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure on a LOB column. |
MEMBER PROCEDURE ADD_COLUMN( value_type IN VARCHAR2, column_name IN VARCHAR2, column_value IN SYS.AnyData);
Converts LONG
data in a row LCR into a CLOB
, or converts LONG
RAW
data in a row LCR into a BLOB
.
This procedure may change the operation code from LONG_WRITE
to LOB_WRITE
for the row LCR.
This procedure may be used in rule-based transformations and apply handlers.
The following restrictions apply to this member procedure:
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 may be sent in multiple pieces, make sure that this method is invoked on either none or all LONG
pieces.LONG
conversion is not supported.MEMBER PROCEDURE CONVERT_LONG_TO_LOB_CHUNK();
Deletes the old value, the new value, or both, for the specified column, depending on the value type specified.
MEMBER PROCEDURE DELETE_COLUMN( column_name IN VARCHAR2, value_type IN VARCHAR2 DEFAULT '*');
Executes the row LCR under the security domain of the current user. Any apply process handlers that would be run for an LCR are not run when the LCR is applied using this procedure.
MEMBER PROCEDURE EXECUTE( conflict_resolution IN BOOLEAN);
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.
MEMBER FUNCTION GET_LOB_INFORMATION( value_type IN VARCHAR2, column_name IN VARCHAR2, use_old IN VARCHAR2 DEFAULT 'Y') RETURN NUMBER;
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:
DBMS_LCR.LAST_LOB_CHUNK
or DBMS_LCR.LOB_CHUNK
LOB
ERASE
or LOB
WRITE
Otherwise, returns NULL
.
GET_LOB_OFFSET( value_type IN VARCHAR2, column_name IN VARCHAR2) RETURN NUMBER;
Parameter | Description |
---|---|
|
The type of value to return for the column. Currently, only |
|
The name of the LOB column |
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:
LOB
ERASE
or LOB
TRIM
DBMS_LCR.LAST_LOB_CHUNK
Otherwise, returns NULL
.
MEMBER FUNCTION GET_LOB_OPERATION_SIZE( value_type IN VARCHAR2, column_name IN VARCHAR2) RETURN NUMBER,
Parameter | Description |
---|---|
|
The type of value to return for the column. Currently, only |
|
The name of the LOB column |
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.
MEMBER FUNCTION GET_LONG_INFORMATION( value_type IN VARCHAR2, column_name IN VARCHAR2, use_old IN VARCHAR2 DEFAULT 'Y') RETURN NUMBER;
Returns 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.
MEMBER FUNCTION GET_VALUE( value_type IN VARCHAR2, column_name IN VARCHAR2, use_old IN VARCHAR2 DEFAULT 'Y') RETURN SYS.AnyData;
Returns 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.
MEMBER FUNCTION GET_VALUES( value_type IN VARCHAR2, use_old IN VARCHAR2 DEFAULT 'Y') RETURN SYS.LCR$_ROW_LIST;
Renames a column in an LCR.
MEMBER PROCEDURE RENAME_COLUMN( from_column_name IN VARCHAR2, to_column_name IN VARCHAR2, value_type IN VARCHAR2 DEFAULT '*');
Sets LOB information for the column.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure. |
MEMBER PROCEDURE SET_LOB_INFORMATION( value_type IN VARCHAR2, column_name IN VARCHAR2, lob_information IN NUMBER);
Sets the LOB offset for the specified column in the number of characters for CLOB
columns and the number of bytes for BLOB
columns.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure. |
MEMBER PROCEDURE SET_LOB_OFFSET( value_type IN VARCHAR2, column_name IN VARCHAR2, lob_offset IN NUMBER);
Sets the operation size for the LOB column in the number of characters for CLOB
columns and bytes for BLOB
columns.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure. |
MEMBER PROCEDURE SET_LOB_OPERATION_SIZE( value_type IN VARCHAR2, column_name IN VARCHAR2, lob_operation_size IN NUMBER);
Overwrites the old or new value of the specified column.
One reason you may want to overwrite an old value for a column is to resolve an error that resulted from a conflict.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure on a |
MEMBER PROCEDURE SET_VALUE( value_type IN VARCHAR2, column_name IN VARCHAR2, column_value IN SYS.AnyData);
Replaces all old values or all new values for the LCR, depending on the value type specified.
Note: When you are processing a row LCR with a rule-based transformation, DML handler, or error handler, you cannot use this member procedure on a |
MEMBER PROCEDURE SET_VALUES( value_type IN VARCHAR2, value_list IN SYS.LCR$_ROW_LIST);
The following functions and procedures are common to both the LCR$_DDL_RECORD
and LCR$_ROW_RECORD
type.
Returns the command type of the LCR.
See Also:
The "SQL Command Codes" table in the Oracle Call Interface Programmer's Guide for a complete list of command types |
MEMBER FUNCTION GET_COMMAND_TYPE() RETURN VARCHAR2;
Returns 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 DML handler, DDL handler, or error handler. Such a handler may use the SCN obtained by this procedure to flashback to the transaction commit time for an LCR. In this case, the flashback must be performed at the source database for the LCR.
The commit SCN may not be available for an LCR that is part of an incomplete transaction. For example, user-enqueued LCRs may not have a commit SCN. If the commit SCN is not available for an LCR, then this function returns NULL
.
MEMBER FUNCTION GET_COMMIT_SCN() RETURN NUMBER;
Returns 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:
Return Value | COMPATIBLE Initialization Parameter Equivalent |
---|---|
|
|
|
|
DDL LCRs always return DBMS_STREAMS.COMPATIBLE_9_2
.
You may use the following functions for constant compatibility return values:
DBMS_STREAMS.COMPATIBLE_9_2
function returns the DBMS_STREAMS.COMPATIBLE_9_2
constant.DBMS_STREAMS.COMPATIBLE_10_1
function returns DBMS_STREAMS.COMPATIBLE_10_1
constant.You can use these functions with the GET_COMPATIBLE
member function for an LCR in rule conditions and apply handlers.
Note: You can determine which database objects in a database are not supported by Streams by querying the |
See Also:
|
MEMBER FUNCTION GET_COMPATIBLE() RETURN NUMBER;
Returns the value for the specified extra attribute in the LCR. The returned extra attribute is contained within a SYS.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.
MEMBER FUNCTION GET_EXTRA_ATTRIBUTE( attribute_name IN VARCHAR2) RETURN SYS.AnyData;
Returns the name of the object that is changed by the LCR.
MEMBER FUNCTION GET_OBJECT_NAME() RETURN VARCHAR2;
Returns the owner of the object that is changed by the LCR.
MEMBER FUNCTION GET_OBJECT_OWNER() RETURN VARCHAR2;
Returns the system change number (SCN) of the LCR.
MEMBER FUNCTION GET_SCN() RETURN NUMBER;
Returns the global name of the source database name. The source database is the database where the change occurred.
MEMBER FUNCTION GET_SOURCE_DATABASE_NAME() RETURN VARCHAR2;
Returns the tag for the LCR. An LCR tag is a binary tag that enables tracking of the LCR. For example, this tag may be used to determine the original source database of the DML or DDL change when apply forwarding is used.
See Also:
Oracle Streams Replication Administrator's Guide for more information about tags |
MEMBER FUNCTION GET_TAG() RETURN RAW;
Returns the transaction identifier of the LCR.
MEMBER FUNCTION GET_TRANSACTION_ID() RETURN VARCHAR2;
Returns Y
if the tag for the LCR is NULL
, or returns N
if the tag for the LCR is not NULL
.
See Also:
Oracle Streams Replication Administrator's Guide for more information about tags |
MEMBER FUNCTION IS_NULL_TAG() RETURN VARCHAR2;
Sets the command type in the LCR. If the command type specified cannot be interpreted, then an error is raised. For example, changing INSERT
to GRANT
would raise an error.
See Also:
|
MEMBER PROCEDURE SET_COMMAND_TYPE( command_type IN VARCHAR2);
Parameter | Description |
---|---|
|
The command type. This parameter should be set to a non- |
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.
MEMBER PROCEDURE SET_EXTRA_ATTRIBUTE( attribute_name IN VARCHAR2, attribute_value IN SYS.AnyData);
Sets the name of the object that is changed by the LCR.
MEMBER PROCEDURE SET_OBJECT_NAME( object_name IN VARCHAR2);
Parameter | Description |
---|---|
|
The name of the object |
Sets the owner of the object that is changed by the LCR.
MEMBER PROCEDURE SET_OBJECT_OWNER( object_owner IN VARCHAR2);
Parameter | Description |
---|---|
|
The schema that contains the object |
Sets the source database name of the object that is changed by the LCR.
MEMBER PROCEDURE SET_SOURCE_DATABASE_NAME( source_database_name IN VARCHAR2);
Sets the tag for the LCR. An LCR tag is a binary tag that enables tracking of the LCR. For example, this tag may be used to determine the original source database of the change when apply forwarding is used.
See Also:
Oracle Streams Replication Administrator's Guide for more information about tags |
MEMBER PROCEDURE SET_TAG( tag IN RAW);
Parameter | Description |
---|---|
|
The binary tag for the LCR. The size limit for a tag value is two kilobytes. |
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.
CREATE TYPE SYS.LCR$_ROW_LIST AS TABLE OF SYS.LCR$_ROW_UNIT /
Identifies the value for a column in a row.
This type is used in the LCR$_ROW_LIST
type.
CREATE TYPE LCR$_ROW_UNIT AS OBJECT ( column_name VARCHAR2(4000), data SYS.AnyData, lob_information NUMBER, lob_offset NUMBER, lob_operation_size NUMBER long_information NUMBER); /