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