1 PACKAGE dbms_defer AUTHID CURRENT_USER AS
2 -------------------
3 -- OVERVIEW
4 --
5 -- This package is the user interface to a replicated transactional
6 -- deferred remote
7 -- procedure call facility. Replicated applications use the calls
8 -- in this interface
9 -- to queue procedure call for later transactional execution at remote nodes.
10 -- These routines are typically called from either after row triggers
11 -- or application
12 -- specified update procedures.
13 ------------
14 -- SECURITY
15 --
16 -- This package is not granted to public because a user can
17 -- potentially steal the rights of the user pushing the deferred rpcs.
18 -- Be careful when granting execute privileges on dbms_defer.
19 -------------
20 -- CONSTANTS
21 --
22 -- The following constants are deprecated, please
23 -- use the corresponding one defined in DBMS_DEFER_QUERY.
24 --
25 arg_type_num CONSTANT NUMBER := dbms_defer_query.arg_type_num;
26 arg_type_char CONSTANT NUMBER := dbms_defer_query.arg_type_char;
27 arg_type_varchar2 CONSTANT NUMBER := dbms_defer_query.arg_type_varchar2;
28 arg_type_date CONSTANT NUMBER := dbms_defer_query.arg_type_date;
29 arg_type_rowid CONSTANT NUMBER := dbms_defer_query.arg_type_rowid;
30 arg_type_raw CONSTANT NUMBER := dbms_defer_query.arg_type_raw;
31 arg_type_blob CONSTANT NUMBER := dbms_defer_query.arg_type_blob;
32 arg_type_clob CONSTANT NUMBER := dbms_defer_query.arg_type_clob;
33 arg_type_bfil CONSTANT NUMBER := dbms_defer_query.arg_type_bfil;
34 arg_type_cfil CONSTANT NUMBER := dbms_defer_query.arg_type_cfil;
35 arg_type_time CONSTANT NUMBER := dbms_defer_query.arg_type_time;
36 arg_type_ttz CONSTANT NUMBER := dbms_defer_query.arg_type_ttz;
37 arg_type_timestamp CONSTANT NUMBER := dbms_defer_query.arg_type_timestamp;
38 arg_type_tstz CONSTANT NUMBER := dbms_defer_query.arg_type_tstz;
39 arg_type_iym CONSTANT NUMBER := dbms_defer_query.arg_type_iym;
40 arg_type_ids CONSTANT NUMBER := dbms_defer_query.arg_type_ids;
41 arg_type_tsltz CONSTANT NUMBER := dbms_defer_query.arg_type_tsltz;
42 -----------
43 -- The following constants are deprecated, please
44 -- use the corresponding one defined in DBMS_DEFER_QUERY.
45 arg_csetid_none CONSTANT NUMBER := dbms_defer_query.arg_csetid_none;
46 arg_form_none CONSTANT NUMBER := dbms_defer_query.arg_form_none;
47 arg_form_implicit CONSTANT NUMBER := dbms_defer_query.arg_form_implicit;
48 arg_form_nchar CONSTANT NUMBER := dbms_defer_query.arg_form_nchar;
49 arg_form_any CONSTANT NUMBER := dbms_defer_query.arg_form_any;
50 -- definition same as dbms_repcat_mas.repcat_status_normal
51 -- (don't want to require repcat to be loaded)
52 repcat_status_normal CONSTANT NUMBER := 0.0;
53 --
54 ---------
55 -- TYPES
56 --
57 -- node list type used for the defer_txn call
58 -- representation is an array (table) indexed from 1 up to a NULL
59 -- entry or NO_DATA_FOUND
60 TYPE node_list_t IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
61 --
62 -----------------
63 -- EXCEPTIONS
64 --
65 -- Parameter type does not match actual type.
66 bad_param_type EXCEPTION;
67 PRAGMA exception_init(bad_param_type, -23325);
68 bad_param_num NUMBER := -23325;
69
70 -- The database is being quiesced.
71 deferred_rpc_quiesce EXCEPTION;
72 PRAGMA exception_init(deferred_rpc_quiesce, -23326);
73 quiesce_num NUMBER := -23326;
74 quiesce_msg VARCHAR(76) := 'the system is being quiesced.';
75
76 -- Generic errors that are not important enough for specific exceptions
77 -- string text will explain them further. These are internal errors.
78 -- message varies.
79 dbms_defererror EXCEPTION;
80 PRAGMA exception_init(dbms_defererror, -23305);
81 deferror_num NUMBER := -23305;
82
83 --
84 -- dbms_defer package detects mal-formed call (e.g. argument count
85 -- miss-match). Message varies.
86 malformedcall EXCEPTION;
87 PRAGMA exception_init(malformedcall, -23304);
88 malformed_num NUMBER := -23304;
89
90 -- generic exceptions that (user-written) deferred procedures
91 -- can raise to indicate
92 -- that the remote update has failed because of data updates by concurrent
93 -- transactions. A deferror table record will be created by the deferred
94 -- rpc executor
95 updateconflict EXCEPTION;
96 PRAGMA exception_init(updateconflict, -23303);
97 conflict_num NUMBER := -23303;
98 conflict_msg VARCHAR(76) := 'Remote update failed due to conflict.';
99
100 -- generic exceptions that (user-written) deferred procedures
101 -- can raise to indicate
102 -- that the remote update has failed because communications failures
103 -- so that a a deferror table record will not be created by the
104 -- deferred rpc
105 -- executor.
106
107 condescfailure EXCEPTION; -- connection description for remote db not found
108 PRAGMA exception_init(condescfailure, -2019);
109 condescfail_num NUMBER := -2019;
110
111 commfailure EXCEPTION;
112 PRAGMA exception_init(commfailure, -23302);
113 commfail_num NUMBER := -23302;
114 commfail_msg VARCHAR(76) :=
115 'Remote update failed due to communication failure';
116
117 noparalprop EXCEPTION;
118 PRAGMA exception_init(noparalprop, -26575);
119 noparalprop_num NUMBER := -26575;
120
121 -- mixed use repcat determined destinations and non-repcat destinations
122 -- in one transaction
123 mixeddest EXCEPTION;
124 PRAGMA exception_init(mixeddest, -23301);
125 mixeddest_num NUMBER := -23301;
126 mixeddest_msg VARCHAR(76) :=
127 'Destinations for transaction not consistently specified';
128
129 -- parameter length exceed deferred rpc limits (2000 char/varchar2,
130 -- 255 raw) in one transaction
131 parameterlength EXCEPTION;
132 PRAGMA exception_init(parameterlength, -23323);
133 paramlen_num NUMBER := -23323;
134 paramlen_msg VARCHAR(76) := 'parameter length exceeds deferred rpc limits';
135
136 -- deferred rpc execution is disabled
137 executiondisabled EXCEPTION;
138 PRAGMA exception_init(executiondisabled, -23354);
139 executiondisabled_num NUMBER := -23354;
140 executiondisabled_msg VARCHAR(76) := 'deferred rpc execution is disabled';
141
142 -- deferred rpc processing is disabled
143 rpcdisabled EXCEPTION;
144 PRAGMA exception_init(rpcdisabled, -23473);
145 rpcdisabled_num NUMBER := -23473;
146 ----------------------
147 -- PROCEDURES
148 --
149 PROCEDURE commit_work(commit_work_comment IN VARCHAR2);
150 -- Perform a transaction commit after checking for well-formed
151 -- deferred RPCs.
152 -- Must be used instead of the commit work sql call for
153 -- transactions deferring RPCS.
154 -- Updates the comment_comment and commit_scn fields in
155 -- the def$_txn table.
156 -- Input parameters:
157 -- commit_work_comment
158 -- Up to fifty characters to describe the transaction
159 -- in the def$_txns
160 -- table and system two-phase commit tables (this latter
161 -- once we figure out
162 -- how to get it in.) Comment is truncated to fifty characters.
163 -- Exceptions
164 -- ORA-23304 (malformedcall) if there is an defer_rpc_arg
165 -- call missing or defer_txn
166 -- was not called for this transaction.
167 --
168 --
169 --
170 -- Transaction and call deferral procedures
171 -- A deferred transaction consist of the following:
172 -- Call to dbms_defer.transaction (this is optional, the first call to
173 -- dbms_defer.call will call transaction)
174 -- one or more complete calls, each of which consists of
175 -- Call to dbms_defer.call
176 -- zero of more calls (depending on arg_count in
177 -- dbms_defer.call) to dbms_defer.arg_*
178 -- commit or call to commit_work
179 --
180 -- DESTINATION SPECIFICATION
181 -- Destinations can be specified in several ways
182 -- A) All deferred procedures are in repcat and the default list is
183 -- NOT specified in the transaction call.
184 -- OR
185 -- B) destinations are specified without repcat using the following order
186 -- of precedence
187 -- 1) list specified in the nodes parameter to dbms_defer.call
188 -- 2) list specified in the nodes parameter to dbms_defer.transaction
189 -- 3) list specified in defdefaultdest table.
190 -- The mixeddest exception is raised if an attempt to mix destinations
191 -- modes is detected.
192 --
193 PROCEDURE transaction;
194 PROCEDURE transaction(nodes IN node_list_t);
195 -- Mark a transaction as deferred (as containing deferred RPCs )
196 -- This call is optional. The first call to dbms_defer.call
197 -- in a transaction will call
198 -- deftxn (with no arguments) if it has not been previously called.
199 -- Input parameters are optional, and if they are not
200 -- specified the destination
201 -- list is taken from the system defaults stored in the
202 -- def$_defaultdest table and
203 -- maintained by the dbms_defer_sys.add_default_node and
204 -- dbms_defer_sys.delete_default_node calls
205 -- Input parameters:
206 -- nodes
207 -- Table containing a list of nodes (dblink) to propagate the
208 -- deferred calls of the
209 -- transaction to. Indexed from 1 until a NULL entry is
210 -- found or NO_DATA_FOUND is raised.
211 -- Case insensitive comparison
212 -- used for node lists.
213 -- Use of this parameter overrides distribution lists as
214 -- specified in repcat.
215 -- Exceptions
216 -- ORA-23304 (malformedcall) if the previous transaction
217 -- not correctly formed
218 -- or terminated
219 -- ORA-23319 Parameter value is not appropriate
220 -- ORA-23352 Raised by dbms_defer.call if the node
221 -- list contains duplicates
222 ----
223
224 PROCEDURE call( schema_name IN VARCHAR2,
225 package_name IN VARCHAR2,
226 proc_name IN VARCHAR2,
227 arg_count IN NATURAL,
228 group_name IN VARCHAR2 := '');
229
230 PROCEDURE call( schema_name IN VARCHAR2,
231 package_name IN VARCHAR2,
232 proc_name IN VARCHAR2,
233 arg_count IN NATURAL,
234 nodes IN node_list_t);
235 -- Defer a remote procedure call. Automatically call
236 -- deftxn if this is the first
237 -- call call of a transaction.
238 -- Input parameters:
239 -- schema_name
240 -- Name of the schema containing the remote procedure. For
241 -- compatibility with future compile-time checking only string
242 -- constants should be used.
243 -- package_name
244 -- Name of the package containing the remote procedure. For
245 -- compatibility with future compile-time checking only string
246 -- constants should be used.
247 -- proc_name
248 -- Name of the remote procedure to call.
249 -- For compatibility with
250 -- future syntactic integration
251 -- and compile-time checking only string constants should be used.
252 -- arg_count
253 -- Number of parameters to the procedure. This must
254 -- exactly match the number of
255 -- defrpcarg_* calls immediately following the dbms_defer.call call.
256 -- group_name
257 -- Reserved for internal use
258 -- nodes
259 -- Optional table containing a list of nodes to propagate the
260 -- deferred call to.
261 -- Indexed from 1 until a NULL entry is
262 -- found or NO_DATA_FOUND is raised.
263 -- Case insensitive comparison
264 -- used for node lists.
265 -- If not specified, the destination list is determined by the
266 -- list passed to the transaction procedure, or the system defaults,
267 -- Use of this parameter in any deferred call invalidate the use of
268 -- the use of repcat to determine distribution lists in any
269 -- calls for a transaction.
270 -- Exceptions --
271 -- Exceptions
272 -- ORA-23304 (malformedcall) if the previous call not
273 -- correctly formed (number of
274 -- defrpcarg_* call not matched to arg_count).
275 -- ORA-23319 Parameter value is not appropriate
276 -- ORA-23352 If the destination list (specified by nodes or by a previous
277 -- dbms_defer.transaction call contains a duplicate.
278 ----
279
280 PROCEDURE number_arg(arg IN nUMBER);
281 -- Queue a number parameter value for a deferred call.
282 -- Input parameter:
283 -- arg
284 -- The number value of the parameter to the call
285 -- previously deferred with a
286 -- dbms_defer.call call.
287 -- Exceptions: none.
288 --------
289
290 PROCEDURE date_arg(arg IN DATE);
291 -- Queue a date parameter value for a deferred call.
292 -- Input parameter:
293 -- arg
294 -- The date value of the parameter to the call previously
295 -- deferred with a
296 -- dbms_defer.call call.
297 -- Exceptions: none.
298 --------
299
300 PROCEDURE varchar2_arg(arg IN VARCHAR2);
301 -- Queue a varchar2 parameter value for a deferred call.
302 -- Input parameter:
303 -- arg
304 -- The varchar2 value of the parameter to the call
305 -- previously deferred with a
306 -- dbms_defer.call call. The length of arg is limited to 2000.
307 -- Exceptions:
308 -- whatever error sql gives if arg exceeds 2000 characters.
309
310 PROCEDURE nvarchar2_arg(arg IN NVARCHAR2);
311 -- Queue an nvarchar2 parameter value for a deferred call.
312 -- Input parameter:
313 -- arg
314 -- The nvarchar2 value of the parameter to the call
315 -- previously deferred with a
316 -- dbms_defer.call call. The length of arg is limited to 2000.
317 -- Exceptions:
318 -- whatever error sql gives if arg exceeds 2000 characters.
319
320 PROCEDURE any_varchar2_arg(arg IN VARCHAR2 CHARACTER SET ANY_CS);
321 -- Queue a varchar2 parameter value for a deferred call.
322 -- Input parameter:
323 -- arg
324 -- The varchar2 value of the parameter to the call
325 -- previously deferred with a
326 -- dbms_defer.call call. The length of arg is limited to 2000.
327 -- Exceptions:
328 -- whatever error sql gives if arg exceeds 2000 characters.
329
330 PROCEDURE char_arg(arg IN CHAR);
331 -- Queue a char parameter value for a deferred call.
332 -- Input parameter:
333 -- arg
334 -- The char value of the parameter to the call previously
335 -- deferred with a
336 -- dbms_defer.call call. The length of arg is limited to 2000.
337 -- Exceptions:
338 -- whatever error sql gives if arg exceeds 2000 characters.
339
340 PROCEDURE nchar_arg(arg IN NCHAR);
341 -- Queue an nchar parameter value for a deferred call.
342 -- Input parameter:
343 -- arg
344 -- The nchar value of the parameter to the call previously
345 -- deferred with a
346 -- dbms_defer.call call. The length of arg is limited to 2000.
347 -- Exceptions:
348 -- whatever error sql gives if arg exceeds 2000 characters.
349
350 PROCEDURE any_char_arg(arg IN CHAR CHARACTER SET ANY_CS);
351 -- Queue a char parameter value for a deferred call.
352 -- Input parameter:
353 -- arg
354 -- The char value of the parameter to the call previously
355 -- deferred with a
356 -- dbms_defer.call call. The length of arg is limited to 2000.
357 -- Exceptions:
358 -- whatever error sql gives if arg exceeds 2000 characters.
359
360 ---------------------
361 -- rowids can not be
362 -- used on different nodes. It might be reasonable to use a
363 -- rid in a deferred call
364 -- to a local node, but be careful.
365 PROCEDURE rowid_arg(arg IN ROWID);
369 -- The rowid value of the parameter to the call
366 -- Queue a rowid parameter value for a deferred call.
367 -- Input parameter:
368 -- arg
370 -- previously deferred with a
371 -- dbms_defer.call call.
372 -- Exceptions:
373 -- dbms_deferError
374 --------
375
376 -- The following calls will not be supported until dbms_sql
377 -- supports
378 --
379 PROCEDURE raw_arg(arg IN raw);
380 -- Queue a rowid parameter value for a deferred call.
381 -- Input parameter:
382 -- arg
383 -- The raw value of the parameter to the call
384 -- previously deferred with a
385 -- dbms_defer.call call.
386 -- Exceptions:
387 -- dbms_deferError
388 --------
389
390 PROCEDURE blob_arg(arg IN BLOB);
391 -- Queue a BLOB for a deferred call.
392 -- Input parameter:
393 -- arg
394 -- The value of the parameter to the call previously
395 -- deferred with a dbms_defer.call call.
396 --------
397
398 PROCEDURE clob_arg(arg IN CLOB);
399 -- Queue a CLOB for a deferred call.
400 -- Input parameter:
401 -- arg
402 -- The value of the parameter to the call previously
403 -- deferred with a dbms_defer.call call.
404 --------
405
406 PROCEDURE any_clob_arg(arg IN CLOB CHARACTER SET ANY_CS);
407 -- Queue a CLOB for a deferred call.
408 -- Input parameter:
409 -- arg
410 -- The value of the parameter to the call previously
411 -- deferred with a dbms_defer.call call.
412 --------
413
414 PROCEDURE nclob_arg(arg IN NCLOB);
415 -- Queue an NCLOB for a deferred call.
416 -- Input parameter:
417 -- arg
418 -- The value of the parameter to the call previously
419 -- deferred with a dbms_defer.call call.
420 --------
421
422 PROCEDURE time_arg(arg IN TIME_UNCONSTRAINED);
423 -- Queue a time parameter value for a deferred call.
424 -- Input parameter:
425 -- arg
426 -- The time value of the parameter to the call previously
427 -- deferred with a
428 -- dbms_defer.call call.
429 -- Exceptions: none.
430 --------
431
432 PROCEDURE timestamp_arg(arg IN TIMESTAMP_UNCONSTRAINED);
433 -- Queue a timestamp parameter value for a deferred call.
434 -- Input parameter:
435 -- arg
436 -- The timestamp value of the parameter to the call previously
437 -- deferred with a
438 -- dbms_defer.call call.
439 -- Exceptions: none.
440 --------
441
442 PROCEDURE ttz_arg(arg IN TIME_TZ_UNCONSTRAINED);
443 -- Queue a time with time zone parameter value for a deferred call.
444 -- Input parameter:
445 -- arg
446 -- The time with time zone value of the parameter to the call previously
447 -- deferred with a
448 -- dbms_defer.call call.
449 -- Exceptions: none.
450 --------
451
452 PROCEDURE tstz_arg(arg IN TIMESTAMP_TZ_UNCONSTRAINED);
453 -- Queue a timestamp with time zone parameter value for a deferred call.
454 -- Input parameter:
455 -- arg
456 -- The timestamp with time zone value of the parameter to the call
457 -- previously deferred with a
458 -- dbms_defer.call call.
459 -- Exceptions: none.
460 --------
461
462 PROCEDURE tsltz_arg(arg IN TIMESTAMP_LTZ_UNCONSTRAINED);
463 -- Queue a timestamp with local timezone parameter value for a deferred call.
464 -- Input parameter:
465 -- arg
466 -- The timestamp with local time zone value of the parameter to the call
467 -- previously deferred with a
468 -- dbms_defer.call call.
469 -- Exceptions: none.
470 --------
471
472 PROCEDURE iym_arg(arg IN YMINTERVAL_UNCONSTRAINED);
473 -- Queue a interval year to month parameter value for a deferred call.
474 -- Input parameter:
475 -- arg
476 -- The interval year to month value of the parameter to the call
477 -- previously deferred with a
478 -- dbms_defer.call call.
479 -- Exceptions: none.
480 --------
481
482 PROCEDURE ids_arg(arg IN DSINTERVAL_UNCONSTRAINED);
483 -- Queue a interval date to second parameter value for a deferred call.
484 -- Input parameter:
485 -- arg
486 -- The interval date to second value of the parameter to the call
487 -- previously deferred with a
488 -- dbms_defer.call call.
489 -- Exceptions: none.
490 --------
491
492 -- PROCEDURE bfile_arg(arg IN BFILE);
493 -- Queue the contents of a binary file for a deferred call. The contents
494 -- are interpreted and stored as a BLOB.
495 -- Input parameter:
496 -- arg
497 -- The value of the parameter to the call previously
498 -- deferred with a dbms_defer.call call.
499 --------
500
501 -- PROCEDURE cfile_arg(arg IN CFILE);
502 -- Queue the contents of a binary file for a deferred call. The contents
503 -- are interpreted and stored as a CLOB.
504 -- Input parameter:
505 -- arg
506 -- The value of the parameter to the call previously
507 -- deferred with a dbms_defer.call call.
508 --------
509
510 PROCEDURE anydata_arg(arg IN Sys.AnyData);
511 -- Queue a anydata for the parameter value for a deferred call.
515 -- previously deferred with a dbms_defer.call call.
512 -- Input parameter:
513 -- arg
514 -- The user data
516 -- The supported types are Collections and Object types.
517 -- It does not support REF types, Opaque types, AnyData, AnyType and
518 -- AnyDataSet.
519 --------
520
521 --
522 -- Public procs for parallel prop txn log records
523 --
524
525 PROCEDURE record_transaction(origin_site_p IN VARCHAR2,
526 origin_dblink_p IN VARCHAR2,
527 transaction_number_p IN NUMBER);
528 -- Record transaction in def$_origin as current connected user
529 --
530 -- Inputs:
531 -- origin_site_p
532 -- fully qualified global name of the pushing site
533 -- origin_dblink_p
534 -- dblink used by the pushing site (may have a qualifier)
535 -- transaction_number_p
536 -- trans seq number to record
537 --------
538 --------
539
540 PROCEDURE purge_transaction_log(origin_site_p IN VARCHAR2,
541 origin_dblink_p IN VARCHAR2,
542 transaction_number_p IN NUMBER);
543 -- Purge from def$_origin all txns with given parameters and current
544 -- connected user id
545 -- Inputs:
546 -- origin_site_p
547 -- fully qualified global name of the pushing site
548 -- origin_dblink_p
549 -- dblink used by the pushing site (may have a qualifier)
550 -- transaction_number_p
551 -- least transaction seq number to retain
552 --------
553 --------
554
555 --
556 -- Public procs for parallel prop recovery set retrieval
557 --
558
559 PROCEDURE get_txn_log_runs(
560 origin_site IN VARCHAR2,
561 origin_dblink IN VARCHAR2,
562 in_tran_seq IN NUMBER);
563 -- Initialize for retrieving run-encoded set of txn sequence numbers
564 -- from destination site
565 -- Input parameters:
566 -- origin_site
567 -- fully qualified global name of the pushing site
568 -- origin_dblink
569 -- dblink used by the pushing site (may have a qualifier)
570 -- in_tran_seq
571 -- initial trans seq number to return (all earlier seqs
572 -- are known by pushing site already)
573 --
574 -- It's obsolete in 8.2[+].
575 --------
576 --------
577
578 PROCEDURE get_next_txn_log_run(
579 run_seq OUT NUMBER,
580 run_len OUT NUMBER,
581 scn_first OUT NUMBER,
582 id_first OUT VARCHAR2,
583 scn_last OUT NUMBER,
584 id_last OUT VARCHAR2);
585 -- Retrieve next run of applied txn sequence numbers
586 -- as specified by prior call to get_txn_log_runs
587 -- Runs are returned in increasing order by seq
588 -- which is also increasing order by commit number
589 -- (i.e. <scn, id>)
590 -- Outputs:
591 -- run_seq, run_len
592 -- returned run bounds; an empty (0-length) run indicates
593 -- end-of-data
594 -- scn_first, id_first
595 -- commit number of first txn in run
596 -- scn_last, id_last
597 -- commit number of last txn in run
598 --
599 -- It's obsolete in 8.2[+].
600 --------
601 --------
602
603 END dbms_defer;
[Home] [Help]