Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

22 DBMS_CDC_SUBSCRIBE

The DBMS_CDC_SUBSCRIBE package, one of a set of Change Data Capture packages, lets subscribers view and query change data that was captured and published with the DBMS_CDC_PUBLISH package.

A Change Data Capture system usually has one publisher and many subscribers. The subscribers (applications or individuals), use the Oracle supplied package, DBMS_CDC_SUBSCRIBE, to access published data.

Note:

In previous releases, this package was named DBMS_LOGMNR_CDC_SUBSCRIBE. Beginning with Oracle Database 10g, the LOGMNR string has been removed from the name, resulting in the name DBMS_CDC_SUBSCRIBE. Although both variants of the name are still supported, the variant with the LOGMNR string has been deprecated and may not be supported in a future release.

See Also:

Oracle Database Data Warehousing Guide for information regarding Oracle Change Data Capture.

This chapter contains the following topics:


Using DBMS_CDC_SUBSCRIBE

This section contains the following topics, which relate to using the DBMS_CDC_SUBSCRIBE package:


Overview

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

Once the publisher sets up the system to capture data into change tables (which are viewed as publications by subscribers) and grants subscribers access to the change tables, subscribers can access and query the published change data for any of the source tables of interest. Using the subprograms in the DBMS_CDC_SUBSCRIBE package, the subscriber accomplishes the following main objectives:

  1. Indicates the change data of interest by creating a subscription and associated subscriber views on published source tables and source columns

  2. Activates the subscription to indicate that the subscriber is ready to receive change data

  3. Extends the subscription window to receive a new set of change data

  4. Uses SQL SELECT statements to retrieve change data from the subscriber views

  5. Purges the subscription window when finished processing a block of changes

  6. Drops the subscription when finished with the subscription

Figure 22-1 provides a graphical flowchart of the order in which subscribers most typically use the subprograms in the DBMS_CDC_SUBSCRIBE package (which are listed in Table 22-1). A subscriber would typically create a subscription, subscribe to one or more source tables and columns, activate the subscription, extend the subscription window, query the subscriber views, purge the subscription window, and then either extend the subscription window again or drop the subscription.

Note:

If a subscriber uses the PURGE_WINDOW procedure immediately after using an EXTEND_WINDOW procedure, then change data may be lost without ever being processed.

See Also:

Chapter 21, "DBMS_CDC_PUBLISH" for information on the package for publishing change data.

Figure 22-1 Subscription Flow

Description of arpls011.gif follows
Description of the illustration arpls011.gif


Deprecated Subprograms

Note:

Oracle Corporation recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

The following subprograms are deprecated with Oracle Database 10g:

Changes in Behavior

If an existing application uses these deprecated DBMS_CDC_SUBSCRIBE subprograms with Oracle Database 10g, note the following changes in behavior:

Deprecated Parameters

The use of the subscription_handle parameter with the following DBMS_CDC_SUBSCRIBE procedures has been deprecated beginning with Oracle Database 10g:


Security Model

Change Data Capture grants EXECUTE privileges to PUBLIC on the DBMS_CDC_SUBSCRIBE package.


Views

The DBMS_CDC_SUBSCRIBE package uses the views listed in the section on Getting Information About the Change Data Capture Environment in Oracle Database Data Warehousing Guide.


Summary of DBMS_CDC_SUBSCRIBE Subprograms

Table 22-1 DBMS_CDC_SUBSCRIBE Package Subprograms

Subprogram Description
ACTIVATE_SUBSCRIPTION Procedure
Indicates that a subscription is ready to start accessing change data
CREATE_SUBSCRIPTION Procedure Creates a subscription and associates it with one change set
DROP_SUBSCRIPTION Procedure
Drops a subscription that was created with a prior call to the CREATE_SUBSCRIPTION procedure
EXTEND_WINDOW Procedure Sets a subscription window high boundary so that new change data can be seen
PURGE_WINDOW Procedure
Sets the low boundary for a subscription window to notify Change Data Capture that the subscriber is finished processing a set of change data
SUBSCRIBE Procedure
Specifies a source table and the source columns for which the subscriber wants to access change data and specifies the subscriber view through which the subscriber sees change data for the source table


ACTIVATE_SUBSCRIPTION Procedure

This procedure indicates that a subscription is ready to start accessing change data.

Syntax

DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION (
     subscription_name  IN VARCHAR2);

Parameters

Table 22-2 ACTIVATE_SUBSCRIPTION Procedure Parameters

