CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_CDC_PUBLISH
Version 11.1
 
General Information
Purpose Public interface for the Change Data Capture Publishers
Source {ORACLE_HOME}/rdbms/admin/dbmscdcp.sql
First Available 9.0.1
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

Exceptions
Exception Description
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
Security Model Execute is granted to the EXECUTE_CATALOG_ROLE. Runs under AUTHID CURRENT_USER
 
ALTER_AUTOLOG_CHANGE_SOURCE
Changes the properties of an existing AutoLog change source

10.1 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
 
ALTER_CHANGE_TABLE (new 11g parameter)

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
 
ALTER_HOTLOG_CHANGE_SOURCE
Changes the properties of an existing Distributed HotLog change source

10.2 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

10.1 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
 
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
 
CREATE_CHANGE_TABLE (new 11g parameter)

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 Streams Demo 2
 
CREATE_HOTLOG_CHANGE_SOURCE
Creates a Distributed HotLog change source on the source database when the publisher runs this procedure from the staging database

10.2 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 Streams Demo 2
 
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 Streams Demo 2
 
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
10g Version
dbms_cdc_publish.drop_subscription(subscription_name IN VARCHAR2);
exec dbms_cdc_publish.drop_subscription('CDC_DEMO_SUB');
9i Version / Deprecated dbms_cdc_publish.drop_subscription(subscription_handle IN NUMBER);
Deprecated
 
GET_DDLOPER (new in 11g)
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 (new 11g parameters ?)
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 (new 11g parameter ?)

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
DBMS_CDC_SUBSCRIBE
DBMS_CDC_UTILITY
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [62 users online]    © 2010 psoug.org