4 Oracle Streams Information Consumption

Implicit Consumption

With implicit consumption, an apply process automatically dequeues either captured LCRs, persistent LCRs, or persistent user messages. The queue must be an ANYDATA queue. If a message contains a logical change record (LCR), then the apply process can either apply it directly or call a user-specified procedure for processing. If the message does not contain an LCR, then the apply process can invoke a user-specified procedure called a message handler to process it.

Explicit Consumption

With explicit consumption, messages are dequeued in one of the following ways:

• A messaging client explicitly dequeues persistent LCRs or persistent user messages. The queue must be an ANYDATA queue. A messaging client dequeues messages when it is invoked by an application, and the application processes the messages after the messaging client dequeues them.

• An application explicitly dequeues messages manually and processes them. An application can dequeue the following types of messages: persistent LCRs, persistent user messages, buffered LCRs, and buffered user messages. The queue from which the messages are dequeued can be an ANYDATA queue or a typed queue.

Captured LCRs

A captured LCR is a logical change record (LCR) that was captured implicitly by a capture process and enqueued into the buffered queue portion of an ANYDATA queue.

Only an apply process can dequeue captured LCRs. After dequeue, an apply process can apply the captured LCR directly to make a database change, discard the captured LCR, send the captured LCR to an apply handler for processing, or re-enqueue the captured LCR into a persistent queue.

Persistent LCRs

A persistent LCR is a logical change record (LCR) that was enqueued into the persistent queue portion of an ANYDATA queue. A persistent LCR can be enqueued in one of the following ways:

• Captured implicitly by a synchronous capture and enqueued

• Constructed explicitly by an application and enqueued

• Dequeued by an apply process and enqueued by the same apply process using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package

Persistent LCRs can be dequeued by an apply process, a messaging client, or an application.

Buffered LCRs

A buffered LCR is a logical change record (LCR) that was constructed explicitly by an application and enqueued into the buffered queue portion of an ANYDATA queue. Only an application can dequeue buffered LCRs.

Persistent User Messages

A persistent user message is a non-LCR message of a user-defined type that was enqueued into a persistent queue. A persistent user message can be enqueued in one of the following ways:

• Created explicitly by an application and enqueued

• Dequeued by an apply process and enqueued by the same apply process using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package

Apply processes and messaging clients can only dequeue persistent user messages that are in an ANYDATA queue. Applications can dequeue persistent user messages that are in an ANYDATA queue or a typed queue.

Buffered User Messages

A buffered user message is a non-LCR message of a user-defined type that was created explicitly by an application and enqueued into a buffered queue. A buffered user message can be enqueued into the buffered queue portion of an ANYDATA queue or a typed queue. Only an application can dequeue buffered user messages.

DML Handlers

DML handlers process row logical change records (row LCRs) dequeued by an apply process. There are two types of DML handlers: statement DML handlers and procedure DML handlers. A statement DML handler uses a collection of SQL statements to process row LCRs, while a procedure DML handler uses a PL/SQL procedure to process row LCRs.

The following sections describe DML handlers and error handlers:

• Statement DML Handlers

• Procedure DML Handlers

Error Handlers

An error handler has the following characteristics:

• Mechanism: A user-defined PL/SQL procedure

• Type of message: Row LCR

• Message creator: Capture process, synchronous capture, or application

• Scope: One operation on one table

• Number allowed for each apply process: Many, but only one can be specified for the same operation on the same table

An error handler is similar to a procedure DML handler. The difference between the two is that an error handler is invoked only if an apply error results when an apply process tries to apply a row LCR for the specified operation on the specified table.

You create an error handler in the same way that you create a procedure DML handler, except that you set the error_handler parameter to TRUE when you run the SET_DML_HANDLER procedure.

An error handler cannot coexist with a procedure DML handler for the same operation on the same table. However, an error handler can coexist with a statement DML handler for the same operation on the same table.

DDL Handlers

A DDL handler has the following characteristics:

• Mechanism: A user-defined PL/SQL procedure

• Type of message: DDL LCR

• Message creator: Capture process or application

• Scope: All DDL LCRs dequeued by the apply process

