26
DBMS_LOGMNR_CDC_SUBSCRIBE

This chapter describes how to use the DBMS_LOGMNR_CDC_SUBSCRIBE package to view and query the change data that was captured and published with the DBMS_LOGMNR_CDC_PUBLISH package.

A Change Data Capture system usually has one publisher that captures and publishes changes for any number of Oracle source (relational) tables and many subscribers. The subscribers, typically applications, use the Oracle supplied package, DBMS_LOGMNR_CDC_SUBSCRIBE, to access the published data.

This chapter discusses the following topics:

• Subscribing to Change Data

• Summary of DBMS_LOGMNR_CDC_SUBSCRIBE Subprograms

  See Also: Oracle9i Data Warehousing Guide for more information about the Oracle Change Data Capture publish and subscribe model.

Subscribing to Change Data

Once the publisher sets up the system to capture data into change tables and grants access, subscribers can access and query the published change data for any of the source tables of interest. Using the procedures in the DBMS_LOGMNR_CDC_SUBSCRIBE package, the subscriber accomplishes the following main objectives:

1. Indicate the change data of interest by creating subscriptions to published source tables and source columns.

2. Extend the subscription window and create a new subscriber view when the subscriber is ready to receive a set of change data.

3. Use SELECT statements to retrieve change data from the subscriber views.

4. Drop the subscriber view and purge the subscription window when finished processing a block of changes.

5. Drop the subscription when the subscriber no longer needs its change data.

Summary of DBMS_LOGMNR_CDC_SUBSCRIBE Subprograms

The primary role of the subscriber is to use the change data. Through the DBMS_LOGMNR_CDC_SUBSCRIBE package, each subscriber registers interest in a set of source tables by subscribing to them.

Table 26-1 describes the procedures for the DBMS_LOGMNR_CDC_SUBSCRIBE package.

Table 26-1 DBMS_LOGMNR_CDC_SUBSCRIBE Package Subprograms  

Subprogram	Description
"GET_SUBSCRIPTION_HANDLE Procedure"	Creates a subscription handle that associates the subscription with one change set.
"SUBSCRIBE Procedure"	Specifies the source tables and source columns for which the subscriber wants to access change data.
"ACTIVATE_SUBSCRIPTION Procedure"	Indicates that a subscription is ready to start accessing change data.
"EXTEND_WINDOW Procedure"	Sets the subscription window boundaries (low-water and high-water mark) so that new change data can be seen.
"PREPARE_SUBSCRIBER_VIEW Procedure"	Creates a subscriber view in the subscriber's schema in which the subscriber can query the change data encompassed by the current subscription window.
"DROP_SUBSCRIBER_VIEW Procedure"	Drops a subscriber view from the subscriber's schema.
"PURGE_WINDOW Procedure"	Sets the low-water mark for a subscription window to notify the capture system that the subscriber is finished processing a set of change data.
"DROP_SUBSCRIPTION Procedure"	Drops a subscription that was created with a prior call to the GET_SUBSCRIPTION_HANDLE procedure.

Subscribers call the procedures in the order shown in Table 26-1 unless an error occurs, at which time the subscribers should exit. Figure 26-1 shows the most common steps for using the procedures in the DBMS_LOGMNR_CDC_SUBSCRIBE package.

Figure 26-1 Subscription Flow

Text description of the illustration sub_flow.gif

In Figure 26-1:

1. If you use the PURGE_WINDOW procedure immediately after using an EXTEND_WINDOW procedure, then change data is lost without ever being processed.

2. If you use the EXTEND_WINDOW procedure immediately after using the DROP_SUBSCRIBER_VIEW procedure, you will see the data that you just processed again and possibly some new data.

3. If an error occurs during any step in the process, the application program calling the DBMS_LOGMNR_CDC_SUBSCRIBE procedures should detect the error and exit. For example, if the PREPARE_SUBSCRIBER_VIEW procedure fails for any reason, and the application ignores the error and continues, then the PURGE_WINDOW procedure will delete data that was never seen or selected by the subscriber.