DBMS_LOGMNR_CDC_SUBSCRIBE, 2 of 2

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 27-1 describes the procedures for the DBMS_LOGMNR_CDC_SUBSCRIBE package.

Table 27-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 27-1 unless an error occurs, at which time the subscribers should exit. Figure 27-1 shows the most common steps for using the procedures in the DBMS_LOGMNR_CDC_SUBSCRIBE package.

Figure 27-1 Subscription Flow

Text description of arpls005.gif follows

Text description of the illustration arpls005.gif

In Figure 27-1:

If you use the PURGE_WINDOW procedure immediately after using an EXTEND_WINDOW procedure, then change data is lost without ever being processed.
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.
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.

GET_SUBSCRIPTION_HANDLE Procedure

This procedure creates a subscription handle that associates the subscription with one change set. Creating a subscription handle is the first step in obtaining a subscription.

Syntax

DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE(
   change_set           IN  VARCHAR2,
   description          IN  VARCHAR2 := NULL,
   subscription_handle  OUT NUMBER);

Parameters

Table 27-2 GET_SUBSCRIPTION_HANDLE Procedure Parameters

Parameter	Description
change_set	Name of an existing change set to which the application subscribes. You must set the value to `SYNC_SET`.
description	Describes the subscription handle and the purpose for which it is used.
subscription_handle	Unique number of the subscription handle for this subscription.

Exception

Table 27-3 GET_SUBSCRIPTION_HANDLE Procedure Exceptions

Exception	Description
`ORA-31415`	Could not find an existing change set with this name.
`ORA-31457`	The maximum number of characters permitted in the description field was exceeded.
`ORA-31458`	This is an internal error. Contact Oracle Support Services and report the error.

Usage Notes

The GET_SUBSCRIPTION_HANDLE procedure allows a subscriber to register interest in a change set associated with source tables of interest.
To see all of the published source tables for which the subscriber has privileges, query the ALL_PUBLICATIONS view.
A subscriber can later use a single subscription handle to access the multiple change tables in the subscription.
Subscription handles:
- Never get reused and are tracked from the time of creation until they are dropped with the DROP_SUBSCRIPTION procedure.
- Are not shared among subscribers; rather, each subscription handle is validated against the subscriber's login ID.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE(\
   CHANGE_SET=>'SYNC_SET', \
   DESCRIPTION=>'Change data for emp',\
   SUBSCRIPTION_HANDLE=>:subhandle);

SUBSCRIBE Procedure

This procedure specifies the source tables and source columns for which the subscriber wants to access change data.

Syntax

There are two versions of syntax for the SUBSCRIBE procedure, each of which specifies the subscriber columns and datatypes. If the subscribers know which publication contains the source columns of interest, the subscribers can use the version of the procedure that contains the publication ID. If they do not know the publication ID, the Change Data Capture system will select a publication based on the supplied source schema and source table.

The following syntax identifies the source table of interest, allowing Change Data Capture to select any publication that contains all source columns of interest.

DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE (
 subscription_handle  IN NUMBER,
 source_schema        IN VARCHAR2,
 source_table         IN VARCHAR2,
 column_list          IN VARCHAR2);

The following syntax specifies the publication ID for a specific publication that contains the source columns of interest.

DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE (
 subscription_handle  IN NUMBER,
 publication_id       IN NUMBER,
 column_list          IN VARCHAR2);

Parameters

Table 27-4 SUBSCRIBE Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.
`source_schema`	Schema name where the source table resides.
`source_table`	Name of a published source table.
`column_list`	A comma-delimited list of columns from the published source table.
`publication_id`	A valid publication_id, which you can obtain from the `ALL_PUBLISHED_COLUMNS` view.

Exceptions

Table 27-5 SUBSCRIBE Procedure Exceptions

Exception	Description
`ORA-31425`	The specified subscription handle does not exist, or it does not belong to this user or application.
`ORA-31426`	The subscription handle has been activated; additional calls to the `SUBSCRIBE` procedure are prohibited. You must subscribe to all of the desired tables and columns before activating the subscription. Ensure that the correct subscription handle was specified.
`ORA-31427`	The subscription represented by the subscription handle already contains the schema name and source table. Check the values of the `subscription_handle`, `source_schema`, and `source_table` parameters. Do not attempt to subscribe to the same table more than once using the same subscription handle.
`ORA-31428`	No publication contains all of the specified columns. One or more of the specified columns cannot be found in a single publication. Consult the `ALL_PUBLISHED_COLUMNS` view to see the current publications and change the subscription request to select only the columns that are in the same publication.

Usage Notes