• Number allowed for each apply process: One

The user-defined PL/SQL procedure can perform any customized processing of DDL LCRs. For example, to log DDL changes before applying them, you can create a procedure that processes DDL operations to accomplish this.

To execute a DDL LCR inside a DDL handler, invoke the EXECUTE member procedure for the DDL LCR. To associate a DDL handler with a particular apply process, use the ddl_handler parameter in the CREATE_APPLY or the ALTER_APPLY procedure in the DBMS_APPLY_ADM package.

Typically, DDL handlers are used in Oracle Streams replication environments to perform custom processing of DDL LCRs, but these handlers can be used in nonreplication environments as well. For example, you can use such handlers to record changes made to database objects without replicating these changes.

Message Handlers

A message handler has the following characteristics:

• Mechanism: A user-defined PL/SQL procedure

• Type of message: Persistent user message (non-LCR)

• Message creator: Application

• Scope: All user messages dequeued by the apply process

• Number allowed for each apply process: One

A message handler offers advantages in any environment that has applications that must update one or more remote databases or perform some other remote action. These applications can enqueue persistent user messages into a queue at the local database, and Oracle Streams can propagate each persistent user message to the appropriate queues at destination databases. If there are multiple destinations, then Oracle Streams provides the infrastructure for automatic propagation and processing of these messages at these destinations. If there is only one destination, then Oracle Streams still provides a layer between the application at the source database and the application at the destination database, so that, if the application at the remote database becomes unavailable, then the application at the source database can continue to function normally.

For example, a message handler can convert a persistent user message into an electronic mail message. In this case, the persistent user message can contain the attributes you would expect in an electronic mail message, such as from, to, subject, text_of_message, and so on. After converting a message into an electronic mail messages, the message handler can send it out through an electronic mail gateway.

You can specify a message handler for an apply process using the message_handler parameter in the CREATE_APPLY or the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. An Oracle Streams apply process always assumes that a non-LCR message has no dependencies on any other messages in the queue. If parallelism is greater than 1 for an apply process that applies persistent user messages, then these messages can be dequeued by a message handler in any order. Therefore, if dependencies exist between these messages in your environment, then Oracle recommends that you set apply process parallelism to 1.

Precommit Handlers

A precommit handler has the following characteristics:

• Mechanism: A user-defined PL/SQL procedure

• Type of message: Commit directive for transactions that include row LCRs or persistent user messages

• Message creator: Capture process, synchronous capture, or application

• Scope: All row LCRs with commit directives dequeued by the apply process

• Number allowed for each apply process: One

You can use a precommit handler to audit commit directives for captured LCRs and transaction boundaries for persistent LCRs and persistent user messages. A commit directive is a transaction control directive that contains a COMMIT. A precommit handler is a user-defined PL/SQL procedure that can receive the commit information for a transaction and process the commit information in any customized way. A precommit handler can work with a statement DML handler, procedure DML handler, or message handler.

For example, a precommit handler can improve performance by caching data for the length of a transaction. This data can include cursors, temporary LOBs, data from a message, and so on. The precommit handler can release or execute the objects cached by the handler when a transaction completes.

A precommit handler executes when the apply process commits a transaction. You can use the commit_serialization apply process parameter to control the commit order for an apply process.

The following list describes commit directives and transaction boundaries:

• Commit Directives for Captured LCRs: When you are using a capture process, and a user commits a transaction, the capture process captures an internal commit directive for the transaction if the transaction contains row LCRs that were captured by the capture process. The capture process also records the transaction identifier in each captured LCR in a transaction.

  Once enqueued, these commit directives can be propagated to destination queues, along with the LCRs in a transaction. A precommit handler receives each commit SCN for these internal commit directives in the queue of an apply process before they are processed by the apply process.

• Transaction Boundaries for Persistent LCRs Enqueued by Synchronous Captures: When you are using a synchronous capture, and a user commits a transaction, the persistent LCRs that were enqueued by the synchronous capture are organized into a message group. The synchronous capture records the transaction identifier in each persistent LCR in a transaction.

  After persistent LCRs are enqueued by a synchronous capture, the persistent LCRs in the message group can be propagated to other queues. When an apply process is configured to process these persistent LCRs, it generates a commit SCN for all of the persistent LCRs in a message group. The commit SCN values generated by an individual apply process have no relation to the source transaction, or to the values generated by any other apply process. A precommit handler configured for such an apply process receives the commit SCN supplied by the apply process.