Parameter Description
subscription_name The name of the subscription that was specified for a previous call to the CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 22-3 ACTIVATE_SUBSCRIPTION Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist
ORA-31426 Cannot modify active subscriptions
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31514 Change set disabled due to capture error

Usage Notes


CREATE_SUBSCRIPTION Procedure

This procedure creates a subscription that is associated with one change set. This procedure replaces the deprecated GET_SUBSCRIPTION_HANDLE procedure.

Syntax

DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION (
     change_set_name      IN  VARCHAR2,
     description          IN  VARCHAR2,
     subscription_name    IN  VARCHAR2);

Parameters

Table 22-4 CREATE_SUBSCRIPTION Procedure Parameters

Parameter Description
change_set_name The name of an existing change set to which the subscriber subscribes
description A description of the subscription (which might include, for example, the purpose for which it is used). The description must be specified using 255 or fewer characters.
subscription_name A unique name for a subscription that must consist of 30 characters or fewer and cannot have a prefix of CDC$. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 22-5 CREATE_SUBSCRIPTION Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31415 Specified change set does not exist
ORA-31449 Invalid value for change_set_name
ORA-31457 Maximum length of description field exceeded
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31506 Duplicate subscription name specified
ORA-31510 Name uses reserved prefix CDC$
ORA-31511 Name exceeds maximum length of 30 characters

Usage Notes


DROP_SUBSCRIPTION Procedure

This procedure drops a subscription.

Syntax

DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION (
     subscription_name  IN VARCHAR2);

Parameters

Table 22-6 DROP_SUBSCRIPTION Procedure Parameters

Parameter Description
subscription_name The name of the subscription that was specified for a previous call to the CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 22-7 DROP_SUBSCRIPTION Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist

Usage Notes

Subscribers should be diligent about dropping subscriptions that are no longer needed so that change data will not be held in the change tables unnecessarily.


EXTEND_WINDOW Procedure

This procedure sets the subscription window high boundary so that new change data can be seen.

Syntax

DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW (
     subscription_name  IN VARCHAR2);

Parameters

Table 22-8 EXTEND_WINDOW Procedure Parameters

Parameter Description
subscription_name The unique name of the subscription that was specified by a previous call to the CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 22-9 EXTEND_WINDOW Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist
ORA-31429 Subscription has not been activated
ORA-31432 Invalid source table
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31509 Publication does not exist
ORA-31514 Change set disabled due to capture error

Usage Notes


PURGE_WINDOW Procedure

This procedure sets the low boundary of the subscription window so that the subscription no longer sees any change data, effectively making the subscription window empty. The subscriber calls this procedure to notify Change Data Capture that the subscriber is finished processing a block of change data.

Syntax

DBMS_CDC_SUBSCRIBE.PURGE_WINDOW (
     subscription_name   IN VARCHAR2);

Parameters

Table 22-10 PURGE_WINDOW Procedure Parameters

Parameter Description
subscription_name The name of the subscription that was specified for a previous call to the CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 22-11 PURGE_WINDOW Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist
ORA-31429 Subscription has not been activated
ORA-31432 Invalid source table
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31514 Change set disabled due to capture error

Usage Notes


SUBSCRIBE Procedure

This procedure specifies a source table and the source columns for which the subscriber wants to access change data. In addition, it specifies the subscriber view through which the subscriber sees change data for the source table.

Syntax

There are two versions of syntax for the SUBSCRIBE procedure, as follow:

Parameters

Table 22-12 SUBSCRIBE Procedure Parameters

Parameter Description
subscription_name The name of a subscription that was specified for, or returned by, a previous call to the CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.
source_schema The name of the schema where the source table resides
source_table The name of a published source table
column_list A comma-delimited list of columns from the published source table or publication
subscriber_view Unique name for the subscriber view for this source table or publication that must consist of 30 or fewer characters and must not have a prefix of CDC$. Subscriber view names follow the Oracle schema object naming rules.
publication_id A valid publication_id, which the subscriber can obtain from the ALL_PUBLISHED_COLUMNS view.

Exceptions

Table 22-13 SUBSCRIBE Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist
ORA-31426 Cannot modify active subscriptions
ORA-31427 Specified source table already subscribed
ORA-31428 No publication contains all the specified columns
ORA-31432 Invalid source table
ORA-31466 No publications found
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31510 Name uses reserved prefix CDC$
ORA-31511 Name exceeds maximum length of 30 characters

Usage Notes