Oracle DBMS_APPLY_ADM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose Provides subprograms to start, stop, and configure apply processes.
AUTHID CURRENT_USER
Dependencies
ALL_APPLY_ERROR_MESSAGES DBMS_FILE_GROUP DBMS_STREAMS_DECL
ANYDATA DBMS_FILE_GROUP_INTERNAL_INVOK DBMS_STREAMS_HANDLER_ADM
CDB_APPLY DBMS_LOGREP_IMP DBMS_STREAMS_MT
CDB_APPLY_ERROR_MESSAGES DBMS_LOGREP_LIB DBMS_STREAMS_SM
CDB_XSTREAM_RULES DBMS_LOGREP_UTIL DBMS_SYS_ERROR
DBA_APPLY DBMS_LOGREP_UTIL_INVOK DBMS_UTILITY
DBA_APPLY_ERROR_MESSAGES DBMS_REPCAT_COMMON_UTL DBMS_XSTREAM_ADM
DBA_XSTREAM_RULES DBMS_REPCAT_DECL DBMS_XSTREAM_ADM_UTL
DBMS_APPLY_ADM_INTERNAL DBMS_REPCAT_UTL DBMS_XSTREAM_AUTH_IVK
DBMS_APPLY_ADM_IVK DBMS_RULE_ADM DBMS_XSTREAM_GG_ADM
DBMS_APPLY_ERROR DBMS_STREAMS DBMS_XSTREAM_UTL_IVK
DBMS_APPLY_HANDLER_ADM DBMS_STREAMS_ADM_IVK RE$NV_ARRAY
DBMS_AQADM_SYS DBMS_STREAMS_ADM_UTL RE$NV_LIST
DBMS_CAPTURE_ADM_IVK DBMS_STREAMS_ADM_UTL_INVOK _DBA_GGXSTREAM_OUTBOUND
DBMS_CAPTURE_SWITCH_INTERNAL DBMS_STREAMS_AUTH  
Documented Yes
Exceptions
Error Code Reason
-23605 invalidparam EXCEPTION;
PRAGMA exception_init(invalidparam, -23605);
invalidparam_num NUMBER := -23605;
-23606 invalidobj EXCEPTION;
PRAGMA exception_init(invalidobj, -23606);
invalidobj_num NUMBER := -23606;
-23607 invalidcol EXCEPTION;
PRAGMA exception_init(invalidcol, -23607);
invalidcol_num NUMBER := -23607;
-23608 invalidrescol EXCEPTION;
PRAGMA exception_init(invalidrescol, -23608);
invalidrescol_num NUMBER := -23608;
-23665 conflict_handler_not_found EXCEPTION;
PRAGMA exception_init(conflict_handler_not_found, -23665);
conflict_handler_not_found_num NUMBER := -23665;
-23666  default_col_group_exists EXCEPTION;
PRAGMA exception_init(default_col_group_exists, -23666);
default_col_group_exists_num NUMBER := -23666;
-23667  col_used_by_conf_handler EXCEPTION;
PRAGMA exception_init(col_used_by_conf_handler, -23667);
col_used_by_conf_handler_num NUMBER := -23667;
-23668 delta_col_non_numeric EXCEPTION;
PRAGMA exception_init(delta_col_non_numeric, -23668);
delta_col_non_numeric_num NUMBER := -23668;
-23669 conflict_handler_found EXCEPTION;
PRAGMA exception_init(conflict_handler_found, -23669);
conflict_handler_found_num NUMBER := -23669;
-23670 duplicates_in_column_list EXCEPTION;
PRAGMA exception_init(duplicates_in_column_list, -23670);
duplicates_in_column_list_num NUMBER := -23670;
-23671 def_col_group_required EXCEPTION;
PRAGMA exception_init(def_col_group_required, -23671);
def_col_group_required_num NUMBER := -23671;
-23675 incompat_dml_conf_params EXCEPTION;
PRAGMA exception_init(incompat_dml_conf_params, -23675);
incompat_dml_conf_params_num NUMBER := -23675;
-25343 export_errq_error EXCEPTION;
PRAGMA exception_init(export_errq_error, -25343);
export_errq_num NUMBER := -25343;
-26669 incompatible_params EXCEPTION;
PRAGMA exception_init(incompatible_params, -26669);
incompatible_params_num NUMBER := -26669;
-26692 invalidparamformat EXCEPTION;
PRAGMA exception_init(invalidparamformat, -26692);
invalidparamformat_num NUMBER := -26692;
-26693 drop_unused_rule_set_error EXCEPTION;
PRAGMA exception_init(drop_unused_rule_set_error, -26693);
drop_unused_rule_set_error_num NUMBER := -26693;
-26695 lock_error EXCEPTION;
PRAGMA exception_init(lock_error, -26695);
lock_error_num NUMBER := -26695;
First Available 9.2.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsapp.sql
Subprograms
 