• Transaction Boundaries for Messages Enqueued by Applications: An application can enqueue persistent LCRs and persistent user messages, as well as other types of messages. When the user performing these enqueue operations issues a COMMIT statement to end the transaction, the enqueued persistent LCRs and persistent user messages are organized into a message group.

  When messages that were enqueued by an application are organized into a message group, the messages in the message group can be propagated to other queues. When an apply process is configured to process these messages, it generates a single transaction identifier and commit SCN for all the messages in a message group. Transaction identifiers and commit SCN values generated by an individual apply process have no relation to the source transaction, or to the values generated by any other apply process. A precommit handler configured for such an apply process receives the commit SCN supplied by the apply process.

Considerations for Apply Handlers

The following are considerations for using apply handlers:

• Both statement DML handlers and procedure DML handlers process row LCRs. Procedure DML handlers require PL/SQL processing while statement DML handlers do not. Therefore, statement DML handlers typically perform better than procedure DML handlers. Statement DML handlers also are usually easier to configure that procedure DML handlers. However, procedure DML handlers can perform operations that are not possible with a statement DML handler, such as controlling program flow and trapping errors. In addition, procedure DML handlers can modify column values in row LCRs while statement DML handlers cannot.

• Statement DML handlers, procedure DML handlers, error handlers, DDL handlers, and message handlers can execute an LCR by calling the LCR's EXECUTE member procedure.

• All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

• An apply handler that uses a PL/SQL procedure can set an Oracle Streams session tag. Statement DML handlers cannot set an Oracle Streams session tag.

• An apply handler that uses a user-defined PL/SQL procedure can call a Java stored procedure that is published (or wrapped) in a PL/SQL procedure. Statement DML handlers cannot call a Java stored procedure.

• If an apply process tries to invoke an apply handler that does not exist or is invalid, then the apply process aborts.

• If an apply handler that uses a PL/SQL procedure invokes a procedure or function in an Oracle-supplied package, then the user who runs the apply handler must have direct EXECUTE privilege on the package. It is not sufficient to grant this privilege through a role. The DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure grants EXECUTE privilege on all Oracle Streams packages, and other privileges relevant to Oracle Streams. A statement DML handler cannot invoke a procedure or function.

Summary of Message Processing Options

The table in this section summarizes the message processing options available when you are using one or more of the apply handlers described in the previous sections. Apply handlers are optional for row LCRs and DDL LCRs because an apply process can apply these messages directly. However, a message handler is required for processing persistent user messages. In addition, an apply process dequeues a message only if the message satisfies the rule sets for the apply process. In general, a message satisfies the rule sets for an apply process if no rules in the negative rule set evaluate to TRUE for the message, and at least one rule in the positive rule set evaluates to TRUE for the message.

Table 4-3 summarizes the message processing options for an apply process.

In addition to the message processing options described in this section, you can use the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package to instruct an apply process to enqueue messages into the persistent queue portion of a specified destination queue. Also, you can control message execution using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package.

Automatic Trimming of Character Data Types During Apply

The rtrim_on_implicit_conversion apply process parameter determines whether the apply process trims data when it converts a CHAR or NCHAR to a VARCHAR2, NVARCHAR2, or CLOB. When this parameter is set to Y, the apply process automatically removes blank padding from the right end of a column during data type conversion. When this parameter is set to N, the apply process preserves blank padding during data type conversion.

Consider the following example:

• A row LCR contains 'abc' for a CHAR(10) column.

• The corresponding table column for the row LCR is NVARCHAR2(10).

If the rtrim_on_implicit_conversion apply process parameter is set to Y, the apply process inserts 'abc' into the table column and trims the padding after these characters. If the rtrim_on_implicit_conversion apply process parameter is set to N, then the apply process inserts 'abc' into the table column, and the remaining space in the column is filled with blanks.

Automatic Conversion and LOB Data Types

Procedure DML handlers and error handlers can use LOB assembly for data that has been converted from LONG to CLOB or from LONG RAW to BLOB.

Interfaces for Performing SQL Generation

You can use the GET_ROW_TEXT and GET_WHERE_CLAUSE member procedures for row LCRs to perform SQL generation. The PL/SQL interface generates SQL in a CLOB data type.

SQL Generation Formats

SQL statement can be generated in one of two formats: inline values or bind variables. Use inline values when the returned SQL statement is relatively small. For larger SQL statements, use bind variables. In this case, the bind variables are passed in a separate list that includes pointers to both old and new column values.

For information about using bind variables with each interface, see the documentation about the GET_ROW_TEXT and GET_WHERE_CLAUSE row LCR member procedures in Oracle Database PL/SQL Packages and Types Reference.

SQL Generation and Data Types

SQL generation supports the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• FLOAT

• DATE

• BINARY_FLOAT

• BINARY_DOUBLE

• LONG

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• LONG RAW

• CHAR

• NCHAR

• CLOB with BASICFILE storage

• NCLOB with BASICFILE storage

• BLOB with BASICFILE storage

• XMLType stored as CLOB

UPDATE hr.departments SET department_name='Management' 
  WHERE department_name='Administration';

WHERE "DEPARTMENT_NAME"='Management'

SELECT "CLOB_COL" FROM "HR"."LOB_TAB" WHERE "N1"=2 FOR UPDATE

SQL Generation and Character Sets

When you use the LCR methods, the generated SQL is in the database character set. SQL keywords, such as INSERT, UPDATE, and INTO, do not change with the character set.

Sample Generated SQL Statements

This section provides examples of generated SQL statements:

• Sample Generated SQL Statements for the hr.employees Table

• Sample Generated SQL Statements for a Table With LOB Columns

INSERT INTO hr.employees (employee_id, 
                           last_name, 
                           email, 
                           hire_date, 
                           job_id, 
                           salary, 
                           commission_pct) 
                   VALUES (207, 
                           'Gregory', 
                           'pgregory@example.com', 
                           SYSDATE, 
                           'PU_CLERK', 
                           9000, 
                           NULL);

INSERT INTO "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME",
"EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT",
"MANAGER_ID","DEPARTMENT_ID" ) VALUES ( 207, NULL,'Gregory',
'pgregory@example.com', NULL , TO_DATE(' 2009-04-15','syyyy-mm-dd'),
'PU_CLERK',9000, NULL , NULL , NULL )

INSERT INTO "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME",
"EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY",
"COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" ) VALUES ( :1   ,:2   ,:3   
,:4   ,:5   ,:6   ,:7   ,:8   ,:9   ,:10  ,:11  )

UPDATE hr.employees SET salary=10000 WHERE employee_id=207;

UPDATE "HR"."EMPLOYEES" SET "SALARY"=10000 WHERE "EMPLOYEE_ID"=207 
AND "SALARY"=9000

UPDATE "HR"."EMPLOYEES" SET "SALARY"=:1    WHERE "EMPLOYEE_ID"=:2    
AND "SALARY"=:3

DELETE FROM hr.employees WHERE employee_id=207;

DELETE  FROM "HR"."EMPLOYEES" WHERE "EMPLOYEE_ID"=207 AND "FIRST_NAME" IS NULL 
AND "LAST_NAME"='Gregory' AND "EMAIL"='pgregory@example.com' AND 
"PHONE_NUMBER" IS NULL  AND "HIRE_DATE"= TO_DATE(' 2009-04-15','syyyy-mm-dd') 
AND "JOB_ID"='PU_CLERK' AND "SALARY"=10000 AND "COMMISSION_PCT" IS NULL  
AND "MANAGER_ID" IS NULL  AND "DEPARTMENT_ID" IS NULL 

