158 DBMS_STREAMS

The DBMS_STREAMS package, one of a set of Oracle Streams packages, provides subprograms to convert ANYDATA objects into logical change record (LCR) objects, to return information about Oracle Streams attributes and Oracle Streams clients, and to annotate redo entries generated by a session with a binary tag. This tag affects the behavior of a capture process, a propagation, or an apply process whose rules include specifications for these binary tags in redo entries or LCRs.

This chapter contains the following topics:

Using DBMS_STREAMS
- Overview
- Security Model
Summary of DBMS_STREAMS Subprograms

Using DBMS_STREAMS

This section contains topics which relate to using the DBMS_STREAMS package.

Overview
Security Model

Overview

This package provides subprograms to convert ANYDATA objects into logical change record (LCR) objects, to return information about Oracle Streams attributes and Oracle Streams clients, and to annotate redo entries generated by a session with a binary tag. This tag affects the behavior of a capture process, a propagation, or an apply process whose rules include specifications for these binary tags in redo entries or LCRs.

See Also:

Oracle Streams Concepts and Administration and Oracle Streams Replication Administrator's Guide for more information about this package and Oracle Streams

Security Model

PUBLIC is granted EXECUTE privilege on this package.

See Also:

Oracle Database Security Guide for more information about user group PUBLIC

Summary of DBMS_STREAMS Subprograms

Table 158-1 DBMS_STREAMS Package Subprograms

Subprogram	Description
COMPATIBLE_12_1 Function	Returns the `DBMS_STREAMS.COMPATIBLE_12_1` constant
COMPATIBLE_11_2 Function	Returns the `DBMS_STREAMS.COMPATIBLE_11_2` constant
COMPATIBLE_11_1 Function	Returns the `DBMS_STREAMS.COMPATIBLE_11_1` constant
COMPATIBLE_10_2 Function	Returns the `DBMS_STREAMS.COMPATIBLE_10_2` constant
COMPATIBLE_10_1 Function	Returns the `DBMS_STREAMS.COMPATIBLE_10_1` constant
COMPATIBLE_9_2 Function	Returns the `DBMS_STREAMS.COMPATIBLE_9_2` constant
CONVERT_ANYDATA_TO_LCR_DDL Function	Converts a `ANYDATA` object to a `SYS.LCR$_DDL_RECORD` object
CONVERT_ANYDATA_TO_LCR_ROW Function	Converts a `ANYDATA` object to a `SYS.LCR$_ROW_RECORD` object
CONVERT_LCR_TO_XML Function	Converts a logical change record (LCR) encapsulated in a `ANYDATA` object into an XML object that conforms to the XML schema for LCRs
CONVERT_XML_TO_LCR Function	Converts an XML object that conforms to the XML schema for LCRs into a logical change record (LCR) encapsulated in a `ANYDATA` object
GET_INFORMATION Function	Returns information about various Oracle Streams attributes
GET_STREAMS_NAME Function	Returns the name of the invoker
GET_STREAMS_TYPE Function	Returns the type of the invoker
GET_TAG Function	Gets the binary tag for all redo entries generated by the current session
MAX_COMPATIBLE Function	Returns an integer that is greater than the highest possible compatibility constant for the current release of Oracle Database
SET_TAG Procedure	Sets the binary tag for all redo entries subsequently generated by the current session

Note:

The subprograms in this package do not commit.

COMPATIBLE_12_1 Function

This function returns the DBMS_STREAMS.COMPATIBLE_12_1 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_12_1
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 12.1.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

COMPATIBLE_11_2 Function

This function returns the DBMS_STREAMS.COMPATIBLE_11_2 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_11_2
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 11.2.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

COMPATIBLE_11_1 Function

This function returns the DBMS_STREAMS.COMPATIBLE_11_1 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_11_1
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 11.1.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

COMPATIBLE_10_2 Function

This function returns the DBMS_STREAMS.COMPATIBLE_10_2 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_10_2
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 10.2.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

COMPATIBLE_10_1 Function

This function returns the DBMS_STREAMS.COMPATIBLE_10_1 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_10_1
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 10.1.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

COMPATIBLE_9_2 Function

This function returns the DBMS_STREAMS.COMPATIBLE_9_2 constant.

Syntax

DBMS_STREAMS.COMPATIBLE_9_2
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The constant value returned by this function corresponds to 9.2.0 compatibility in a database. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

CONVERT_ANYDATA_TO_LCR_DDL Function

This function converts a ANYDATA object into a SYS.LCR$_DDL_RECORD object.

Syntax

DBMS_STREAMS.CONVERT_ANYDATA_TO_LCR_DDL(
   source  IN  ANYDATA) 
