1 PACKAGE dbms_apply_adm AUTHID CURRENT_USER AS
2
3 invalidparam EXCEPTION;
4 PRAGMA exception_init(invalidparam, -23605);
5 invalidparam_num NUMBER := -23605;
6
7 invalidobj EXCEPTION;
8 PRAGMA exception_init(invalidobj, -23606);
9 invalidobj_num NUMBER := -23606;
10
11 invalidcol EXCEPTION;
12 PRAGMA exception_init(invalidcol, -23607);
13 invalidcol_num NUMBER := -23607;
14
15 invalidrescol EXCEPTION;
16 PRAGMA exception_init(invalidrescol, -23608);
17 invalidrescol_num NUMBER := -23608;
18
19 export_errq_error EXCEPTION;
20 PRAGMA exception_init(export_errq_error, -25343);
21 export_errq_num NUMBER := -25343;
22
23 invalidparamformat EXCEPTION;
24 PRAGMA exception_init(invalidparamformat, -26692);
25 invalidparamformat_num NUMBER := -26692;
26
27 drop_unused_rule_set_error EXCEPTION;
28 PRAGMA exception_init(drop_unused_rule_set_error, -26693);
29 drop_unused_rule_set_error_num NUMBER := -26693;
30
31 lock_error EXCEPTION;
32 PRAGMA exception_init(lock_error, -26695);
33 lock_error_num NUMBER := -26695;
34
35 incompatible_params EXCEPTION;
36 PRAGMA exception_init(incompatible_params, -26669);
37 incompatible_params_num NUMBER := -26669;
38
39 conflict_handler_not_found EXCEPTION;
40 PRAGMA exception_init(conflict_handler_not_found, -23665);
41 conflict_handler_not_found_num NUMBER := -23665;
42
43 default_col_group_exists EXCEPTION;
44 PRAGMA exception_init(default_col_group_exists, -23666);
45 default_col_group_exists_num NUMBER := -23666;
46
47 col_used_by_conf_handler EXCEPTION;
48 PRAGMA exception_init(col_used_by_conf_handler, -23667);
49 col_used_by_conf_handler_num NUMBER := -23667;
50
51 delta_col_non_numeric EXCEPTION;
52 PRAGMA exception_init(delta_col_non_numeric, -23668);
53 delta_col_non_numeric_num NUMBER := -23668;
54
55 conflict_handler_found EXCEPTION;
56 PRAGMA exception_init(conflict_handler_found, -23669);
57 conflict_handler_found_num NUMBER := -23669;
58
59 duplicates_in_column_list EXCEPTION;
60 PRAGMA exception_init(duplicates_in_column_list, -23670);
61 duplicates_in_column_list_num NUMBER := -23670;
62
63 def_col_group_required EXCEPTION;
64 PRAGMA exception_init(def_col_group_required, -23671);
65 def_col_group_required_num NUMBER := -23671;
66
67 incompat_dml_conf_params EXCEPTION;
68 PRAGMA exception_init(incompat_dml_conf_params, -23675);
69 incompat_dml_conf_params_num NUMBER := -23675;
70
71 -- prototype procedure for starting an apply process
72 PROCEDURE start_apply(apply_name IN VARCHAR2);
73
74 -- prototype procedure for stopping an apply process
75 PROCEDURE stop_apply(apply_name IN VARCHAR2,
76 force IN BOOLEAN DEFAULT FALSE);
77
78 -- procedure for setting apply process parameters
79 -- value=NULL will set the parameter to its default value.
80 PROCEDURE set_parameter(apply_name IN VARCHAR2,
81 parameter IN VARCHAR2,
82 value IN VARCHAR2 DEFAULT NULL);
83
84 -- records the set of columns to be used as the "primary key"
85 -- for an apply engine.
86 -- If apply_name is NULL, this key will be used for all the
87 -- apply engines, which are interested in LCRs for object_name.
88 -- This includes apply engines for applying LCRs to non-Oracle
89 -- store.
90 -- The existence of object_name can not be verified since
91 -- an apply engine for a non Oracle store may not have object_name
92 -- locally.
93 PROCEDURE set_key_columns(object_name IN VARCHAR2,
94 column_list IN VARCHAR2,
95 apply_database_link IN VARCHAR2 := NULL);
96 -- column_list is a comma-separated list of columns, with no space
97 -- between columns.
98
99 PROCEDURE set_key_columns(object_name IN VARCHAR2,
100 column_table IN dbms_utility.name_array,
101 apply_database_link IN VARCHAR2 := NULL);
102 -- Index for column_table is is 1-based, increasing, dense, and
103 -- terminated by a NULL.
104
105 PROCEDURE set_key_columns(object_name IN VARCHAR2,
106 column_table IN dbms_utility.quoted_name_array,
107 apply_database_link IN VARCHAR2 := NULL);
108 -- Index for column_table is is 1-based, increasing, dense, and
109 -- terminated by a NULL.
110
111 -- sets the dml handler for a specified object
112 PROCEDURE set_dml_handler(object_name IN VARCHAR2,
113 object_type IN VARCHAR2,
114 operation_name IN VARCHAR2,
115 error_handler IN BOOLEAN:= FALSE,
116 user_procedure IN VARCHAR2,
117 apply_database_link IN VARCHAR2 DEFAULT NULL,
118 apply_name IN VARCHAR2 DEFAULT NULL,
119 assemble_lobs IN BOOLEAN:= TRUE);
120
121 FUNCTION get_error_message(
122 message_number IN NUMBER,
123 local_transaction_id IN VARCHAR2) RETURN sys.anydata;
124 -- Returns the logical change record from the error queue for the
125 -- specified message number and deferred transaction id.
126 -- message_id is the position of the message (logical change record)
127 -- within the transaction.
128 -- local_transaction_id is the id number of the error transaction.
129
130 FUNCTION get_error_message(
131 message_number IN NUMBER,
132 local_transaction_id IN VARCHAR2,
133 destination_queue_name OUT VARCHAR2,
134 execute OUT BOOLEAN) RETURN sys.anydata;
135 -- Returns the logical change record from the error queue for the
136 -- specified message number and deferred transaction id.
137 -- If the logical change record has an enqueue destination then
138 -- that value is returned in destination_queue_name.
139 -- The out variable execute indicates whether the logical change record
140 -- should be executed.
141 -- message_id is the position of the message (logical change record)
142 -- within the transaction.
143 -- local_transaction_id is the id number of the error transaction.
144
145 PROCEDURE delete_error(local_transaction_id IN VARCHAR2);
146 -- Deletes the specified transaction from the error queue.
147 -- local_transaction_id is the id number of the deferred transaction to
148 -- delete.
149
150 PROCEDURE delete_all_errors(apply_name IN VARCHAR2 DEFAULT NULL);
151 -- Deletes all the error transactions for the given apply engine from the
152 -- error queue.
153 -- apply_name is the apply engine which raised the error.
154 -- If apply_name is NULL, the all error transactions, for all apply engines,
155 -- will be deleted.
156
157 PROCEDURE execute_error(
158 local_transaction_id IN VARCHAR2,
159 execute_as_user IN BOOLEAN DEFAULT FALSE,
160 user_procedure IN VARCHAR2 DEFAULT NULL);
161 -- Re-executes the specified transaction in the error queue.
162 -- local_transaction_id is the id number of the deferred transaction to
163 -- re-execute.
164 -- If execute_as_user is TRUE, then the transaction is re-executed in
165 -- the security context of the connected user.
166
167 PROCEDURE execute_all_errors(
168 apply_name IN VARCHAR2 DEFAULT NULL,
169 execute_as_user IN BOOLEAN DEFAULT FALSE);
170 -- Re-executes the error queue transactions for the specified apply
171 -- engine.
172 -- apply_name is the apply engine which raised the error.
173 -- If apply_name is NULL, then then all error transactions, for all apply
174 -- engines, will be re-executed.
175 -- If execute_as_user is TRUE, then the transactions are re-executed in
176 -- the security context of the connected user.
177
178 PROCEDURE set_update_conflict_handler(
179 object_name IN VARCHAR2,
180 method_name IN VARCHAR2,
181 resolution_column IN VARCHAR2,
182 column_list IN dbms_utility.name_array,
183 apply_database_link IN VARCHAR2 DEFAULT NULL);
184 -- Adds a conflict handler to resolve update conflicts
185 -- object_name - the schema and name of the table, specified as
186 -- schema_name.object_name, for which the update conflict handler is being
187 -- added. The schema will default to the current user if one isn't
188 -- specified.
189 -- method_name - type of update conflict handler to create/invoke. Users can
190 -- specify one of the built-in methouds (MINIMUM, MAXIMUM, OVERWRITE,
191 -- DISCARD), or USER FUNCTION for a user-defined method.
192 -- resolution_column - name of the column used to resolve the conflict
193 -- column_list - list of columns whose values will be updated in case
194 -- of a conflict.
195
196 PROCEDURE create_apply(
197 queue_name IN VARCHAR2,
198 apply_name IN VARCHAR2,
199 rule_set_name IN VARCHAR2 DEFAULT NULL,
200 message_handler IN VARCHAR2 DEFAULT NULL,
201 ddl_handler IN VARCHAR2 DEFAULT NULL,
202 apply_user IN VARCHAR2 DEFAULT NULL,
203 apply_database_link IN VARCHAR2 DEFAULT NULL,
204 apply_tag IN RAW DEFAULT '00',
205 apply_captured IN BOOLEAN DEFAULT FALSE,
206 precommit_handler IN VARCHAR2 DEFAULT NULL,
207 negative_rule_set_name IN VARCHAR2 DEFAULT NULL,
208 source_database IN VARCHAR2 DEFAULT NULL);
209
210 PROCEDURE alter_apply(
211 apply_name IN VARCHAR2,
212 rule_set_name IN VARCHAR2 DEFAULT NULL,
213 remove_rule_set IN BOOLEAN DEFAULT FALSE,
214 message_handler IN VARCHAR2 DEFAULT NULL,
215 remove_message_handler IN BOOLEAN DEFAULT FALSE,
216 ddl_handler IN VARCHAR2 DEFAULT NULL,
217 remove_ddl_handler IN BOOLEAN DEFAULT FALSE,
218 apply_user IN VARCHAR2 DEFAULT NULL,
219 apply_tag IN RAW DEFAULT NULL,
220 remove_apply_tag IN BOOLEAN DEFAULT FALSE,
221 precommit_handler IN VARCHAR2 DEFAULT NULL,
222 remove_precommit_handler IN BOOLEAN DEFAULT FALSE,
223 negative_rule_set_name IN VARCHAR2 DEFAULT NULL,
224 remove_negative_rule_set IN BOOLEAN DEFAULT FALSE);
225
226 PROCEDURE drop_apply(apply_name IN VARCHAR2,
227 drop_unused_rule_sets IN BOOLEAN DEFAULT FALSE);
228
229 -- Records the specified instantiation SCN for the table given by the
230 -- source_object_name parameter from the source_database_name database
231 -- if instantiation_scn is not NULL. Remove the instantiation SCN
232 -- if instantiation_scn is NULL.
233 --
234 -- Argument source_root_name is defaulted to source_database_name if it is
235 -- null.
236
237 PROCEDURE set_table_instantiation_scn(source_object_name IN VARCHAR2,
238 source_database_name IN VARCHAR2,
239 instantiation_scn IN NUMBER,
240 apply_database_link IN VARCHAR2 DEFAULT NULL,
241 source_root_name IN VARCHAR2 DEFAULT NULL);
242
243 -- Records the specified instantiation SCN for the schema
244 -- from the source_database_name database if instantiation_scn is not NULL.
245 -- Remove the instantiation SCN if instantiation_scn is NULL.
246 -- If recursive = true then for all tables in this schema at the source db
247 -- set the scn.
248 --
249 -- Argument source_root_name is defaulted to source_database_name if it is
250 -- null.
251 PROCEDURE set_schema_instantiation_scn(source_schema_name IN VARCHAR2,
252 source_database_name IN VARCHAR2,
253 instantiation_scn IN NUMBER,
254 apply_database_link IN VARCHAR2 DEFAULT NULL,
255 recursive IN BOOLEAN DEFAULT FALSE,
256 source_root_name IN VARCHAR2 DEFAULT NULL);
257
258 -- Records the specified instantiation SCN for the source_database_name
259 -- database if instantiation_scn is not NULL
260 -- Remove the instantiation SCN if instantiation_scn is NULL
261 -- If recursive = true then for all tables and schemas at the source db
262 -- set the scn.
263 --
264 -- Argument source_root_name and source_database_name cannot be null at the
265 -- time. If source_database_name is null then this procedure will set or
266 -- remove the instantiation SCN for all tables in the specified source root
267 -- depending on the instantiation_scn value.
268 PROCEDURE set_global_instantiation_scn(source_database_name IN VARCHAR2,
269 instantiation_scn IN NUMBER,
270 apply_database_link IN VARCHAR2 DEFAULT NULL,
271 recursive IN BOOLEAN DEFAULT FALSE,
272 source_root_name IN VARCHAR2 DEFAULT NULL);
273
274 -- Sets destination_queue_name as the queue where events satisfying the
275 -- rule specified by rule_name will be enqueued. If destination_queue_name
276 -- is NULL then any existing queue name for the rule will be removed from
277 -- the rule's action context.
278 PROCEDURE set_enqueue_destination(rule_name IN VARCHAR2,
279 destination_queue_name IN VARCHAR2);
280
281 -- Sets APPLY$_EXECUTE in the rule's action context to 'NO' when
282 -- execute = false. Remove the variable if execute = true.
283 PROCEDURE set_execute(rule_name IN VARCHAR2, execute IN BOOLEAN);
284
285 PROCEDURE compare_old_values (
286 object_name IN VARCHAR2,
287 column_list IN VARCHAR2,
288 operation IN VARCHAR2 DEFAULT 'UPDATE',
289 compare IN BOOLEAN DEFAULT TRUE,
290 apply_database_link IN VARCHAR2 DEFAULT NULL);
291 -- Indicates whether or not to compare old column with the current column
292 -- values for deletes or updates when they are sent.
293 -- object_name - the schema and name of the table, specified as
294 -- schema_name.object_name, for which the columns are being specified.
295 -- The schema will default to the current user if one isn't specified.
296 -- column_list - comma seperated list of columns. If '*' is specified, then
297 -- it includes all non-key columns.
298 -- operation - 'DELETE' or 'UPDATE'. '*' implies both 'DELETE' and 'UPDATE'
299 -- compare - TRUE -> old values are compared
300 -- FALSE -> old values are not compared
301 -- apply_database_link - if remote apply, then name of database link
302 -- pointing to the remote database
303
304 PROCEDURE compare_old_values (
305 object_name IN VARCHAR2,
306 column_table IN DBMS_UTILITY.LNAME_ARRAY,
307 operation IN VARCHAR2 DEFAULT 'UPDATE',
308 compare IN BOOLEAN DEFAULT TRUE,
309 apply_database_link IN VARCHAR2 DEFAULT NULL);
310 -- Indicates whether or not to compare old column with the current column
311 -- values for deletes or updates when they are sent.
312 -- object_name - the schema and name of the table, specified as
313 -- schema_name.object_name, for which the columns are being specified.
314 -- The schema will default to the current user if one isn't specified.
315 -- column_table - PL/SQL table of columns. The table must be dense and
316 -- need not be null terminatd.
317 -- operation - 'DELETE' or 'UPDATE'. '*' implies both 'DELETE' and 'UPDATE'
318 -- compare - TRUE -> old values are compared
319 -- FALSE -> old values are not compared
320 -- apply_database_link - if remote apply, then name of database link
321 -- pointing to the remote database
322
323 PROCEDURE set_value_dependency (
324 dependency_name IN VARCHAR2,
325 object_name IN VARCHAR2,
326 attribute_table IN dbms_utility.name_array);
327
328 -- Adds a set of columns to a virtual constraint.
329 -- If the constraint_name is null, an error is raised.
330 -- If the constraint_name is unknown, a new virtual constraint is created.
331 -- If the constraint_name already contains columns for object_name,
332 -- the existing column list will be replaced. If the new column list is
333 -- empty, the constraint_name for this object will be deleted.
334 -- If object_name is null, all the information about this constraint
335 -- will be removed. The column_table parameter is ignored.
336 --
337 -- Default_values allows the user to define a default value for each
338 -- column of the column list. The default value is used for dependency
339 -- computation if the column value is not available in the LCR.
340 --
341 -- Ignore_values allows the user to define a column ignore value. When
342 -- the ignore values are set, and they match the value of the key in
343 -- a given LCR, dependencies for the column of the current LCR are
344 -- not computed.
345 --
346 -- All PL/SQL table is 1-based
347 -- An error will be raised if the count of the values (default/ignore)
348 -- do not match with the column list.
349
350 PROCEDURE set_value_dependency (
351 dependency_name IN VARCHAR2,
352 object_name IN VARCHAR2,
353 attribute_list IN VARCHAR2);
354 -- Same as above except the attribute list is coma separated
355
356 PROCEDURE create_object_dependency (
357 object_name IN VARCHAR2,
358 parent_object_name IN VARCHAR2);
359 -- Allows users to define object level dependencies. All transactions
360 -- that operate on the (child) object depend on the last transaction that
361 -- operated on the parent object.
362 -- This procedure does not check if the user defined, Object Ladder is
363 -- circular.
364
365 PROCEDURE drop_object_dependency (
366 object_name IN VARCHAR2,
367 parent_object_name IN VARCHAR2);
368 -- This procedure allows the Stream application developer to drop defined
369 -- parent child relationships between destination objects.
370
371
372 PROCEDURE add_stmt_handler(
373 object_name IN VARCHAR2,
374 operation_name IN VARCHAR2,
375 handler_name IN VARCHAR2,
376 statement IN CLOB,
377 apply_name IN VARCHAR2 DEFAULT NULL,
378 comment IN VARCHAR2 DEFAULT NULL);
379 -- Create a stmt handler with a user-specified statement and add it to apply.
380
381 PROCEDURE add_stmt_handler(
382 object_name IN VARCHAR2,
383 operation_name IN VARCHAR2,
384 handler_name IN VARCHAR2,
385 apply_name IN VARCHAR2 DEFAULT NULL);
386 -- Adds a lcr-processing stmt handler to apply.
387
388 PROCEDURE remove_stmt_handler(
389 object_name IN VARCHAR2,
390 operation_name IN VARCHAR2,
391 handler_name IN VARCHAR2,
392 apply_name IN VARCHAR2 DEFAULT NULL);
393 -- Removes a lcr-processing stmt handler from apply.
394
395 --
396 -- This procedure sets a change handler for the specified apply and operation
397 --
398 -- Parameters:
399 --
400 -- change_table_name : Name of change table
401 -- source_table_name : Name of source table
402 -- capture_values : 'OLD', 'NEW', or '*'(BOTH)
403 -- apply_name : Name of apply
404 -- operation_name : Name of DML operation
405 -- change_handler_name : Nmae of change handler to set to the apply process.
406 -- If NULL, remove all change handlers from the
407 -- specified apply and operation.
408 --
409 PROCEDURE set_change_handler(
410 change_table_name VARCHAR2,
411 source_table_name VARCHAR2,
412 capture_values VARCHAR2,
413 apply_name VARCHAR2,
414 operation_name VARCHAR2,
415 change_handler_name VARCHAR2 DEFAULT NULL);
416
417 ----------------------------------------------------------------------------
418 -- This procedure sets a dml conflict handler for the specified apply_name,
419 -- target object, source object, operation_name, conflict_type,
420 -- and method_name
421 --
422 -- Parameters:
423 -- apply_name (IN) name of the apply - can NOT be NULL
424 -- conflict_handler_name (IN) name to identify the conflict handler
425 -- object (IN) name of the target object: schema.name
426 -- operation_name (IN) 'INSERT', 'UPDATE', 'DELETE'
427 -- conflict_type (IN) 'ROW_EXISTS', 'ROW_MISSING'
428 -- method_name (IN) 'RECORD', 'IGNORE', 'OVERWRITE', 'MAXIMUM',
429 -- 'MINIMUM', 'DELTA'
430 -- column_list (IN) comma separated list of columns in the column
431 -- group. Specify '*' for the default column
432 -- group, which includes all columns that are not
433 -- already included in another column list for
434 -- the given apply_name, object, source_object,
435 -- operation_name, and conflict type.
436 -- resolution_column (IN) name of the column used to resolve the
437 -- conflict for resolution method MAXIMUM and
438 -- MINIMUM
439 -- source_object (IN) name of the source object: schema.name
440 PROCEDURE set_dml_conflict_handler(
444 operation_name IN VARCHAR2 DEFAULT NULL,
441 apply_name IN VARCHAR2,
442 conflict_handler_name IN VARCHAR2,
443 object IN VARCHAR2 DEFAULT NULL,
445 conflict_type IN VARCHAR2 DEFAULT NULL,
446 method_name IN VARCHAR2 DEFAULT NULL,
447 column_list IN VARCHAR2 DEFAULT NULL,
448 resolution_column IN VARCHAR2 DEFAULT NULL,
449 source_object IN VARCHAR2 DEFAULT NULL);
450
451 ----------------------------------------------------------------------------
452 -- This procedure sets a dml conflict handler for the specified apply_name,
453 -- target object, source object, operation_name, conflict_type,
454 -- and method_name
455 --
456 -- Parameters:
457 -- apply_name (IN) name of the apply - can NOT be NULL
458 -- conflict_handler_name (IN) name to identify the conflict handler
459 -- object (IN) name of the target object: schema.name
460 -- operation_name (IN) 'INSERT', 'UPDATE', 'DELETE'
461 -- conflict_type (IN) 'ROW_EXISTS', 'ROW_MISSING'
462 -- method_name (IN) 'RECORD', 'IGNORE', 'OVERWRITE', 'MAXIMUM',
463 -- 'MINIMUM', 'DELTA'
464 -- column_table (IN) table of columns in the column group.
465 -- resolution_column (IN) name of the column used to resolve the
466 -- conflict for resolution method MAXIMUM and
467 -- MINIMUM
468 -- source_object (IN) name of the source object: schema.name
469 PROCEDURE set_dml_conflict_handler(
470 apply_name IN VARCHAR2,
471 conflict_handler_name IN VARCHAR2,
472 object IN VARCHAR2 DEFAULT NULL,
473 operation_name IN VARCHAR2 DEFAULT NULL,
474 conflict_type IN VARCHAR2 DEFAULT NULL,
475 method_name IN VARCHAR2 DEFAULT NULL,
476 column_table IN dbms_utility.lname_array,
477 resolution_column IN VARCHAR2 DEFAULT NULL,
478 source_object IN VARCHAR2 DEFAULT NULL);
479
480 ----------------------------------------------------------------------------
481 -- This procedure sets a collision handler for a given apply and src/tgt pair
482 --
483 -- Parameters:
484 -- apply_name (IN) name of the apply
485 -- enable (IN) enable collision handling?
486 -- object (IN) name of the target object: schema.name
487 -- source_object (IN) name of the source object: schema.name
488 PROCEDURE handle_collisions(apply_name IN VARCHAR2,
489 enable IN BOOLEAN,
490 object IN VARCHAR2,
491 source_object IN VARCHAR2 DEFAULT NULL);
492
493 ----------------------------------------------------------------------------
494 -- This procedure sets a reperror handler for an apply, src table, tgt table,
495 -- and error number
496 --
497 -- Parameters:
498 -- apply_name (IN) name of the apply
499 -- object (IN) name of the target object: schema.name
500 -- error_number (IN) error number. 0 for all errors.
501 -- method (IN) error handling method
502 -- source_object (IN) name of the source object: schema.name
503 -- max_retries (IN) number of times to retry
504 -- delay_csecs (IN) number of centiseconds to wait between retries
505 PROCEDURE set_reperror_handler(apply_name IN VARCHAR2,
506 object IN VARCHAR2,
507 error_number IN NUMBER,
508 method IN VARCHAR2,
509 source_object IN VARCHAR2 DEFAULT NULL,
510 max_retries IN NUMBER DEFAULT NULL,
513 END dbms_apply_adm;511 delay_csecs IN NUMBER DEFAULT 6000);
512
[Home] [Help]