1 PACKAGE dbms_rectifier_diff AUTHID CURRENT_USER AS
2
3 -------------
4 -- TYPE DEFINITIONS
5 --
6
7 -------------
8 -- EXCEPTIONS
9 --
10
11 nosuchsite EXCEPTION;
12 PRAGMA exception_init(nosuchsite, -23365);
13 nosuchsite_num NUMBER := -23365;
14
15 badnumber EXCEPTION;
16 PRAGMA exception_Init(badnumber, -23366);
17 badnumber_num NUMBER := -23366;
18
19 missingprimarykey EXCEPTION;
20 PRAGMA exception_Init(missingprimarykey, -23367);
21 missingprimarykey_num NUMBER := -23367;
22
23 badname EXCEPTION;
24 PRAGMA exception_Init(badname, -23368);
25 badname_num NUMBER := -23368;
26
27 cannotbenull EXCEPTION;
28 PRAGMA exception_Init(cannotbenull, -23369);
29 cannotbenull_num NUMBER := -23369;
30
31 notshapeequivalent EXCEPTION;
32 PRAGMA exception_Init(notshapeequivalent, -23370);
33 notshapeequivalent_num NUMBER := -23370;
34
35 unknowncolumn EXCEPTION;
36 PRAGMA exception_Init(unknowncolumn, -23371);
37 unknowncolumn_num NUMBER := -23371;
38
39 unsupportedtype EXCEPTION;
40 PRAGMA exception_Init(unsupportedtype, -23372);
41 unsupportedtype_num NUMBER := -23372;
42
43 badmrname EXCEPTION;
44 PRAGMA exception_Init(badmrname, -23377);
45 badmrname_num NUMBER := -23377;
46
47 -------------
48 -- PROCEDURES
49 --
50
51 -- Requires: The caller must have already created the tables named
52 -- "missing_rows_oname1" and "missing_rows_oname2" in schema
53 -- "missing_rows_sname" at the site "missing_rows_site" with the
54 -- correct shapes. There must be no white space before or after
55 -- commas in "column_list."
56 -- Effects: This routine compares the fragment defined by "column_list"
57 -- and the "where_clause" of the table named "sname1.oname1" (truth) at site
58 -- "reference_site" with the fragment defined by "column_list" and
59 -- "where_clause" of the table named "sname2.oname2" at site "comparison_site."
60 -- It determines what is different and records this information in the
61 -- pair of tables named "missing_rows_oname1" and "missing_rows_oname2"
62 -- in schema "missing_rows_sname." If the fragments are exactly equivalent,
63 -- then the tables "missing_rows_oname1" and "missing_rows_oname2" are
64 -- unchanged. If the fragments are not exactly equivalent, then those
65 -- rows in "sname1.oname1" at the "reference_site" that are missing
66 -- from "sname2.oname2" at the "comparison_site," and the rows in the
67 -- table at "comparision_site" that are missing from the table at
68 -- "reference_site" are inserted into the tables "missing_rows_oname1" and
69 -- "missing_rows_oname2." The routine commits those rows that have been
70 -- inserted into the pair of missing rows tables whenever "commit_row"
71 -- number of rows have been inserted. Raises exception badnumber if
72 -- "commit_rows" is less than 1. If "sname," "oname," "missing_rows_sname,"
73 -- or "missing_rows_oname" is NULL or `' (empty string), raise exception
74 -- badname. Raise badmrname if arguments "missing_rows_oname1" and "oname1"
75 -- have the same name; the "oname1" truth table cannot be used as one
76 -- of the missing rows tables. Raises exception unknowncolumn if any
77 -- column specified in "column_list" is missing from table
78 -- "sname1.oname1." or from table "sname2.oname2."
79 -- Raises exception missingobject if tables named "oname1,"
80 -- "oname2," "missing_rows_oname1," or "missing_rows_oname2" do not exist.
81 -- "max_missing" is an integer that refers to the maximum number of rows
82 -- that should be inserted into the "missing_rows_oname" table. If more
83 -- than "max_missing" number of rows is missing, that many rows will be
84 -- inserted into the "missing_rows_oname," and the routine then returns
85 -- normally without determining whether more rows are missing; this
86 -- argument is useful in the cases that the fragments are so different that
87 -- the missing rows table will have too many entries and there's no
88 -- point in continuing. Raises exception badnumber if "max_missing" is
89 -- less than 1, or NULL. Raises exception cannotbenull if "max_missing"
90 -- is NULL. Raises exception unsupportedtype if any column data type of
91 -- any column in in "column_list" is a long or long raw.
92 -- The "where_clause" is used as a predicate to restrict the query
93 -- space; if `' (the empty string), then every row is selected, else,
94 -- every row that satisfies the predicate is selected. Raises exception
95 -- notshapeequivalent if "sname1.oname1" at site "reference_site" is not
96 -- shape equivalent to "sname2.oname2" at site "comparison_site" with
97 -- respect to the specified columns in "column_list."
98 -- Duplicate rows are treated as one row under set semantics, and
99 -- thus, if they are missing, will appear exactly once in "missing_rows_oname1"
100 -- and "missing_rows_oname2" tables. (However, when the table at the
101 -- comparison site is rectified, duplicate rows are handled correctly.)
102 -- Routine raises exception nosuchsite if the "reference_site,"
103 -- "comparison_site," or "missing_rows_site" does not name a site.
104 -- Raises exception dbsm_repcat.commfailure if the remote site is inaccessible.
105 -- Raises exception missingprimarykey if "column_list" does not contain
106 -- the table's primary keys (if multiple columns constitute the primary
107 -- key, then all columns must be specified in "column_list").
108 -- Two successive invocations of this routine on the same pair of tables
109 -- is equivalent to one invocation (without cleaning out the missing rows
110 -- tables manually); the missing rows tables remain unchanged.
111 -- If the replication option is not installed, then raise exception
112 -- dbms_repcat.norepoption.
113 -- The table "missing_rows_oname1" must have only the column names given in
114 -- "column_list" and the associated data types, which defines a fragment. The
115 -- table "missing_rows_oname2" must have three columns with the associated
116 -- datatypes: "present" (type varchar2(128))for the name of the site where
117 -- the row appeared, "absent" (type varchar2(128)) for the name of the site
118 -- where the row does not appear, and "r_id" (type rowid) to hold the rowid
119 -- of the corresponding row in "missing_rows_oname1."
120 --
121 -- Arguments:
122 -- *sname1: schema name at reference_site; need not be the fully qualified
123 -- canonical name
124 -- *oname1: table name at reference_site
125 -- *reference_site: site name; need not be the fully qualified canonical name;
126 -- legal values are `' (empty string) or NULL, which refer to the
127 -- site where this routine is invoked, or any other non-empty string.
128 -- Defaults to `'.
129 -- *sname2: schema name at comparison_site ; need not be the fully qualified
130 -- canonical name
131 -- *oname2: table name at comparision_site
132 -- *comparison_site: site name; need not be the fully qualified canonical
133 -- name;legal values are `' (empty string) or NULL, which refer to
134 -- the site where this routine is invoked, or any other non-empty
135 -- string. Defaults to `'.
136 -- *where_clause: meaningful value is either `' (empty string), NULL, or
137 -- non-empty string; empty string or NULL means no where clause
138 -- is provided, so every row is selected; otherwise, for the
139 -- non-empty string, every row that satisfies the predicate is
140 -- selected. Defaults to `'.
141 -- *column_list: comma-separated list of one or more column names that
142 -- define a fragment (partition) of table "sname.oname." Legal
143 -- values are `' (empty string), NULL, or comma-separated list.
144 -- If `' or NULL, then all column names are used. Defaults to `'.
145 -- Note that list must not have any white space before or
146 -- after the separating comma.
147 -- *missing_rows_sname: schema name of schema where missing rows table is.
148 -- *missing_rows_oname1: name of a table at "missing_rows_site" that contains
149 -- information about the rows in "sname1.oname2" that are missing from
150 -- "sname2.oname2" at "comparison_site" and the rows at the
151 -- "comparison_site" that are missing from the "reference_site."
152 -- *missing_rows_oname2: the name of the table at site "missing_rows_site"
153 -- that holds information about whether a missing row is present at
154 -- a site and absent from another site and links this table with
155 -- "missing_rows_oname1" to identify the actual missing row.
156 -- *missing_rows_site: The site where the "missing_rows_oname1" and
157 -- "missing_rows_oname2" tables are located; legal values
158 -- are `' (empty string) or NULL, which refers to the site where
159 -- this routine is invoked, or legal site name. Defaults to `'.
160 -- *max_missing: a number that refers to the maximum number of rows inserted
161 -- into the "missing_rows_tab" table; the routine returns normally
162 -- when that limit is exceeded. Legal integer values must be
163 -- greater than or equal to 1. Value cannot be NULL.
164 -- *commit_rows: commit the rows inserted into "missing_rows_tab" every
165 -- "commit_row" number of rows. Legal values are `' (empty string),
166 -- NULL, or an integer value that must start at 1. `' or NULL means
167 -- that all rows are inserted before committing. Defaults to 500.
168 --
169 -- Exceptions:
170 -- nosuchsite
171 -- badnumber
172 -- missingprimarykey
173 -- badname
174 -- cannotbenull
175 -- notshapeequivalent
176 -- unknowncolumn
177 -- unsupportedtype
178 -- badmrname
179 -- dbms_repcat.commfailure
180 -- dbms_repcat.missingobject
181 -- dbms_repcat.norepoption
182
183 PROCEDURE differences (sname1 IN VARCHAR2,
184 oname1 IN VARCHAR2,
185 reference_site IN VARCHAR2 := '',
186 sname2 IN VARCHAR2,
187 oname2 IN VARCHAR2,
188 comparison_site IN VARCHAR2 := '',
189 where_clause IN VARCHAR2 := '',
190 column_list IN VARCHAR2 := '',
191 missing_rows_sname IN VARCHAR2,
192 missing_rows_oname1 IN VARCHAR2,
193 missing_rows_oname2 IN VARCHAR2,
194 missing_rows_site IN VARCHAR2 := '',
195 max_missing IN INTEGER,
196 commit_rows IN INTEGER := 500);
197
198 PROCEDURE differences (sname1 IN VARCHAR2,
199 oname1 IN VARCHAR2,
200 reference_site IN VARCHAR2 := '',
201 sname2 IN VARCHAR2,
202 oname2 IN VARCHAR2,
203 comparison_site IN VARCHAR2 := '',
204 where_clause IN VARCHAR2 := '',
205 array_columns IN dbms_utility.name_array,
206 missing_rows_sname IN VARCHAR2,
207 missing_rows_oname1 IN VARCHAR2,
208 missing_rows_oname2 IN VARCHAR2,
209 missing_rows_site IN VARCHAR2 := '',
210 max_missing IN INTEGER,
211 commit_rows IN INTEGER := 500);
212
213 -- Requires: The caller must have already created the tables named
214 -- "missing_rows_oname1" and "missing_rows_oname2" in schema
215 -- "missing_rows_sname" at the site "missing_rows_site" with the
216 -- correct shapes. "column_list" must specify the same number and
217 -- same names of columns that are in "missing_rows_oname1;" both have
218 -- exactly the same column names. In short, all the same arguments
219 -- used in a call to differences(..., column_list, ...) must be
220 -- used in this routine to rectify the table at the comparison_site.
221 -- The tables "sname1.oname1@reference_site" and "sname2.oname2@comparison_site"
222 -- must be the same ones used in a previous invocation of differences().
223 -- There must be no white space before or after commas in "column_list."
224 -- Effects: This routine uses the information in table "missing_rows_oname1" and
225 -- "missing_rows_oname2" in schema "missing_rows_sname" to rectify table named
226 -- "sname2.oname2" at site "comparison_site." Rows are deleted from
227 -- "sname2.oname2" at "comparison_site" that are not in "sname1.oname1" at
228 -- "reference_site." Rows missing from "sname2.oname2" at "comparison_site" are
229 -- inserted. When this routine returns normally, the two tables are exactly
230 -- equivalent. In short, extraneous rows are deleted from the table
231 -- "sname2.oname2" at "comparison_site" and missing rows are inserted
232 -- into the table. If tables "missing_rows_oname1" and "missing_rows_oname2"
233 -- are empty, then this routine has no effect. Commit every "commit_rows"
234 -- number of rows are deleted from "sname2.oname2"; commit every "commit_rows"
235 -- number of rows are inserted into "sname2.oname2."
236 -- Raises exception nosuchsite if the "reference_site,"
237 -- "comparison_site," or "missing_rows_site" does not name a site.
238 -- Raises exception badnumber if "commit_rows" is
239 -- less than 1. Raises exception commfailure if the remote site
240 -- inaccessible. If "sname1," "oname2," "sname2," "oname2," "missing_rows_oname1,"
241 -- or "missing_rows_oname2" is NULL or `' (empty string), raise exception badname.
242 -- Raise badmrname if "missing_rows_oname1" and "oname1"
243 -- have the same name; the "oname1" truth table cannot be used as one
244 -- of the missing rows tables.
245 -- Raises exception missingobject if tables named "oname1," "oname2,"
246 -- "missing_rows_oname1" or "missing_rows_oname2" do not exist.
247 -- If table "sname1.oname1" (truth) at the reference site contains duplicate
248 -- rows, those rows are treated under set semantics as one single row
249 -- and will appear in the "missing_rows_oname1" and "missing_rows_oname2"
250 -- exactly once. When the table at the comparison site is rectified,
251 -- the duplicate rows are in fact inserted into the table at the comparison_site.
252 -- If there are duplicate rows at the table at the
253 -- comparison site, then all rows will be deleted. If replication is
254 -- turned on, this routine will remember that state, turn replication
255 -- off to allow rectifying to proceed, and then reinstate the original state.
256 -- The tables "missing_rows_oname1" and "missing_rows_oname2" cannot contain
257 -- differences for different pairs; the routine must only be used to
258 -- rectify a pair of table whose differences are in the associated
259 -- missing rows tables. The table "missing_rows_oname1"
260 -- must have only the column names given in "column_list" and the
261 -- associated data types, which defines a fragment. The table
262 -- "missing_rows_oname2" must have three columns with the associated datatypes:
263 -- "present" (type varchar2(128))for the name of the site where the row appeared,
264 -- "absent" (type varchar2(128)) for the name of the site where the row
265 -- does not appear, and "r_id" (type rowid) to hold the rowid of the
266 -- corresponding row in "missing_rows_oname1."
267 -- If the replication option is not installed, then raise exception
268 -- dbms_repcat.norepoption.
269 --
270 -- Arguments:
271 -- *sname1: name of schema at "reference_site;" need not be the
272 -- fully qualified canonical name.
273 -- *oname1: name of table at "reference_site"
274 -- *reference_site: site name; need not be the fully qualified
275 -- canonical name; legal values are `' (empty string) or
276 -- NULL, which refer to the site where this routine is invoked,
277 -- or any other non-empty string. Defaults to `'.
278 -- *sname2: schema name at "comparison_site;" need not be the fully qualified
279 -- canonical name
280 -- *oname2: name of table at "comparison_site"
281 -- *comparison_site: site name; need not be the fully qualified canonical
282 -- name. Legal values are `' (empty string) or NULL, which refer
283 -- to the site where this routine is invoked, or any other
284 -- non-empty string. Defaults to `'.
285 -- *column_list: comma-separated list of one or more column names that
286 -- define a fragment (partition) of table "sname.oname." Legal
287 -- values are `' (empty string), NULL, or comma-separated list.
288 -- If `' or NULL, then all column names are used. Defaults to `'.
289 -- Note that list must not have any white space before or
290 -- after the separating comma.
291 -- *missing_rows_sname: schema name of schema where missing rows table
292 -- is located.
293 -- *missing_rows_oname1: name of a table at "missing_rows_site" that
294 -- contains information about the rows in "sname1.oname2" that
298 -- *missing_rows_oname2: the name of the table at site "missing_rows_site"
295 -- are missing from "sname2.oname2" at "comparison_site" and
296 -- the rows at the "comparison_site" that are missing from
297 -- the "reference_site."
299 -- that holds information about whether a missing row is present
300 -- at a site and absent from another site and links this table
301 -- with "missing_rows_oname1" to identify the actual missing row.
302 -- *missing_rows_site: The site where the "missing_rows_oname1" and
303 -- "missing_rows_oname2" tables are
304 -- located; legal values are `' (empty string) or NULL, which
305 -- refers to the site where this routine is invoked, or legal
306 -- site name. Defaults to `'.
307 -- *commit_rows: commit the rows deleted from "sname2.oname2" every
308 -- "commit_rows" number of rows. Similarly, commit the rows
309 -- inserted intot "sname.oname2" "commit_rows" number of rows.
310 -- Legal values are `' (empty string), NULL, or an integer value that must
311 -- start at 1. `' or NULL means that all rows are deleted or inserted
312 -- before committing. Defaults to 500.
313 --
314 -- Exceptions:
315 -- nosuchsite
316 -- dbms_repcat.commfailure
317 -- badnumber
318 -- badname
319 -- dbms_repcat.missingobject
320 -- dbms_repcat.norepoption
321
322 PROCEDURE rectify ( sname1 IN VARCHAR2,
323 oname1 IN VARCHAR2,
324 reference_site IN VARCHAR2 := '',
325 sname2 IN VARCHAR2,
326 oname2 IN VARCHAR2,
327 comparison_site IN VARCHAR2 := '',
328 column_list IN VARCHAR2 := '',
329 missing_rows_sname IN VARCHAR2,
330 missing_rows_oname1 IN VARCHAR2,
331 missing_rows_oname2 IN VARCHAR2,
332 missing_rows_site IN VARCHAR2 := '',
333 commit_rows IN INTEGER := 500);
334
335 PROCEDURE rectify ( sname1 IN VARCHAR2,
336 oname1 IN VARCHAR2,
337 reference_site IN VARCHAR2 := '',
338 sname2 IN VARCHAR2,
339 oname2 IN VARCHAR2,
340 comparison_site IN VARCHAR2 := '',
341 array_columns IN dbms_utility.name_array,
342 missing_rows_sname IN VARCHAR2,
343 missing_rows_oname1 IN VARCHAR2,
344 missing_rows_oname2 IN VARCHAR2,
345 missing_rows_site IN VARCHAR2 := '',
346 commit_rows IN INTEGER := 500);
347
348
349 PROCEDURE turn_replication_off;
350
351 PROCEDURE turn_replication_on;
352
353 end;
[Home] [Help]