RETURN SYS.LCR$_DDL_RECORD;

Parameters

Table 158-2 CONVERT_ANYDATA_TO_LCR_DDL Function Parameters

Parameter	Description
`source`	The `ANYDATA` object to be converted. If this object is not a DDL logical change record (DDL LCR), then the function raises an exception.

Usage Notes

You can use this function in a transformation created by the CREATE_TRANSFORMATION procedure in the DBMS_TRANSFORM package. Use the transformation you create when you add a subscriber for propagation of DDL LCRs from a ANYDATA queue to a SYS.LCR$_DDL_RECORD typed queue.

CONVERT_ANYDATA_TO_LCR_ROW Function

This function converts a ANYDATA object into a SYS.LCR$_ROW_RECORD object.

Syntax

DBMS_STREAMS.CONVERT_ANYDATA_TO_LCR_ROW(
   source  IN  ANYDATA) 
RETURN SYS.LCR$_ROW_RECORD;

Parameters

Table 158-3 CONVERT_ANYDATA_TO_LCR_ROW Function Parameters

Parameter	Description
`source`	The `ANYDATA` object to be converted. If this object is not a row logical change record (row LCR), then the function raises an exception.

Usage Notes

You can use this function in a transformation created by the CREATE_TRANSFORMATION procedure in the DBMS_TRANSFORM package. Use the transformation you create when you add a subscriber for propagation of row LCRs from a ANYDATA queue to a SYS.LCR$_ROW_RECORD typed queue.

CONVERT_LCR_TO_XML Function

This function converts a logical change record (LCR) encapsulated in a ANYDATA object into an XML object that conforms to the XML schema for LCRs. The LCR can be a row LCR or a DDL LCR.

See Also:

Oracle Streams Concepts and Administration for more information about the XML schema for LCRs

Syntax

DBMS_STREAMS.CONVERT_LCR_TO_XML(
   anylcr  IN  ANYDATA) 
RETURN SYS.XMLTYPE;

Parameters

Table 158-4 CONVERT_LCR_TO_XML Function Parameters

Parameter	Description
`anylcr`	The `ANYDATA` encapsulated LCR to be converted. If this object is not a `ANYDATA` encapsulated LCR, then the function raises an exception.

CONVERT_XML_TO_LCR Function

This function converts an XML object that conforms to the XML schema for logical change records (LCRs) into an LCR encapsulated in a ANYDATA object. The LCR can be a row or DDL LCR.

See Also:

Oracle Streams Concepts and Administration for more information about the XML schema for LCRs

Syntax

DBMS_STREAMS.CONVERT_XML_TO_LCR(
   xmldat  IN  SYS.XMLTYPE) 
RETURN ANYDATA;

Parameters

Table 158-5 CONVERT_XML_TO_LCR Function Parameters

Parameter	Description
`xmldat`	The XML LCR object to be converted. If this object does not conform to XML schema for LCRs, then the function raises an exception.

GET_INFORMATION Function

This function returns information about various Oracle Streams attributes.

Syntax

DBMS_STREAMS.GET_INFORMATION(
   name  IN  VARCHAR2) 
RETURN ANYDATA;

Parameters

Table 158-6 GET_INFORMATION Function Parameters

Parameter Description

Parameter	Description
`name`	The type of information you want to retrieve. Currently, the following names are available: `SENDER`: Returns the name of the sender for the current logical change record (LCR) from its AQ message properties. This function is called inside a procedure DML handler, a DDL handler, an error handler, or a message handler. Returns `NULL` if called outside of an apply handler. The return value is to be interpreted as a `VARCHAR2`. `CONSTRAINT_NAME`: Returns the name of the constraint that was violated for an LCR that raised an error. This function is called inside a procedure DML handler or error handler for an apply process. Returns `NULL` if called outside of a procedure DML handler or error handler. The return value is to be interpreted as a `VARCHAR2`.

name

The type of information you want to retrieve. Currently, the following names are available:

SENDER: Returns the name of the sender for the current logical change record (LCR) from its AQ message properties. This function is called inside a procedure DML handler, a DDL handler, an error handler, or a message handler. Returns NULL if called outside of an apply handler. The return value is to be interpreted as a VARCHAR2.
CONSTRAINT_NAME: Returns the name of the constraint that was violated for an LCR that raised an error. This function is called inside a procedure DML handler or error handler for an apply process. Returns NULL if called outside of a procedure DML handler or error handler. The return value is to be interpreted as a VARCHAR2.

GET_STREAMS_NAME Function

This function gets the Oracle Streams name of the invoker if the invoker is one of the following Oracle Streams types:

CAPTURE
APPLY
ERROR_EXECUTION

If the invoker is not one of these types, then this function returns a NULL.

