Oracle DBMS_CDC_PUBLISH
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Public interface for the Change Data Capture Publishers
AUTHID CURRENT_USER
Dependencies
CDC_CHANGE_SETS$ DBMS_CDC_IPUBLISH DBMS_SYS_ERROR
CHANGE_SETS DBMS_CDC_SYS_IPUBLISH DBMS_UTILITY
DBA_QUEUE_PUBLISHERS DBMS_CDC_UTILITY V$BUFFERED_PUBLISHERS
DBMS_CDCPUB_LIB DBMS_LOGMNR_CDC_PUBLISH WRH$_STREAMS_POOL_ADVICE
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
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

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-2014 Daniel A. Morgan All Rights Reserved