ADD_STMT_HANDLER
Create a statement handler with a user-specified statement and add it to apply

Overload 1
dbms_apply_adm.add_stmt_handler(
object_name    IN VARCHAR2,
operation_name IN VARCHAR2,
handler_name   IN VARCHAR2,
statement      IN CLOB,
apply_name     IN VARCHAR2 DEFAULT NULL,
comment        IN VARCHAR2 DEFAULT NULL);
TBD
Add an LCR processing statement handler to apply

Overload 2
dbms_apply_adm.add_stmt_handler(
object_name    IN VARCHAR2,
operation_name IN VARCHAR2,
handler_name   IN VARCHAR2,
apply_name     IN VARCHAR2 DEFAULT NULL);
TBD
 
ALTER_APPLY
Alters an apply process dbms_apply_adm.alter_apply(
apply_name               IN VARCHAR2,
rule_set_name            IN VARCHAR2 DEFAULT NULL,
remove_rule_set          IN BOOLEAN  DEFAULT FALSE,
message_handler          IN VARCHAR2 DEFAULT NULL
remove_message_handler   IN BOOLEAN  DEFAULT FALSE,
ddl_handler              IN VARCHAR2 DEFAULT NULL,
remove_ddl_handler       IN BOOLEAN  DEFAULT FALSE,
apply_user               IN VARCHAR2 DEFAULT NULL,
apply_tag                IN RAW      DEFAULT NULL,
remove_apply_tag         IN BOOLEAN  DEFAULT FALSE,
precommit_handler        IN VARCHAR2 DEFAULT NULL,
remove_precommit_handler IN BOOLEAN  DEFAULT FALSE,
negative_rule_set_name   IN VARCHAR2 DEFAULT NULL,
remove_negative_rule_set IN BOOLEAN  DEFAULT FALSE);
TBD
 
COMPARE_OLD_VALUES
Specifies whether to compare the old value of one or more columns in a row logical change record (row LCR) with the current value of the corresponding columns at the destination site during apply

