Oracle DBMS_APPLY_ADM
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Provides subprograms to start, stop, and configure apply processes.
AUTHID CURRENT_USER
Dependencies
ALL_APPLY_ERROR_MESSAGES DBMS_FILE_GROUP DBMS_STREAMS_AUTH
ANYDATA DBMS_FILE_GROUP_INTERNAL_INVOK DBMS_STREAMS_DECL
CDB_APPLY DBMS_GOLDENGATE_ADM DBMS_STREAMS_MT
CDB_APPLY_ERROR_MESSAGES DBMS_GOLDENGATE_AUTH DBMS_STREAMS_SM
CDB_XSTREAM_RULES DBMS_LOCK DBMS_SYS_ERROR
DBA_APPLY DBMS_LOGREP_IMP DBMS_UTILITY
DBA_APPLY_ERROR_MESSAGES DBMS_LOGREP_LIB DBMS_XSTREAM_ADM
DBA_XSTREAM_RULES DBMS_LOGREP_UTIL DBMS_XSTREAM_ADM_UTL
DBMS_APPLY_ADM_INTERNAL DBMS_LOGREP_UTIL_INVOK DBMS_XSTREAM_AUTH_IVK
DBMS_APPLY_ADM_IVK DBMS_RULE_ADM DBMS_XSTREAM_GG
DBMS_APPLY_ERROR DBMS_STANDARD DBMS_XSTREAM_GG_ADM
DBMS_AQADM_INV DBMS_STREAMS DBMS_XSTREAM_UTL_IVK
DBMS_AQADM_SYS DBMS_STREAMS_ADM_IVK PLITBLM
DBMS_ASSERT DBMS_STREAMS_ADM_UTL RE$NV_ARRAY
DBMS_CAPTURE_ADM_IVK DBMS_STREAMS_ADM_UTL_INVOK RE$NV_LIST
DBMS_CAPTURE_SWITCH_INTERNAL    
Documented Yes: Packages and Types Reference
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
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsapp.sql
Subprograms
ALTER_APPLY EXECUTE_ALL_ERRORS SET_KEY_COLUMNS
CLEAR_KEY_COLUMNS EXECUTE_ERROR SET_PARAMETER
COMPARE_OLD_VALUES GET_ERROR_MESSAGE SET_REPERROR_HANDLER
CREATE_APPLY HANDLE_COLLISIONS SET_SCHEMA_INSTANTIATION_SCN
CREATE_OBJECT_DEPENDENCY SET_DML_CONFLICT_HANDLER SET_TABLE_INSTANTIATION_SCN
DELETE_ALL_ERRORS SET_DML_HANDLER SET_UPDATE_CONFLICT_HANDLER
DELETE_ERROR SET_ENQUEUE_DESTINATION SET_VALUE_DEPENDENCY
DROP_APPLY SET_EXECUTE START_APPLY
DROP_OBJECT_DEPENDENCY SET_GLOBAL_INSTANTIATION_SCN STOP_APPLY
 
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
 
CLEAR_KEY_COLUMNS
Clear all substitute "primary keys" defined for the specified apply dbms_apply_adm.clear_key_columns(apply_name IN VARCHAR2 := NULL);
exec dbms_apply_adm.clear_key_columns('*');
 
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
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
 
SET_DML_CONFLICT_HANDLER
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(
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_table          IN dbms_utility.lname_array,
resolution_column     IN VARCHAR2 DEFAULT NULL,
source_object         IN VARCHAR2 DEFAULT NULL);
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  := TRUE);
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
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. Column_list is a comma-separated list of columns, with no space between columns.

Overload 1		 dbms_apply_adm.set_key_columns(
object_name         IN VARCHAR2,
column_list         IN VARCHAR2,
apply_database_link IN VARCHAR2 := NULL,
apply_name          IN VARCHAR2 := NULL);
TBD
Index for column_table is is 1-based, increasing, dense, and terminated by a NULL.

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,
apply_name          IN VARCHAR2 := NULL);
TBD
Index for column_table is is 1-based, increasing, dense, and terminated by a NULL

Overload 3		 dbms_apply_adm.set_key_columns(
object_name         IN VARCHAR2,
column_table        IN dbms_utility.quoted_name_array,
apply_database_link IN VARCHAR2 := NULL,
apply_name          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 DEFAULT NULL);
TBD
 
SET_REPERROR_HANDLER
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
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
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
Built-in Functions
Built-in Packages
DBMS_APPLY_ADM_INTERNAL
DBMS_APPLY_ADM_IVK
DBMS_APPLY_ERROR
DBMS_APPLY_PROCESS
DBMS_AQADM
DBMS_CAPTURE_ADM_IVK
DBMS_CAPTURE_SWITCH_INTERNAL
DBMS_FILE_GROUP
DBMS_GOLDENGATE_ADM
DBMS_GOLDENGATE_AUTH
DBMS_LOCK
DBMS_LOGREP_UTL
DBMS_LOGREP_UTL_INVOK
DBMS_RULE_ADM
DBMS_STANDARD
DBMS_STREAMS
DBMS_STREAMS_ADM_IVK
DBMS_STREAMS_ADM_UTL
DBMS_STREAMS_ADM_UTL_INVOK
DBMS_STREAMS_AUTH
DBMS_STREAMS_DECL
DBMS_SYS_ERROR
DBMS_UTILITY
DBMS_XSTREAM_ADM
DBMS_XSTREAM_ADM_UTL
DBMS_XSTREAM_AUTH_IVK
DBMS_XSTREAM_GG
DBMS_XSTREAM_GG_ADM
DBMS_XSTREAM_UTL_IVK
DBMS_PLITBLM
What's New In 21c
What's New In 23c

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