You can subscribe to any valid publication_id. You can find valid publications in the ALL_PUBLISHED_COLUMNS view.
The SUBSCRIBE procedure allows an application to subscribe to one or more published source tables and to specific columns in each source table.
To see all of the published source table columns for which the subscriber has privileges, query the ALL_PUBLISHED_COLUMNS view.
Subscriptions must be created before the application actually needs the data. The Change Data Capture system does not guarantee that there will be any change data available at the moment the subscription is created.
Subscribers can subscribe only to published columns from the source table. Also, all of the columns must come from the same publication. Any control columns associated with the underlying change table are added to the subscription automatically.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.SUBSCRIBE(\
   SUBSCRIPTION_HANDLE=>:subhandle, \
   SOURCE_SCHEMA=>'scott', \
   SOURCE_TABLE=>'emp', \
   COLUMN_LIST=>'empno, ename, hiredate');

ACTIVATE_SUBSCRIPTION Procedure

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

Syntax

DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION (
 subscription_handle  IN NUMBER);

Parameters

Table 27-6 ACTIVATE_SUBSCRIPTION Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.

Exceptions

Table 27-7 ACTIVATE_SUBSCRIPTION Procedure Exceptions

Exception	Description
`ORA-31425`	The specified subscription handle does not exist, or it does not belong to this user ID or application.
`ORA-31439`	The subscription is already active. You can activate a subscription only once.

Usage Notes

The ACTIVATE_SUBSCRIPTION procedure indicates that you are finished subscribing to tables, and the subscription is ready to start accessing data.
Once the subscriber activates the subscription:
- No additional source tables can be added to the subscription.
- The Change Data Capture system holds the available data for the source tables and sets the subscription window to empty.
- The subscriber must use the EXTEND_WINDOW procedure to see the initial set of change data.
- The subscription cannot be activated again.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION( \
   SUBSCRIPTION_HANDLE=>:subhandle);

EXTEND_WINDOW Procedure

This procedure sets the subscription window boundaries (low-water and high-water mark) so that new change data can be seen.

Syntax

DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW (
 subscription_handle  IN NUMBER);

Parameters

Table 27-8 EXTEND_WINDOW Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.

Exceptions

Table 27-9 EXTEND_WINDOW Procedure Exceptions

Exception	Description
`ORA-31425`	The specified subscription handle does not exist or it does not belong to this user or application.
`ORA-31429`	The subscription handle must be activated before you use the `EXTEND_WINDOW` procedure. Call the `ACTIVATE_SUBSCRIPTION` procedure for this subscription handle and then try the original command again.
`ORA-31430`	The subscriber view was not dropped prior to making this call. Call the `DROP_SUBSCRIBER_VIEW` procedure and then try the original command again.

Usage Notes

Until you call the EXTEND_WINDOW procedure to begin capturing change data, the subscription window remains empty.
- The first time that you call the EXTEND_WINDOW procedure, it establishes the initial boundaries for the subscription window.
- Subsequent calls to the EXTEND_WINDOW procedure extend the high-water mark of the subscription window so that new change data can be seen.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW( \
subscription_handle=>:subhandle);

PREPARE_SUBSCRIBER_VIEW Procedure

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

Syntax

DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW (
   subscription_handle  IN  NUMBER,
   source_schema        IN  VARCHAR2,
   source_table         IN  VARCHAR2,
   view_name            OUT VARCHAR2);

Parameters

Table 27-10 PREPARE_SUBSCRIBER_VIEW Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.
`source_schema`	Schema name where the source table resides.
`source_table`	Name of the published source table that belongs to the subscription handle.
`view_name`	Name of the newly-created view that will return the change data for the source table.

Exceptions

Table 27-11 PREPARE_SUBSCRIBER_VIEW Procedure Exceptions

Exception	Description
`ORA-31425`	The specified subscription handle does not exist, or it does not belong to this user or application.
`ORA-31429`	The subscription has not been activated. The subscription handle must be activated before you use the `PREPARE_SUBSCRIBER_VIEW` procedure. Call the `ACTIVATE_SUBSCRIPTION` procedure for this subscription handle and then try the original command again.
`ORA-31430`	An earlier subscriber view was not dropped prior to making this call. Call the `DROP_SUBSCRIBER_VIEW` procedure and then try the original command again.
`ORA-31432`	The schema name or source table does not exist or does not belong to this subscription. Check the spelling of the `schema_name` and `source_table` parameters. Verify the specified table exists in the specified schema and is subscribed to by the subscription handle.

Usage Notes

This procedure creates a subscriber view in the subscriber's schema in which to display the change data. After the subscriber view is created, the subscriber can select change data that is within the boundaries defined (by the EXTEND_WINDOW procedure) for the subscription window.
The Change Data Capture system determines the name of the subscriber view and returns the name to the subscriber. The name of the subscriber view is constant over the life of the subscription. To access the change data, there must be a view for each source table in the subscription. Applications use a SELECT statement from these views and retrieve the change data. For the purpose of the following example, assume that sys.sub9view was the view name returned by the PREPARE_SUBSCRIBER_VIEW procedure:
```
    SELECT * FROM sys.sub9view;
    .
    .
    .
```
If a view already exists with the same view_name (for example, if the previous view was not dropped with a DROP VIEW DDL statement), an exception occurs. The PREPARE_SUBSCRIBER_VIEW procedure checks if the underlying change table still exists.

