1 PACKAGE dbms_repcat_sna AUTHID CURRENT_USER AS
2 --
3 -- NOTE: The type varchar2s is equivalent to the type varchar2s
4 -- in the package dbms_repcat. If you make changes to this
5 -- version, please make the same changes to the other version.
6 --
7 TYPE varchar2s IS TABLE OF VARCHAR(60) INDEX BY BINARY_INTEGER;
8
9 ----------------------------------------------------------------------------
10 PROCEDURE register_snapshot_repgroup(gname IN VARCHAR2,
11 snapsite IN VARCHAR2,
12 comment IN VARCHAR2 := NULL,
13 rep_type IN NUMBER
14 := dbms_repcat_decl.reg_unknown,
15 fname IN VARCHAR2 := NULL,
16 gowner IN VARCHAR2 := 'PUBLIC');
17 -- This procedure is used at the master site to manually register
18 -- a snapshot repgroup.
19 -- Arguments:
20 -- gname: Name of the repgroup
21 -- snapsite: Site of the snapshot repgroup
22 -- comment: comment describing the snapshot repgroup
23 -- rep_type: Version of the snapshot group (valid constants are
24 -- defined in dbms_repcat package header)
25 -- fname: This parameter is reserved for internal use.
26 -- Do not specify this parameter unless directed
27 -- by Oracle Worldwide Customer Support.
28 -- gowner: Owner of the repgroup
29 ----------------------------------------------------------------------------
30 PROCEDURE unregister_snapshot_repgroup(gname IN VARCHAR2,
31 snapsite IN VARCHAR2,
32 gowner IN VARCHAR2 := 'PUBLIC');
33 -- This procedure is used at the master site to manually unregister
34 -- a snapshot repgroup.
35 -- Arguments:
36 -- gname: Name of the repgroup
37 -- snapsite: Site of the snapshot repgroup
38 -- gowner: owner of the repgroup
39 ----------------------------------------------------------------------------
40 PROCEDURE alter_snapshot_propagation(gname IN VARCHAR2,
41 propagation_mode IN VARCHAR2,
42 comment IN VARCHAR2 := '',
43 gowner IN VARCHAR2
44 := 'PUBLIC');
45 -- Alter the propagation method of all replication snapshots, procedure,
46 -- packages, and package bodies for all snapshot repobjects in the
47 -- specified snapshot repgroup.
48 --
49 -- Altering the propagation method involves regenerating replication
50 -- support at the materialized view site.
51 --
52 -- Arguments:
53 -- gname: name of the snapshot object group
54 -- propagation_mode: SYNCHRONOUS or ASYNCHRONOUS
55 -- comment: comment added to the RepProp view
56 -- gowner: owner of the snapshot object group
57 --
58 -- Exceptions:
59 -- notcompat: only databases operating in 7.3 (or later) mode can
60 -- use this procedure.
61 -- missingrepgroup: replicated object group does not exist
62 -- typefailure: incorrect propagation mode specified
63 -- commfailure: cannot contact master
64 -- failaltermviewrop: materialized view repgroup propagation can be
65 -- altered only when there is no other repgroup with the same master
66 -- sharing the site.
67 ----------------------------------------------------------------------------
68 PROCEDURE create_snapshot_repgroup(gname IN VARCHAR2,
69 master IN VARCHAR2,
70 comment IN VARCHAR2 := '',
71 propagation_mode IN VARCHAR2
72 := 'ASYNCHRONOUS',
73 fname IN VARCHAR2 := NULL,
74 gowner IN VARCHAR2
75 := 'PUBLIC');
76 -- Create a new empty snapshot repgroup at the local site. The group name
77 -- must be a master repgroup at the master database.
78 --
79 -- Arguments:
80 -- gname: name of the replicated object group
81 -- master: database to use as the master
82 -- comment: comment added to the schema_comment field of RepCat view
83 -- propagation_mode: method of propagation for all updatable snapshots
84 -- in the object group (SYNCHRONOUS or ASYNCHRONOUS)
85 -- fname: This parameter is reserved for internal use.
86 -- Do not specify this parameter unless directed
87 -- by Oracle Worldwide Customer Support.
88 -- gowner: owner of the replicated object group
89 --
90 -- Exceptions:
91 -- duplicaterepgroup: if the objectgroup already exists as a repgroup
92 -- at the invocation site.
93 -- nonmaster: if the given database is not a master site.
94 -- commfailure: if the given database is not accessible.
95 -- norepoption: if advanced replication option not installed
96 -- typefailure: if propagation mode specified incorrectly
97 -- missingrepgroup: object group missing at master site
98 -- invalidqualifier: connection qualifier specified for master is not
99 -- valid for the object group
100 -- alreadymastered: if at the local site there is another snapshot
101 -- repgroup with the same group name, but different master.
102 ----------------------------------------------------------------------------
103 PROCEDURE create_snapshot_repobject(sname IN VARCHAR2,
104 oname IN VARCHAR2,
105 type IN VARCHAR2,
106 ddl_text IN VARCHAR2 := '',
107 comment IN VARCHAR2 := '',
108 gname IN VARCHAR2 := '',
109 gen_objs_owner IN VARCHAR2 := '',
110 min_communication IN BOOLEAN := TRUE,
111 generate_80_compatible
112 IN BOOLEAN := TRUE,
113 gowner IN VARCHAR2
114 := 'PUBLIC');
115 -- Add the given object name and type to the RepObject view at the local
116 -- snapshot repgroup. The allowed types are `package', `package body',
117 -- 'procedure', `snapshot', `synonym', 'trigger', 'index' and `view'.
118 --
119 -- For objects of type `snapshot', create the row-level replication trigger
120 -- and client-side half of the stored package if the underlying table
121 -- uses row/column-level replication.
122 --
123 -- The parameter ddl_text defines the snapshot if the snapshot does not
124 -- already exist. The value of oname should match the snapshot
125 -- name defined in the ddl_text. The snaphot's master should match the
126 -- master stored in all_repgroup, this includes the connection qualifier
127 -- that may be associated with the master group.
128 --
129 -- gen_objs_owner indicates the schema in which the generated procedural
130 -- wrapper should be install. If this parameter is NULL, the value of the
131 -- sname parameter is used.
132 --
133 -- If min_communication is TRUE and type is 'SNAPSHOT', the update trigger
134 -- sends the new value of a column only if the update statement modifies the
135 -- column. The update trigger sends the old value of the column only if it
136 -- is a key column or a column in a modified column group.
137 --
138 -- If generate_80_compatible is true, deferred RPC's with the TOP
139 -- flavor are generated using the 8.0 protocol.
140 --
141 -- gowner is the owner of the replicated object group
142 --
143 -- Exceptions:
144 -- missingschema if specified owner of generated objects does not exist
145 -- nonmview if the invocation site is not a materialized view site.
146 -- nonmaster if the master is no longer a master site.
147 -- missingobject if the given object does not exist in the master's
148 -- replicated object group.
149 -- duplicateobject if the given object already exists.
150 -- typefailure if the type is not an allowable type.
151 -- ddlfailure if the DDL does not succeed.
152 -- commfailure if the master is not accessible.
153 -- badmviewddl if th ddl was executed but materialized view does not exist
154 -- onlyonemview if only one materialized view for master table can be
155 -- created
156 -- badmviewname if materialized view base table differs from master table
157 -- misingrepgroup if replicated object group does not exist
158 ----------------------------------------------------------------------------
159 PROCEDURE create_snapshot_repschema(sname IN VARCHAR2,
160 master IN VARCHAR2,
161 comment IN VARCHAR2 := '');
162 -- OBSOLETE PROCEDURE: use create_snapshot_repgroup()
163 -- Create a new empty snapshot repschema. The schema name must be a master
164 -- repschema at the master database. In addition, the schema must also exist
165 -- locally as a database schema.
166 --
167 -- Exceptions:
168 -- duplicateschema: if the schema already exists as a replicated object
169 -- group at the invocation site.
170 -- nonmaster: if the given database is not a master site.
171 -- commfailure: if the given database is not accessible.
172 ----------------------------------------------------------------------------
173 PROCEDURE drop_snapshot_repgroup(gname IN VARCHAR2,
174 drop_contents IN BOOLEAN := FALSE,
175 gowner IN VARCHAR2 := 'PUBLIC');
176 -- Drop the given snapshot repgroup and optionally all of its contents
177 -- at this materialized view site.
178 --
179 -- Arguments:
180 -- gname: name of the replicated object group to be dropped
181 -- drop_contents: (see comment above)
182 -- gowner: owner of the replicated object group
183 --
184 -- Exceptions:
185 -- nonmview: if the invocation site is not a materialized view site.
186 -- missingrepgroup: the replicated object group does not exist
187 ----------------------------------------------------------------------------
188 PROCEDURE drop_snapshot_repobject(sname IN VARCHAR2,
189 oname IN VARCHAR2,
190 type IN VARCHAR2,
191 drop_objects IN BOOLEAN := FALSE);
192 -- Remove the given object name from the local replication catalog
193 -- and optionally drop the object and dependent objects.
194 --
195 -- Exceptions:
196 -- nonmview if the invocation site is not a materialized view site.
197 -- missingobject if the given object does not exist.
198 -- typefailure if the given type parameter is not supported.
199 ----------------------------------------------------------------------------
200 PROCEDURE drop_snapshot_repschema(sname IN VARCHAR2,
201 drop_contents IN BOOLEAN := FALSE);
202 -- OBSOLETE PROCEDURE: use drop_snapshot_repgroup()
203 -- Drop the given snapshot repschema and optionally all of its contents
204 -- at this materialized view site. In addition, the schema must also exist locally
205 -- as a database schema.
206 --
207 -- Exceptions:
208 -- nonmview if the invocation site is not a materialized view site.
209 ----------------------------------------------------------------------------
210 PROCEDURE generate_snapshot_support(sname IN VARCHAR2,
211 oname IN VARCHAR2,
212 type IN VARCHAR2,
213 gen_objs_owner IN VARCHAR2 := '',
214 min_communication IN BOOLEAN := TRUE,
215 generate_80_compatible
216 IN BOOLEAN := TRUE);
217 -- If the object exists in the replicated snapshot object group
218 -- as an updatable snapshot using row/column-level replication,
219 -- create the row-level replication trigger and stored package.
220 --
221 -- If the object exists in the replicated object group as a procedure
222 -- or package (body), then generate the appropriate wrappers.
223 --
224 -- Parameter gen_objs_owner specifies the schema in which the generated
225 -- replication package and wrapper should be installed. If this value is
226 -- NULL, then the generated package or wrapper will be installed in the
227 -- schema specified by the sname parameter.
228 --
229 -- If min_communication is TRUE, then the update trigger sends the new value
230 -- of a column only if the update statement modifies the column. The update
231 -- trigger sends the old value of the column only if it is a key column or
232 -- a column in a modified column group.
233 --
234 -- If generate_80_compatible is true, deferred RPC's with the TOP
235 -- flavor are generated using the 8.0 protocol.
236 --
237 -- Exceptions:
238 -- nonmview: if the invocation site is not a materialized view site.
239 -- missingobject: if the given object does not exist as a snapshot in the
240 -- replicated objevt group awaiting row/column-level
241 -- replication information or as a procedure or package
242 -- (body) awaiting wrapper generation.
243 -- typefailure: if the given type parameter is not supported.
244 -- missingschema: if specified owner of generated objects does not exist
245 -- missingremoteobject: if the master object has not yet generated
246 -- replication support.
247 -- commfailure: if the master is not accessible
248 ----------------------------------------------------------------------------
249 PROCEDURE refresh_snapshot_repgroup(gname IN VARCHAR2,
250 drop_missing_contents IN BOOLEAN
251 := FALSE,
252 refresh_snapshots IN BOOLEAN
253 := FALSE,
254 refresh_other_objects IN BOOLEAN
255 := FALSE,
256 gowner IN VARCHAR2
257 := 'PUBLIC');
258 -- Refresh the RepCat views for the given repgroup and optionally drop
259 -- objects no longer in the repgroup. Consistently refresh the snapshots
260 -- if refresh_snapshots is TRUE. Refresh the other objects if
261 -- refresh_other_objects is TRUE. The value in gname must be an existing
262 -- snapshot object group in the local database.
263 --
264 -- Exceptions:
265 -- nonmview: if the invocation site is not a materialized view site.
266 -- nonmaster: if the master is no longer a master site.
267 -- commfailure: if the master is not accessible.
268 -- missingrepgroup: if the replicated object group does not exist
269 ----------------------------------------------------------------------------
270 PROCEDURE refresh_snapshot_repschema(sname IN VARCHAR2,
271 drop_missing_contents IN BOOLEAN
272 := FALSE,
273 refresh_snapshots IN BOOLEAN
274 := FALSE,
275 refresh_other_objects IN BOOLEAN
276 := FALSE,
277 execute_as_user IN BOOLEAN);
278 PROCEDURE refresh_snapshot_repschema(sname IN VARCHAR2,
279 drop_missing_contents IN BOOLEAN
280 := FALSE,
281 refresh_snapshots IN BOOLEAN
282 := FALSE,
283 refresh_other_objects IN BOOLEAN
284 := FALSE);
285 -- OBSOLETE PROCEDURE: use refresh_snapshot_repgroup()
286 -- Refresh the RepCat views for the given repgroup and optionally drop
287 -- objects no longer in the repschema. Consistently refresh the snapshots
288 -- iff refresh_snapshots is TRUE. Refresh the other objects if
289 -- refresh_other_objects is TRUE. Deferred RPCs to the master site are
290 -- pushed as the current session's user if execute_as_user is TRUE
291 -- The schema sname must exist as a schema in the local database
292 --
293 -- Exceptions:
294 -- nonmview if the invocation site is not a materialized view site.
295 -- nonmaster if the master is no longer a master site.
296 -- commfailure if the master is not accessible.
297 ----------------------------------------------------------------------------
298 PROCEDURE repcat_import_check(gname IN VARCHAR2 := '',
299 master IN BOOLEAN,
300 sname IN VARCHAR2 := '',
301 gowner IN VARCHAR2 := 'PUBLIC');
302 -- Update the object identifiers and status values in repcat$_repobject
303 -- for the given repgroup, preserving object status values other than VALID.
304 --
305 -- Exceptions:
306 -- missingschema: if the replicated object group does not exist.
307 -- nonmaster: if master is TRUE and either the database is not a master or
308 -- the database is not the expected database.
309 -- nonmview: if master is FALSE and the database is not a materialized view site.
310 -- missingobject: if a valid repobject in the schema does not exist.
311 ----------------------------------------------------------------------------
312 PROCEDURE repcat_import_check;
313 -- Invoke repcat_import_check(gowner, gname) for each replicated object group
314 --
315 -- Exceptions:
316 -- nonmaster if the database is not the expected database for any
317 -- replicated object group.
318 -- missingobject if a valid replicated object in any schema does not exist.
319 ----------------------------------------------------------------------------
320 PROCEDURE switch_snapshot_master(gname IN VARCHAR2,
321 master IN VARCHAR2,
322 gowner IN VARCHAR2 := 'PUBLIC');
323 -- Change the master database of the snapshot repgroup to the given
324 -- database. The new database must contain a replica of the master
325 -- repgroup. Each snapshot in the local repgroup will be completely
326 -- refreshed from the new master the next time it is refreshed.
327 -- This procedure will raise an error if any snapshot definition query
328 -- is bigger than 32K.
329 --
330 -- Any snapshot logs should be created at all masters to avoid future
331 -- complete refreshes.
332 --
333 -- Arguments:
334 -- gname: name of the snapshot object group
335 -- master: name of the new master
336 -- gowner: owner of the snapshot object group
337 --
338 -- Exceptions:
339 -- nonmview: if the invocation site is not a materialized view site.
340 -- nonmaster: if the given database is not a master site.
341 -- commfailure: if the given database is not accessible.
342 -- missingrepgroup: snapshot repgroup does not exist
343 -- qrytoolong: snapshot definition query is > 32K
344 -- alreadymastered: if at the local site there is another snapshot repgroup
345 -- with the same group name and mastered at the old master.
346 ----------------------------------------------------------------------------
347 ---
348 ---
349 ---
350 --- #######################################################################
351 --- #######################################################################
352 --- INTERNAL PROCEDURES
353 ---
354 --- The following procedures provide internal functionality and should
355 --- not be called directly. Invoking these procedures may corrupt your
356 --- replication environment.
357 ---
358 --- #######################################################################
359 --- #######################################################################
360 PROCEDURE validate_for_local_flavor(
361 gname IN VARCHAR2,
362 fname IN VARCHAR2,
363 gowner IN VARCHAR2 := 'PUBLIC' );
364
365 PROCEDURE set_local_flavor(
366 gname IN VARCHAR2,
367 fname IN VARCHAR2,
368 validate IN BOOLEAN := TRUE,
369 gowner IN VARCHAR2 := 'PUBLIC' );
370 END dbms_repcat_sna;