147 DBMS_STREAMS_HANDLER_ADM

Using DBMS_STREAMS_HANDLER_ADM

This section contains topics that relate to using the DBMS_STREAMS_HANDLER_ADM package.

• Overview

• Security Model

• Views

• Operational Notes

Overview

A statement DML handler runs one or more data manipulation language (DML) statements on row logical change records (row LCRs) that are dequeued by an apply process. A single statement DML handler can include multiple statements, and you control the execution order of the statements.

Statement DML handlers are similar to procedure DML handlers for apply processes. Both statement DML handlers and procedure DML handlers provide custom processing of row changes that are encapsulated in row LCRs. Statement DML handlers and procedure DML handlers both run when an apply process dequeues a row LCR. However, statement DML handlers have the following advantages over procedure DML handlers:

• Statement DML handlers typically perform better than procedure DML handlers because statement DML handlers do not require PL/SQL processing.

• The syntax for statement DML handlers is same as DML syntax. Statement DML handlers do not require PL/SQL programming. Procedure DML handlers require PL/SQL programming.

• Statement DML handlers do not require the manipulation of ANYDATA values to access the information in row LCRs. Typically, procedure DML handlers must manipulate ANYDATA values.

• A statement DML handler can coexist with an error handler for same operation on the same database object. In contrast, you cannot specify both a procedure DML handler and an error handler for the same operation on the same database object.

Security Model

Security on this package can be controlled in either of the following ways:

• Granting EXECUTE on this package to selected users or roles.

• Granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

To ensure that the user who runs the subprograms in this package has the necessary privileges, configure an Oracle Streams administrator and connect as the Oracle Streams administrator when using this package.

Views

The DBMS_STREAMS_HANDLER_ADM package uses the views listed in the Oracle Database Reference.

• DBA_APPLY_DML_HANDLERS

• DBA_STREAMS_STMTS

• DBA_STREAMS_STMT_HANDLERS

Operational Notes

The following sections contain operational notes about the DBMS_STREAMS_HANDLER_ADM package:

• Statement Execution Order

• Supported SQL Statements

• Supported Row LCR Column Attributes

• Supported Row LCR Attributes

• Supported Row LCR Extra Attributes

• Supported Row LCR Method

Statement Execution Order

Each statement in a statement DML handler has a unique execution sequence number. When a statement DML handler is invoked, it executes its statements in order from the statement with the lowest execution sequence number to the statement with the highest execution sequence number.

Supported SQL Statements

You can use statement DML handlers for any valid DML operation on a row logical change record (row LCR). For example, a statement DML handler can audit the DML changes made to a table.

The following SQL statements are supported in statement DML handlers:

• INSERT

• UPDATE

• DELETE

• MERGE

In addition, define variables are not supported in the SQL statements in a statement DML handler.

However, the SQL statements in a statement DML handler can include calls to member subprograms for the row LCR type (LCR$_ROW_RECORD), such as ADD_COLUMN, DELETE_COLUMN, KEEP_COLUMNS, and RENAME_COLUMN.

Supported Row LCR Column Attributes

Statements in statement DML handlers can contain the row LCR column attributes described in Table 147-1.

Specify these attributes in the following way in a statement:

:attribute.column_name

For example, to specify the new_only attribute for the salary column, enter the following in a statement:

:new_only.salary

Supported Row LCR Attributes

Statements in statement DML handlers can contain the row LCR attributes described in Table 147-2.

Specify these attributes in the following way in a statement:

:attribute_name

For example, to specify the source_database_name attribute for a row LCR, enter the following in a statement:

:source_database_name

Supported Row LCR Extra Attributes

Statements in statement DML handlers can contain the row LCR extra attributes described in Table 147-3.

Specify these attributes in the following way in a statement:

:extra_attribute.attribute_name

For example, to specify the row_id extra attribute for a row LCR, enter the following in a statement:

:extra_attribute.row_id

Supported Row LCR Method

A statement in a statement DML handler can include a call to the EXECUTE member procedure for row LCRs. The EXECUTE member procedure executes the row LCR under the security domain of the current user.

A statement that runs the EXECUTE member procedure can be placed anywhere in the execution sequence order of the statement DML handler. It is not necessary to execute a row LCR unless the goal is to apply the changes in the row LCR to a table in addition to performing any other SQL statements in the statement DML handler.

When you call the EXECUTE member procedure in a statement, the conflict_resolution parameter controls whether any conflict resolution defined for the table using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package is used to resolve conflicts resulting from the execution of the LCR:

:lcr.execute TRUE|FALSE

A TRUE argument indicates that conflict resolution is used. A FALSE argument indicates that conflict resolution is not used.

For example, to use conflict resolution, enter the following in a statement:

:lcr.execute TRUE

An error is raised if this parameter is not specified or is set to NULL.

Summary of DBMS_STREAMS_HANDLER_ADM Subprograms

ADD_STMT_TO_HANDLER Procedure

This procedure adds a statement to a statement DML handler.

Syntax

DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
   handler_name       IN VARCHAR2,  
   statement          IN CLOB,   
   execution_sequence IN NUMBER  DEFAULT NULL);

Parameters

CREATE_STMT_HANDLER Procedure

This procedure creates a statement DML handler.

Syntax

DBMS_STREAMS_HANDLER_ADM.CREATE_STMT_HANDLER(
   handler_name IN VARCHAR2,  
   comment      IN VARCHAR2  DEFAULT NULL);

Parameters

DROP_STMT_HANDLER Procedure

This procedure drops a statement DML handler.

Syntax

DBMS_STREAMS_HANDLER_ADM.DROP_STMT_HANDLER(
   handler_name IN VARCHAR2);  

Parameters

REMOVE_STMT_FROM_HANDLER Procedure

This procedure removes a statement from a statement DML handler.

Syntax

DBMS_STREAMS_HANDLER_ADM.REMOVE_STMT_FROM_HANDLER(
   handler_name       IN VARCHAR2,  
   execution_sequence IN NUMBER  DEFAULT NULL);

Parameters