1 PACKAGE dbms_comparison AUTHID CURRENT_USER AS
2
3
4 /****************************************************************************/
5 /* Defines For Constants.
6 /****************************************************************************/
7
8 --
9 -- Compare Mode:
10 --
11 CMP_COMPARE_MODE_OBJECT CONSTANT VARCHAR2(30) := 'OBJECT';
12
13
14 --
15 -- Scan Mode:
16 --
17 CMP_SCAN_MODE_FULL CONSTANT VARCHAR2(30) := 'FULL';
18 CMP_SCAN_MODE_RANDOM CONSTANT VARCHAR2(30) := 'RANDOM';
19 CMP_SCAN_MODE_CYCLIC CONSTANT VARCHAR2(30) := 'CYCLIC';
20 CMP_SCAN_MODE_CUSTOM CONSTANT VARCHAR2(30) := 'CUSTOM';
21
22
23 --
24 -- dml options
25 --
26 CMP_CONVERGE_LOCAL_WINS CONSTANT VARCHAR2(30) := 'LOCAL';
27 CMP_CONVERGE_REMOTE_WINS CONSTANT VARCHAR2(30) := 'REMOTE';
28
29
30 -- Null value's substitution value
31 CMP_NULL_VALUE_DEF CONSTANT VARCHAR2(100) := 'ORA$STREAMS$NV';
32
33 -- Other Default values
34 CMP_MAX_NUM_BUCKETS CONSTANT PLS_INTEGER := 1000;
35 CMP_MIN_ROWS_IN_BUCKET CONSTANT PLS_INTEGER := 10000;
36
37 /****************************************************************************/
38 /* Public APIs below.
39 /****************************************************************************/
40
41
42 /*
43 *
44 * create_comparison: Create a comparison.
45 *
46 * NOTE:
47 * Objects on different databases have to be of the same shape.
48 * The object must have an index on a number column, preferably the PK.
49 *
50 *
51 * PARAMETERS:
52 * comparison_name : Identifier for the comparison.
53 * schema_name : Name of the schema to compare.
54 * Must be same schema name on both databases.
55 * object_name : Name of the object to compare.
56 * remote_schema_name : Name of the schema to compare.
57 * (OPTIONAL)
58 * remote_object_name : Name of the object to compare.
59 * (OPTIONAL)
60 * dblink_name : Database link to the remote database.
61 * index_schema_name : Name of index schema.
62 * index_name : Name of index.
63 * comparison_mode : CMP_COMPARE_MODE_OBJECT. In future, there will be
64 more modes.
65 * column_list : '*' : include ALL columns in comparison (default)
66 * Other : Comma-separate list of columns to check.
67 * Must include the column that has been chosen
68 * as the index column for comparison.
69 *
70 * Column names can be quoted.
71 *
72 * scan_mode : CMP_SCAN_MODE_FULL, CMP_SCAN_MODE_RANDOM,
73 * CMP_SCAN_MODE_CYCLIC, CMP_SCAN_MODE_CUSTOM
74 * scan_percent : The percent of table to scan. Applicable when
75 * scan_mode IN (CMP_SCAN_MODE_RANDOM,
76 * CMP_SCAN_MODE_CYCLIC)
77 * null_value : The value to substitute null column values.
78 * (OPTIONAL)
79 * local_converge_tag : The local streams tag to set before performing
80 * any dmls to converge the data.
81 * (OPTIONAL)
82 * remote_converge_tag: The remote streams tag to set before performing
83 * any dmls to converge the data.
84 * (OPTIONAL)
85 * num_buckets : Suggested number of buckets to divide a scan into.
86 * (OPTIONAL)
87 * num_rows_in_bucket : Suggested number of rows in a bucket.
88 * (OPTIONAL)
89 *
90 */
91
92 PROCEDURE create_comparison(
93 comparison_name VARCHAR2,
94 schema_name VARCHAR2,
95 object_name VARCHAR2,
96 dblink_name VARCHAR2,
97 index_schema_name VARCHAR2 DEFAULT NULL,
98 index_name VARCHAR2 DEFAULT NULL,
99 remote_schema_name VARCHAR2 DEFAULT NULL,
100 remote_object_name VARCHAR2 DEFAULT NULL,
101 comparison_mode VARCHAR2 DEFAULT CMP_COMPARE_MODE_OBJECT,
102 column_list VARCHAR2 DEFAULT '*',
103 scan_mode VARCHAR2 DEFAULT CMP_SCAN_MODE_FULL,
104 scan_percent NUMBER DEFAULT NULL,
105 null_value VARCHAR2 DEFAULT CMP_NULL_VALUE_DEF,
106 local_converge_tag RAW DEFAULT NULL,
107 remote_converge_tag RAW DEFAULT NULL,
108 max_num_buckets NUMBER DEFAULT CMP_MAX_NUM_BUCKETS,
109 min_rows_in_bucket NUMBER DEFAULT CMP_MIN_ROWS_IN_BUCKET
110 );
111
112
113 TYPE comparison_type IS RECORD (
114 scan_id NUMBER,
115 loc_rows_merged NUMBER, -- local rows upserted
116 rmt_rows_merged NUMBER, -- remote rows upserted
117 loc_rows_deleted NUMBER,
118 rmt_rows_deleted NUMBER
119 );
120
121
122 /*
123 * compare: Perform a comparison identified by comparison name.
124 *
125 * PARAMETERS:
126 * comparison_name : Identifier for the comparison.
127 * scan_info : Information returned about the scan.
128 * perform_row_dif : When TRUE, performs individual row level difs.
129 * When FALSE, will stop at the bucket level.
130 * min_value : When scan_mode for the comparison is CMP_SCAN_MODE_CUSTOM
131 * then a minimum index column value must be specified.
132 * max_value : Maximum index column value and similar to min_value.
133 *
134 * RETURN:
135 *
136 * TRUE : When no difs are found.
137 * FALSE : When difs are found.
138 */
139
140 FUNCTION compare(
141 comparison_name IN VARCHAR2,
142 scan_info OUT comparison_type,
143 min_value IN VARCHAR2 DEFAULT NULL,
144 max_value IN VARCHAR2 DEFAULT NULL,
145 perform_row_dif IN BOOLEAN DEFAULT FALSE
146 ) RETURN BOOLEAN;
147
148
149
150 /*
151 * recheck: Recheck a specified scan.
152 *
153 * PARAMETERS:
154 * comparison_name: Identifier for the comparison.
155 * scan_id : The scan id to be rechecked. It need not be a top-level
156 * scan.
157 * perform_row_dif: When TRUE, performs individual row level difs.
158 * When FALSE, will stop at the bucket level.
159 *
160 * RETURN:
161 *
162 * TRUE : When no difs are found.
163 * FALSE : When difs are found.
164 *
165 */
166 FUNCTION recheck(
167 comparison_name IN VARCHAR2,
168 scan_id IN NUMBER,
169 perform_row_dif IN BOOLEAN DEFAULT FALSE
170 ) RETURN BOOLEAN;
171
172
173 /*
174 * converge: Execute compensating dmls to get the two objects to converge.
175 *
176 * PARAMETERS:
177 * comparison_name : Identifier for the comparison.
178 * scan_id : The scan id for which dmls need to be executed.
179 * It need not be a top-level scan.
180 * converge_options: This decides whether the local object wins or the
181 * remote object wins.
182 * perform_commit : Whether to perform a Commit after executing the dmls.
183 * local_converge_tag : The local streams tag to set before performing
184 * any dmls to converge the data. This will override
185 * the local tag set through create_comparison() API.
186 * remote_converge_tag : The remote streams tag to set before performing
187 * any dmls to converge the data. This will override
188 * the remote tag set through create_comparison() API.
189 *
190 */
191 PROCEDURE converge(
192 comparison_name IN VARCHAR2,
193 scan_id IN NUMBER,
194 scan_info OUT comparison_type,
195 converge_options IN VARCHAR2 DEFAULT CMP_CONVERGE_LOCAL_WINS,
196 perform_commit IN BOOLEAN DEFAULT TRUE,
197 local_converge_tag IN RAW DEFAULT NULL,
198 remote_converge_tag IN RAW DEFAULT NULL
199 );
200
201
202 /*
203 * purge_comparison: Purge a comparison's results or a subset of it.
204 *
205 * PARAMETERS:
206 * comparison_name : Identifier for the comparison.
207 * scan_id : The scan id whose results need to be purged.
208 * It has to be a top-level scan, else an error is raised.
209 * (Optional)
210 * purge_date : The date before which results can be purged.
211 * (Optional)
212 *
213 */
214
215 PROCEDURE purge_comparison(
216 comparison_name IN VARCHAR2,
217 scan_id IN NUMBER DEFAULT NULL,
218 purge_time IN TIMESTAMP DEFAULT NULL
219 );
220
221
222 /*
223 * drop_comparison: Drop a comparison.
224 *
225 * PARAMETERS:
226 * comparison_name: Identifier for the comparison.
227 *
228 */
229
230 PROCEDURE drop_comparison(
231 comparison_name IN VARCHAR2
232 );
233
234
235
236
237 end dbms_comparison;
[Home] [Help]