Syntax

DBMS_STREAMS.GET_STREAMS_NAME
RETURN VARCHAR2;

Usage Notes

You can use this function in rule conditions, rule-based transformations, apply handlers, and error handlers. For example, if you use one error handler for multiple apply processes, then you can use the GET_STREAMS_NAME function to determine the name of the apply process that raised the error.

GET_STREAMS_TYPE Function

This function gets the Oracle Streams type of the invoker and returns one of the following types:

CAPTURE
APPLY
ERROR_EXECUTION

If the invoker is not one of these types, then this function returns a NULL.

Syntax

DBMS_STREAMS.GET_STREAMS_TYPE
RETURN VARCHAR2;

Usage Notes

This function can be used in rule conditions, rule-based transformations, apply handlers, and error handlers. For example, you can use the GET_STREAMS_TYPE function to instruct a procedure DML handler to operate differently if it is processing messages from the error queue (ERROR_EXECUTION type) instead of the apply process's queue (APPLY type).

GET_TAG Function

This function gets the binary tag for all redo entries generated by the current session.

Note:

To execute this function, a user must be granted either EXECUTE_CATALOG_ROLE or EXECUTE privilege on the DBMS_STREAMS_ADM package.
Instead of using the DBMS_STREAMS.GET_TAG function, Oracle recommends that you use the DBMS_STREAMS_ADM.GET_TAG function. See GET_TAG Function.

See Also:

Oracle Streams Replication Administrator's Guide for more information about tags

Syntax

DBMS_STREAMS.GET_TAG
RETURN RAW;

Examples

The following example illustrates how to display the current logical change record (LCR) tag as output:

SET SERVEROUTPUT ON
DECLARE
   raw_tag RAW(2000);
BEGIN
   raw_tag := DBMS_STREAMS.GET_TAG();
   DBMS_OUTPUT.PUT_LINE('Tag Value = ' || RAWTOHEX(raw_tag));
END;
/

You can also display the value by querying the DUAL view:

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

MAX_COMPATIBLE Function

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

Syntax

DBMS_STREAMS.MAX_COMPATIBLE
RETURN INTEGER;

Usage Notes

You can use this function with the GET_COMPATIBLE member function for logical change records (LCRs) to specify behavior based on compatibility.

The MAX_COMPATIBLE function always returns the maximum compatibility for the release of Oracle Database on which it is run. Therefore, when you use this function in rule conditions, the rule conditions do not need to be changed when you upgrade to a later release of Oracle Database.

See Also:

GET_COMPATIBLE Member Function
Oracle Streams Concepts and Administration for information about creating rules that discard changes that are not supported by Oracle Streams
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

SET_TAG Procedure

This procedure sets the binary tag for all redo entries subsequently generated by the current session. Each redo entry generated by DML or DDL statements in the current session has this tag. This procedure affects only the current session.

Note:

To execute this procedure, a user must be granted either EXECUTE_CATALOG_ROLE or EXECUTE privilege on the DBMS_STREAMS_ADM package.
Instead of using the DBMS_STREAMS.SET_TAG procedure, Oracle recommends that you use the DBMS_STREAMS_ADM.SET_TAG procedure. See SET_TAG Procedure.

See Also:

Oracle Streams Replication Administrator's Guide for more information about tags

Syntax

DBMS_STREAMS.SET_TAG(
   tag  IN  RAW  DEFAULT NULL);

Parameters

Table 158-7 SET_TAG Procedure Parameters

Parameter Description

Parameter	Description
`tag`	The binary tag for all subsequent redo entries generated by the current session. A raw value is a sequence of bytes, and a byte is a sequence of bits. By default, the tag for a session is `NULL`. The size limit for a tag value is 2000 bytes.

tag

The binary tag for all subsequent redo entries generated by the current session. A raw value is a sequence of bytes, and a byte is a sequence of bits.

By default, the tag for a session is NULL.

The size limit for a tag value is 2000 bytes.

Usage Notes

To set the tag to the hexadecimal value of '17' in the current session, run the following procedure:

EXEC DBMS_STREAMS.SET_TAG(tag => HEXTORAW('17'));

The following are considerations for the SET_TAG procedure:

This procedure is not transactional. That is, the effects of SET_TAG cannot be rolled back.
If the SET_TAG procedure is run to set a non-NULL session tag before a data dictionary build has been performed on the database, then the redo entries for a transaction that started before the dictionary build might not include the specified tag value for the session. Therefore, perform a data dictionary build before using the SET_TAG procedure in a session. A data dictionary build happens when the DBMS_CAPTURE_ADM.BUILD procedure is run. The BUILD procedure can be run automatically when a capture process is created.

See Also:

BUILD Procedure