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_APPLY_ADM

Version 11.1
 
General Information
Purpose Provides subprograms to start, stop, and configure apply processes.
Source {ORACLE_HOME}/rdbms/admin/dbmsapp.sql
First Available 9.2.0.1

Dependencies
ANYDATA DBMS_RULE_ADM
DBMS_APPLY_ADM_INTERNAL DBMS_STREAMS
DBMS_APPLY_ERROR DBMS_STREAMS_ADM
DBMS_AQADM_SYS DBMS_STREAMS_ADM_UTL
DBMS_CAPTURE_SWITCH_INTERNAL DBMS_STREAMS_ADM_UTL_INVOK
DBMS_FILE_GROUP DBMS_STREAMS_AUTH
DBMS_FILE_GROUP_INTERNAL_INVOK DBMS_STREAMS_DECL
DBMS_LOGREP_IMP DBMS_STREAMS_MT
DBMS_LOGREP_LIB DBMS_STREAMS_SM
DBMS_LOGREP_UTIL DBMS_SYS_ERROR
DBMS_LOGREP_UTIL_INVOK DBMS_UTILITY
DBMS_REPCAT_COMMON_UTL RE$NV_ARRAY
DBMS_REPCAT_DECL RE$NV_LIST
DBMS_REPCAT_UTL  

Exceptions
Number Definition
-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;
-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;
Security Model Execute is granted to execute_catalog_role
 
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
 
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);
TBD
 
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);
TBD
 
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);
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_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);
TBD
 
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);
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);
TBD
 
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);
TBD
 

Related Topics

Advanced Queuing
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [58 users online]    © 2010 psoug.org