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 26-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-separated 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 26-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');