Oracle DBMS_CDC_PUBLISH
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Public interface for the Change Data Capture Publishers
AUTHID CURRENT_USER
Dependencies
CDC_CHANGE_SETS$ DBMS_CDC_IPUBLISH DBMS_STANDARD
DBMS_CDCPUB_LIB DBMS_LOGMNR_CDC_PUBLISH  
Documented Yes
Exceptions
Error Code Reason
ORA-31401 Specified change source is not an existing change source
ORA-31402 Unrecognized parameter specified
ORA-31403 Specified change table already contains the specified column
ORA-31406 Specified change source is referenced by a change set
ORA-31407 The end_date must be greater than the begin_date
ORA-31408 Invalid value specified for begin_scn or end_scn
ORA-31409 One or more values for input parameters are incorrect
ORA-31410 Specified change set is not an existing change set
ORA-31411 Specified change set is referenced by a change table
ORA-31415 Specified change set does not exist
ORA-31416 Invalid SOURCE_COLMAP value
ORA-31417 Column list contains control column control-column-name
ORA-31418 Specified source schema does not exist
ORA-31419 Specified source table does not exist
ORA-31420 Unable to submit the purge job
ORA-31421 Change table does not exist
ORA-31422 Specified owner schema does not exist
ORA-31423 Specified change table does not contain the specified column
ORA-31424 Change table has active subscriptions
ORA-31425 Subscription does not exist
ORA-31432 Invalid source table
ORA-31436 Duplicate change source specified
ORA-31437 Invalid value specified for first_scn
ORA-31438 Duplicate change table
ORA-31441 Table is not a change table
ORA-31447 Cannot create change tables in the SYS schema
ORA-31450 Invalid value for change_table_name
ORA-31451 Invalid value for capture_values, expecting: OLD, NEW, or BOTH
ORA-31452 Invalid value for parameter, expecting: Y or N
ORA-31454 Invalid value specified for operation parameter, expecting ADD or DROP
ORA-31455 Nothing to alter
ORA-31456 Error executing a procedure in the DBMS_CDC_UTILITY package
ORA-31459 System triggers for DBMS_CDC_PUBLISH package are not installed
ORA-31467 No column found in the source table
ORA-31468 Cannot process DDL change record
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31471 Invalid OBJECT_ID value
ORA-31480 Staging database and source database cannot be the same
ORA-31481 Change source is not a HotLog change source
ORA-31482 Invalid option for non-distributed HotLog change source
ORA-31483 Cannot have spaces in the parameter
ORA-31484 Source database must be at least 9.2.0.6 or greater
ORA-31485 Invalid database link
ORA-31487 Cannot support begin dates or end dates in this configuration
ORA-31488 Cannot support change set in this configuration
ORA-31497 Invalid value specified for first_scn
ORA-31498 The description and remove_description parameters cannot both be specified
ORA-31499 Null value specified for required parameter
ORA-31501 Specified change source is not an AutoLog change source
ORA-31503 Invalid date supplied for begin_date or end_date
ORA-31504 Cannot alter or drop predefined change source
ORA-31505 Cannot alter or drop predefined change set
ORA-31507 Specified parameter value longer than maximum length
ORA-31508 Invalid parameter value for synchronous change set
ORA-31514 Change set disabled due to capture error
ORA-31532 Cannot enable change source
ORA-31534 Change Data Capture publisher is missing DBA role
ORA-31535 Cannot support change source in this configuration
First Available 9.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmscdcp.sql
Subprograms
 
ALTER_AUTOLOG_CHANGE_SOURCE
Changes the properties of an existing AutoLog change source 10.1g+ Publisher Interface dbms_cdc_publish.alter_autolog_change_source(
change_source_name IN VARCHAR2,
description        IN VARCHAR2 DEFAULT NULL,
remove_description IN CHAR DEFAULT 'N',
first_scn          IN NUMBER DEFAULT NULL);
TBD
 
ALTER_CHANGE_SET
Changes the properties of an existing change set that was created with the CREATE_CHANGE_SET procedure dbms_cdc_publish.alter_change_set(
change_set_name     IN VARCHAR2,
description         IN VARCHAR2 DEFAULT NULL,
remove_description  IN CHAR DEFAULT 'N',
enable_capture      IN CHAR DEFAULT NULL,
recover_after_error IN CHAR DEFAULT NULL,
remove_ddl          IN CHAR DEFAULT NULL,
stop_on_ddl         IN CHAR DEFAULT NULL);
See CDC Demo 2 Linked Below
 
ALTER_CHANGE_TABLE
Adds columns to, or drops columns from, or changes the properties of, a change table that was created with the CREATE_CHANGE_TABLE procedure dbms_cdc_publish.alter_change_table(
owner             IN VARCHAR2,
change_table_name IN VARCHAR2,
operation         IN VARCHAR2,
column_list       IN VARCHAR2,
rs_id             IN CHAR,
row_id            IN CHAR,
user_id           IN CHAR,
timestamp         IN CHAR,
object_id         IN CHAR,
source_colmap     IN CHAR,
target_colmap     IN CHAR,
ddl_markers       IN CHAR DEFAULT NULL);
See CDC Demo 2 Linked Below
 
ALTER_HOTLOG_CHANGE_SOURCE
Changes the properties of an existing Distributed HotLog change source ZZYZX 10gR2+ Publisher Interface dbms_cdc_publish.alter_hotlog_change_source(
change_source_name IN VARCHAR2,
description        IN VARCHAR2 DEFAULT NULL,
remove_description IN CHAR DEFAULT 'N',
enable_source      IN CHAR DEFAULT NULL);
exec dbms_cdc_publish.alter_hotlog_change_source('HOTLOG_SOURCE', 'CDC Demo 2 Change Set', 'N', 'Y');
 