Examples

EXECUTE sys.DBMS_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW( \
   SUBSCRIPTION_HANDLE =>:subhandle, \
   SOURCE_SCHEMA =>'scott', \
   SOURCE_TABLE => 'emp', \
   VIEW_NAME => :viewname);

DROP_SUBSCRIBER_VIEW Procedure

This procedure drops a subscriber view from the subscriber's schema.

Syntax

DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW (
   subscription_handle  IN NUMBER,
   source_schema        IN VARCHAR2,
   source_table         IN VARCHAR2);

Parameters

Table 27-12 DROP_SUBSCRIBER_VIEW Procedure Parameters

Parameter	Description
subscription_handle	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.
source_schema	Schema name where the source table resides.
source_table	Name of the published source table that belongs to the subscription handle.

Exceptions

Table 27-13 DROP_SUBSCRIBER_VIEW Procedure Exceptions

Exception	Description
`ORA-31425`	Subscription handle does not exist or handle does not belong to this user. Call the function again with a valid subscription handle.
`ORA-31429`	The subscription has not been activated. Check the subscription handle and correct it, if necessary. Call the `ACTIVATE_SUBSCRIPTION` procedure for this subscription handle and then try the original command again.
`ORA-31432`	The schema_name.source_table does not exist or does not belong to this subscription. Check the spelling of the `schema_name` and `source_table` parameters. Verify the specified table exists in the specified schema and is subscribed to by the subscription handle.
`ORA-31433`	The subscriber view does not exist. Either you specified an incorrect source table or its view is already dropped.

Usage Notes

The subscriber view you want to drop must have been created with a prior call to the DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW procedure.
You must use this procedure to drop the subscriber view prior to dropping a subscription using the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW( \
   SUBSCRIPTION_HANDLE =>:subhandle, \
   SOURCE_SCHEMA =>'scott', \
   SOURCE_TABLE => 'emp');

PURGE_WINDOW Procedure

The subscriber calls this procedure to notify the capture system it is finished processing a block of changes. The PURGE_WINDOW procedure sets the low-water mark so that the subscription no longer sees any data, effectively making the subscription window empty.

Syntax

DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
   subscription_handle   IN NUMBER);

Parameters

Table 27-14 PURGE_WINDOW Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.

Exceptions

Table 27-15 PURGE_WINDOW Procedure Exceptions

Exception	Description
`ORA-31425`	Subscription handle does not exist or handle does not belong to this user. Call the function again with a valid subscription handle.
`ORA-31429`	The subscription handle must be activated before you use the `EXTEND_WINDOW` procedure. Call the `ACTIVATE_SUBSCRIPTION` procedure for this subscription handle and then try the original command again.
`ORA-31430`	The subscriber view was not dropped prior to making this call. Call the DROP_SUBSCRIBER_VIEW Procedure and then try the original command again.

Usage Notes

When finished with a set of changes, the subscriber purges the subscription window with the PURGE_WINDOW procedure. By this action the subscriber performs the following functions:
- Informs the change capture system that the subscriber is ready to receive the next batch of change data.
- Enables the system to remove change data that is no longer needed by any subscribers.
The Change Data Capture system manages the change data to ensure that it is available as long as there are subscribers who need it.

Example

EXECUTE sys.DBMS_CDC_SUBSCRIBE.PURGE_WINDOW ( \
SUBSCRIPTION_HANDLE=>:subhandle);

DROP_SUBSCRIPTION Procedure

This procedure drops a subscription that was created with a prior call to the GET_SUBSCRIPTION_HANDLE procedure.

Syntax

DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION (
   subscription_handle  IN NUMBER);

Parameters

Table 27-16 DROP_SUBSCRIPTION Procedure Parameters

Parameter	Description
`subscription_handle`	Unique number of the subscription handle that was returned by a previous call to the `GET_SUBSCRIPTION_HANDLE` procedure.

Exceptions

Table 27-17 DROP_SUBSCRIPTION Procedure Exceptions

Exception	Description
`ORA-31425`	Subscription handle does not exist or handle does not belong to this user. Call the function again with a valid subscription handle.
`ORA-31430`	The subscriber view was not dropped prior to making this call. Call the `DROP_SUBSCRIBER_VIEW` procedure and then try the original command again.

Usage Notes

Prior to dropping a subscription, you must drop the subscriber view using the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW procedure.

Example

EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION (\

SUBSCRIPTION_HANDLE => :subhandle);