DELETE  FROM "HR"."EMPLOYEES" WHERE "EMPLOYEE_ID"=:1    AND "FIRST_NAME"=:2    
AND "LAST_NAME"=:3    AND "EMAIL"=:4    AND "PHONE_NUMBER"=:5    AND 
"HIRE_DATE"=:6    AND "JOB_ID"=:7    AND "SALARY"=:8    AND 
"COMMISSION_PCT"=:9   AND "MANAGER_ID"=:10   AND "DEPARTMENT_ID"=:11 

CREATE TABLE hr.lob_tab(
   n1        number primary key,
   clob_col  CLOB,
   nclob_col NCLOB,
   blob_col  BLOB);

INSERT INTO hr.lob_tab VALUES (2, 'test insert', NULL, NULL);

INSERT INTO "HR"."LOB_TAB"("N1","BLOB_COL","CLOB_COL","NCLOB_COL" ) 
VALUES ( 2,, EMPTY_CLOB() ,)

INSERT INTO "HR"."LOB_TAB"("N1","BLOB_COL","CLOB_COL","NCLOB_COL" ) 
VALUES ( :1   ,:2   ,:3   ,:4   )

UPDATE hr.lob_tab SET clob_col='test update' WHERE n1=2;

UPDATE "HR"."LOB_TAB" SET "CLOB_COL"= EMPTY_CLOB()  WHERE "N1"=2

UPDATE "HR"."LOB_TAB" SET "CLOB_COL"=:1    WHERE "N1"=:2

DELETE FROM hr.lob_tab WHERE n1=2;

DELETE  FROM "HR"."LOB_TAB" WHERE "N1"=2

DELETE  FROM "HR"."LOB_TAB" WHERE "N1"=:1

Reader Server States

The state of a reader server describes what the reader server is doing currently. You can view the state of the reader server for an apply process by querying the V$STREAMS_APPLY_READER dynamic performance view. The following reader server states are possible:

• INITIALIZING - Starting up

• IDLE - Performing no work

• DEQUEUE MESSAGES - Dequeuing messages from the apply process's queue

• SCHEDULE MESSAGES - Computing dependencies between messages and assembling messages into transactions

• SPILLING - Spilling unapplied messages from memory to hard disk

• PAUSED - WAITING FOR DDL TO COMPLETE - Paused while waiting for a DDL LCR to be applied

Coordinator Process States

The state of a coordinator process describes what the coordinator process is doing currently. You can view the state of a coordinator process by querying the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The following coordinator process states are possible:

• INITIALIZING - Starting up

• IDLE - Performing no work

• APPLYING - Passing transactions to apply servers

• SHUTTING DOWN CLEANLY - Stopping without an error

• ABORTING - Stopping because of an apply error

Apply Server States

The state of an apply server describes what the apply server is doing currently. You can view the state of each apply server for an apply process by querying the V$STREAMS_APPLY_SERVER dynamic performance view. The following apply server states are possible:

• INITIALIZING - Starting up.

• IDLE - Performing no work.

• RECORD LOW-WATERMARK - Performing an administrative action that maintains information about the apply progress, which is used in the ALL_APPLY_PROGRESS and DBA_APPLY_PROGRESS data dictionary views.

• ADD PARTITION - Performing an administrative action that adds a partition that is used for recording information about in-progress transactions.

• DROP PARTITION - Performing an administrative action that drops a partition that was used to record information about in-progress transactions.

• EXECUTE TRANSACTION - Applying a transaction.

• WAIT COMMIT - Waiting to commit a transaction until all other transactions with a lower commit SCN are applied. This state is possible only if the commit_serialization apply process parameter is set to a value other than DEPENDENT_TRANSACTIONS and the parallelism apply process parameter is set to a value greater than 1.

• WAIT DEPENDENCY - Waiting to apply an LCR in a transaction until another transaction, on which it has a dependency, is applied. This state is possible only if the PARALLELISM apply process parameter is set to a value greater than 1.

• WAIT FOR CLIENT - Waiting for an XStream In client application to request more logical change records (LCRs).

• WAIT FOR NEXT CHUNK - Waiting for the next set of LCRs for a large transaction.

• ROLLBACK TRANSACTION - Rolling back a transaction.

• TRANSACTION CLEANUP - Cleaning up an applied transaction, which includes removing LCRs from the apply process's queue.