95
DBMS_STREAMS

The DBMS_STREAMS package, one of a set of Streams packages, provides interfaces to convert SYS.AnyData objects into logical change record (LCR) objects, to return information about Streams attributes and 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
  • Security Model
• Summary of DBMS_STREAMS Subprograms

---

Using DBMS_STREAMS

• Security Model

---

Security Model

PUBLIC is granted EXECUTE privilege on this package.

---

Summary of DBMS_STREAMS Subprograms

Table 95-1  DBMS_STREAMS Package Subprograms

Subprogram	Description
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 SYS.AnyData object to a SYS.LCR$_DDL_RECORD object
CONVERT_ANYDATA_TO_LCR_ROW Function	Converts a SYS.AnyData object to a SYS.LCR$_ROW_RECORD object
GET_INFORMATION Function	Returns information about various 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
SET_TAG Procedure	Sets the binary tag for all redo entries subsequently generated by the current session

---

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

---

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 may use this function with the GET_COMPATIBLE member function for 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.

---

CONVERT_ANYDATA_TO_LCR_DDL Function

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

Syntax

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

Parameters

Table 95-2 CONVERT_ANYDATA_TO_LCR_DDL Function Parameter

Parameter	Description
source	The SYS.AnyData object to be converted. If this object is not a DDL LCR, then an exception is raised.

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 SYS.AnyData queue to a SYS.LCR$_DDL_RECORD typed queue.

---

CONVERT_ANYDATA_TO_LCR_ROW Function

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

Syntax

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

Parameters

Table 95-3 CONVERT_ANYDATA_TO_LCR_ROW Function Parameter

Parameter	Description
source	The SYS.AnyData object to be converted. If this object is not a row LCR, then an exception is raised.

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 SYS.AnyData queue to a SYS.LCR$_ROW_RECORD typed queue.

---

GET_INFORMATION Function

This function returns information about various Streams attributes.

Syntax

DBMS_STREAMS.GET_INFORMATION(
   name  IN  VARCHAR2) 
 RETURN SYS.AnyData;

Parameters

Table 95-4  GET_INFORMATION Function Parameter

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 LCR (from its AQ message properties). This function is called inside an apply handler. An apply handler is a 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 DML handler or error handler for an apply process. Returns NULL if called outside of a DML handler or error handler. The return value is to be interpreted as a VARCHAR2.

---

GET_STREAMS_NAME Function

This function gets the Streams name of the invoker if the invoker is one of the following 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 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 DML handler to operate differently if it is processing events from the error queue (ERROR_EXECUTION type) instead of the apply process queue (APPLY type).

---

GET_TAG Function

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

Syntax

DBMS_STREAMS.GET_TAG()
 RETURN RAW;

Examples

The following example illustrates how to display the current 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; 

---

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 will have this tag. This procedure affects only the current session.

Syntax

DBMS_STREAMS.SET_TAG(
   tag  IN RAW  DEFAULT NULL);

Parameter

Table 95-5 SET_TAG Procedure Parameter

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.

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'));