Overload 1
dbms_apply_adm.compare_old_values(
object_name         IN VARCHAR2,
column_list         IN VARCHAR2,
operation           IN VARCHAR2 DEFAULT 'UPDATE',
compare             IN BOOLEAN  DEFAULT TRUE,
apply_database_link IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_apply_adm.compare_old_values(
object_name         IN VARCHAR2,
column_table        IN dbms_utility.lname_array,
operation           IN VARCHAR2 DEFAULT 'UPDATE',
compare             IN BOOLEAN  DEFAULT TRUE,
apply_database_link IN VARCHAR2 DEFAULT NULL);
TBD
 
CREATE_APPLY
Creates an apply process dbms_apply_adm.create_apply(
queue_name             IN VARCHAR2,
apply_name             IN VARCHAR2,
rule_set_name          IN VARCHAR2 DEFAULT NULL,
message_handler        IN VARCHAR2 DEFAULT NULL,
ddl_handler            IN VARCHAR2 DEFAULT NULL,
apply_user             IN VARCHAR2 DEFAULT NULL,
apply_database_link    IN VARCHAR2 DEFAULT NULL,
apply_tag              IN RAW      DEFAULT '00',
apply_captured         IN BOOLEAN  DEFAULT FALSE,
precommit_handler      IN VARCHAR2 DEFAULT NULL,
negative_rule_set_name IN VARCHAR2 DEFAULT NULL,
source_database        IN VARCHAR2 DEFAULT NULL);
TBD
 
CREATE_OBJECT_DEPENDENCY
Creates an object dependency dbms_apply_adm.create_object_dependency(
object_name        IN VARCHAR2,
parent_object_name IN VARCHAR2);
TBD
 
DELETE_ALL_ERRORS
Deletes all the error transactions for the specified apply process dbms_apply_adm.delete_all_errors(apply_name IN VARCHAR2 DEFAULT NULL);
TBD
 
DELETE_ERROR
Deletes the specified error transaction dbms_apply_adm.delete_error(local_transaction_id IN VARCHAR2);
TBD
 
DROP_APPLY
Drops an apply process dbms_apply_adm.drop_apply(
apply_name            IN VARCHAR2,
drop_unused_rule_sets IN BOOLEAN DEFAULT FALSE);
TBD
 
DROP_OBJECT_DEPENDENCY
Drops an object dependency dbms_apply_adm.drop_object_dependency(
object_name        IN VARCHAR2,
parent_object_name IN VARCHAR2);
TBD
 
EXECUTE_ALL_ERRORS
Re-executes the error transactions for the specified apply process dbms_apply_adm.execute_all_errors(
apply_name      IN VARCHAR2 DEFAULT NULL,
execute_as_user IN BOOLEAN  DEFAULT FALSE);
TBD
 
EXECUTE_ERROR
Re-executes a specified error transaction dbms_apply_adm.execute_error(
local_transaction_id IN VARCHAR2,
execute_as_user      IN BOOLEAN  DEFAULT FALSE,
user_procedure       IN VARCHAR2 DEFAULT NULL);
TBD
 
GET_ERROR_MESSAGE
Returns the message payload from the error queue for the specified message number and transaction identifier

Overload 1
dbms_apply_adm.get_error_message(
message_number       IN NUMBER,
local_transaction_id IN VARCHAR2)
RETURN SYS.ANYDATA;
TBD
Overload 2 dbms_apply_adm.get_error_message(
message_number         IN  NUMBER,
local_transaction_id   IN  VARCHAR2,
destination_queue_name OUT VARCHAR2,
execute                OUT BOOLEAN)
RETURN SYS.ANYDATA;
TBD
 
HANDLE_COLLISIONS (new 12.1)
Sets a collision handler for a given apply and source/target pair dbms_apply_adm.handle_collisions(
apply_name    IN VARCHAR2,
enable        IN BOOLEAN,
object        IN VARCHAR2,
source_object IN VARCHAR2 DEFAULT NULL);
TBD
 
REMOVE_STMT_HANDLER
Removes an LCR processing statement handler from apply dbms_apply_adm.remove_stmt_handler(
object_name    IN VARCHAR2,
operation_name IN VARCHAR2,
handler_name   IN VARCHAR2,
apply_name     IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_CHANGE_HANDLER
Sets or unsets a change handler that tracks changes for a specified operation on a specified database for a single apply component dbms_apply_adm.set_dml_handler(
change_table_name   IN VARCHAR2,
source_table_name   IN VARCHAR2,
capture_values      IN VARCHAR2,
apply_name          IN VARCHAR2,
operation_name      IN VARCHAR2,
change_handler_name IN VARCHAR2 DEFAULT NULL);
desc dba_apply_change_handlers

SELECT handler_name, change_table_owner, change_table_name
FROM dba_apply_change_handlers;
 
SET_DML_CONFLICT_HANDLER (new 12.1)
Sets a dml conflict handler for the specified apply_name, target object, source object, operation_name, conflict_type, and method_name

Overload 1
dbms_apply_adm.set_conflict_handler(
apply_name            IN VARCHAR2,
conflict_handler_name IN VARCHAR2,
object                IN VARCHAR2 DEFAULT NULL,
operation_name        IN VARCHAR2 DEFAULT NULL,
conflict_type         IN VARCHAR2 DEFAULT NULL,
method_name           IN VARCHAR2 DEFAULT NULL,
column_list           IN VARCHAR2 DEFAULT NULL,
resolution_column     IN VARCHAR2 DEFAULT NULL,
source_object         IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_apply_adm.set_conflict_handler(
TBD
 
SET_DML_HANDLER
Alters operation options for a specified object with a specified apply process dbms_apply_adm.set_dml_handler(
object_name         IN VARCHAR2,
object_type         IN VARCHAR2,
operation_name      IN VARCHAR2,
error_handler       IN BOOLEAN  := FALSE,
user_procedure      IN VARCHAR2,
apply_database_link IN VARCHAR2 DEFAULT NULL,
apply_name          IN VARCHAR2 DEFAULT NULL,
assemble_lobs       IN BOOLEAN  := FALSE);
TBD
 
SET_ENQUEUE_DESTINATION
Sets the queue where the apply process automatically enqueues a message that satisfies the specified rule dbms_apply_adm.set_enqueue_destination(
rule_name              IN VARCHAR2,
destination_queue_name IN VARCHAR2);
exec dbms_apply_adm.set_enqueue_destination('UW_EXCL_RULE1', 'UW_REMOTE');
 
SET_EXECUTE
Specifies whether a message that satisfies the specified rule is executed by an apply process dbms_apply_adm.set_execute(
rule_name IN VARCHAR2,
execute   IN BOOLEAN);
exec dbms_apply_adm.set_execute('UW_EXCL_RULE1', TRUE);
 
SET_GLOBAL_INSTANTIATION_SCN (new 12.1 parameter)
Records the specified instantiation SCN for the specified source database and, optionally, for the schemas at the source database and the tables owned by these schemas dbms_apply_adm.set_global_instantiation_scn(
source_database_name IN VARCHAR2,
instantiation_scn    IN NUMBER,
apply_database_link  IN VARCHAR2 DEFAULT NULL,
recursive            IN BOOLEAN  DEFAULT FALSE,
source_root_name     IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_KEY_COLUMNS
Records the set of columns to be used as the substitute primary key for local apply purposes and removes existing substitute primary key columns for the specified object if they exist

Overload 1
dbms_apply_adm.set_key_columns(
object_name         IN VARCHAR2,
column_list         IN VARCHAR2,
apply_database_link IN VARCHAR2 := NULL);
TBD
Overload 2 dbms_apply_adm.set_key_columns(
object_name         IN VARCHAR2,
column_table        IN dbms_utility.name_array,
apply_database_link IN VARCHAR2 := NULL);
TBD
 
SET_PARAMETER
Sets an apply parameter to the specified value dbms_apply_adm.set_parameter(
apply_name IN VARCHAR2,
parameter  IN VARCHAR2,
value      IN VARCHAR2);
TBD
 
SET_REPERROR_HANDLER (new 12.1)
Sets a reperror handler for an apply, src table, tgt table, and error number dbms_apply_adm.set_reperror_handler(
apply_name    IN VARCHAR2,
object        IN VARCHAR2,
error_number  IN NUMBER,
method        IN VARCHAR2,
source_object IN VARCHAR2 DEFAULT NULL,
max_retries   IN NUMBER DEFAULT NULL,
delay_csecs   IN NUMBER DEFAULT 6000);
TBD
 
SET_SCHEMA_INSTANTIATION_SCN (new 12.1 parameter)
Records the specified instantiation SCN for the specified schema in the specified source database and, optionally, for the tables owned by the schema at the source database dbms_apply_adm.set_schema_instantiation_scn(
source_schema_name   IN VARCHAR2,
source_database_name IN VARCHAR2,
instantiation_scn    IN NUMBER,
apply_database_link  IN VARCHAR2 DEFAULT NULL,
recursive            IN BOOLEAN  DEFAULT FALSE,
source_root_name     IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_apply_adm.set_schema_instantiation_scn(
  source_schema_name => 'SCOTT',
  source_database_name => 'ORABASE',
  instantiation_scn => 4648838),
  apply_database_link => NULL,
  recursive => FALSE);
END;
/
 
SET_TABLE_INSTANTIATION_SCN (new 12.1 parameter)
Records the specified instantiation SCN for the specified table in the specified source database dbms_apply_adm.set_table_instantiation_scn(
source_object_name   IN VARCHAR2,
source_database_name IN VARCHAR2,
instantiation_scn    IN NUMBER,
apply_database_link  IN VARCHAR2 DEFAULT NULL
source_root_name     IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_UPDATE_CONFLICT_HANDLER
Adds, updates, or drops an update conflict handler for the specified object dbms_apply_adm.set_update_conflict_handler(
object_name         IN VARCHAR2,
method_name         IN VARCHAR2,
resolution_column   IN VARCHAR2,
column_list         IN dbms_utility.name_array,
apply_database_link IN VARCHAR2 DEFAULT NULL);
DECLARE
 cols dbms_utility.name_array;
BEGIN
  cols(1) := 'salary';
  cols(2) := 'commission_pct';

  dbms_apply_adm.set_update_conflict_handler('hr.employees',
  'MAXIMUM', 'salary', cols);
END;
/
 
SET_VALUE_DEPENDENCY
Sets or removes a value dependency

Overload 1
dbms_apply_adm.set_value_dependency(
dependency_name IN VARCHAR2,
object_name     IN VARCHAR2,
attribute_table IN dbms_utility.name_array);
TBD
Overload 2 dbms_apply_adm.set_value_dependency(
dependency_name IN VARCHAR2,
object_name     IN VARCHAR2,
attribute_list  IN VARCHAR2);
TBD
 
START_APPLY
Directs the apply process to start applying messages dbms_apply_adm.start_apply(apply_name IN VARCHAR2);
exec dbms_apply_adm.start_apply('UW_APPLY');
 
STOP_APPLY
Stops the apply process from applying any messages and rolls back any unfinished transactions being applied dbms_apply_adm.stop_apply(
apply_name IN VARCHAR2,
force      IN BOOLEAN DEFAULT FALSE);
exec dbms_apply_adm.stop_apply('UW_APPLY', TRUE);

Related Topics
DBMS_APPLY_PROCESS
DBMS_AQADM
DBMS_CAPTURE_ADM
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_UTILITY
Packages
Streams Demo 1

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