CREATE_AUTOLOG_CHANGE_SOURCE
Creates an AutoLog change source based on of a set of redo log files automatically copied by redo transport services to the system on which the staging database resides
10gR1+ Publisher Interface
dbms_cdc_publish.create_autolog_change_source(
change_source_name IN VARCHAR2,
description        IN VARCHAR2 DEFAULT NULL,
source_database    IN VARCHAR2,
first_scn          IN NUMBER,
online_log         IN CHAR DEFAULT 'N');
See CDC Demo 3 Linked Below
 
CREATE_CHANGE_SET
Allows the publisher to create a change set dbms_cdc_publish.create_change_set(
change_set_name    IN VARCHAR2,
description        IN VARCHAR2 DEFAULT NULL,
change_source_name IN VARCHAR2, -- 'SYNC_SOURCE'
stop_on_ddl        IN CHAR DEFAULT 'N',
begin_date         IN DATE DEFAULT NULL,
end_date           IN DATE DEFAULT NULL);
See CDC Demo 2 Linked Below
 
CREATE_CHANGE_TABLE
Creates a change table in a specified schema dbms_cdc_publish.create_change_table(
owner             IN VARCHAR2,
change_table_name IN VARCHAR2,
change_set_name   IN VARCHAR2,
source_schema     IN VARCHAR2,
source_table      IN VARCHAR2,
column_type_list  IN VARCHAR2,
capture_values    IN VARCHAR2,
rs_id             IN CHAR,
row_id            IN CHAR,
user_id           IN CHAR,
timestamp         IN CHAR,
object_id         IN CHAR,
source_colmap     IN CHAR,
target_colmap     IN CHAR,
options_string    IN VARCHAR2,
ddl_markers       IN CHAR DEFAULT 'Y');
See CDC Demo 2 Linked Below
 
CREATE_HOTLOG_CHANGE_SOURCE
Creates a Distributed HotLog change source on the source database when the publisher runs this procedure from the staging database
10gR2+ Publisher Interface
dbms_cdc_publish.create_hotlog_change_source (
change_source_name IN VARCHAR2,
description        IN VARCHAR2 DEFAULT NULL,
source_database    IN VARCHAR2); -- database link name
exec dbms_cdc_publish.create_hotlog_change_source('HOTLOG_SOURCE', 'CDC Demo 2 Change Set', 'REMOTEDB');
 
DROP_CHANGE_SET
Drops an existing change set that was created with the CREATE_CHANGE_SET procedure dbms_cdc_publish.drop_change_set(change_set_name IN VARCHAR2);
See CDC Demo 2 Linked Below
 
DROP_CHANGE_SOURCE
Drops an existing AutoLog change source that was created with CREATE_AUTOLOG_CHANGE_SOURCE dbms_cdc_publish.drop_change_source(change_source_name IN VARCHAR2);
exec dbms_cdc_publish.drop_change_source('HOTLOG_SOURCE');
 
DROP_CHANGE_TABLE
Drops an existing change table that was created with CREATE_CHANGE_TABLE dbms_cdc_publish.drop_change_table(
owner             IN VARCHAR2,
change_table_name IN VARCHAR2,
force_flag        IN CHAR);
See CDC Demo 2 Linked Below
 
DROP_SUBSCRIBER_VIEW
Drops the view created by dbms_cdc_subscribe.subscribe dbms_cdc_publish.drop_subscriber_view(
subscription_handle IN NUMBER,
source_schema       IN VARCHAR2,
source_table        IN VARCHAR2);
Deprecated
 
DROP_SUBSCRIPTION
Allows a publisher to drop a subscriber created subscription
10gR1+ API
dbms_cdc_publish.drop_subscription(subscription_name IN VARCHAR2);
exec dbms_cdc_publish.drop_subscription('CDC_DEMO_SUB');
9i Version Deprecated API dbms_cdc_publish.drop_subscription(subscription_handle IN NUMBER);
Deprecated
 
GET_DDLOPER
Translates the DDLOPR$ value into text dbms_cdc_publish.get_ddloper(ddloper IN BINARY_INTEGER) RETURN VARCHAR2;
TBD
 
PURGE
Monitors change table usage by all subscriptions, determines which rows are no longer needed and removes them dbms_cdc_publish.purge;
exec dbms_cdc_publish.purge;
 
PURGE_CHANGE_SET
Removes unneeded rows from all change tables in the named change set dbms_cdc_publish.purge_change_set(
change_set_name IN VARCHAR2,
force           IN CHAR DEFAULT 'Y',
purge_date      IN DATE DEFAULT NULL);
exec dbms_cdc_publish.purge_change_set('CDC_DEMO_SET');
 
PURGE_CHANGE_TABLE
Removes unneeded rows from the named change table dbms_cdc_publish.purge_change_table(
owner             IN VARCHAR2,
change_table_name IN VARCHAR2,
force             IN CHAR DEFAULT 'Y',
purge_date        IN DATE DEFAULT NULL);
exec dbms_cdc_publish.purge_change_table('CDC_DEMO_CT');

Related Topics
Advanced Queuing
Change Data Capture Demo 1
Change Data Capture Demo 2
Change Data Capture Demo 3
DBMS_CDC_EXPVDP
DBMS_CDC_IMPDPV
DBMS_CDC_SUBSCRIBE
DBMS_CDC_UTILITY
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved