1 package body dbms_objects_apps_utils is
2
3
4 -- Prodecure update_types for APPS UPGRADE
5 -- This one will take in 2 schema names, schema1 and schema2 and will
6 -- do the following ...
7 -- For each type type1 present in schema2 and schema1, it will make any
8 -- object column/table dependent on schema1.type1 point to schema2.type1
9 --
10 -- This can also be used for updating just one type in schema1. In that
11 -- case give the typename as the 3rd parameter. If you give NULL for typename
12 -- it will default to the above behaviour.
13 --
14 -- This also takes another parameter check_update. If this is set to TRUE
15 -- then it will check to make sure that none of the types in schema1 has
16 -- any tables dependencies after the dictionary update. In cases where
17 -- this may not hold true, including most cases where typename is given,
18 -- set it to FALSE.
19 --
20 -- This will do a couple of sanity checks, hashcode and version, to make
21 -- sure that the schema1.type1 and schema2.type2 are structurally similar
22 -- and have the same version#.
23 --
24 -- IMPORTANT: When using this function make sure that if you are moving the
25 -- table dependency for any type in schema1 to a similar type in schema2, all
26 -- of it's referenced types are also moved. ie for type
27 -- t1 (a1 int, b1 t2, b2 t3), schema2 should contain t2 and t3 if t1 is
28 -- included. If not the dictionary will end up in an inconsistent state.
29
30 procedure update_types (schema1 varchar2, schema2 varchar2,
31 typename varchar2, check_update boolean) is
32
33 userid number;
34 lname varchar2(30);
35 lobjid number;
36 ltoid raw(16);
37 lhashcode raw(17);
38 lversion number;
39 lstime date;
40 i number;
41 numtypes number;
42 ltinfo dbms_objects_utils_tinfo;
43 j number;
44 allgood int := 0;
45
46 type tabtinfo is table of dbms_objects_utils_tinfo index by binary_integer;
47
48 vtabtinfo tabtinfo;
49
50 -- query to get relevant type info
51 cursor c1 is
52 select o.name, o.obj#, o.oid$, t.hashcode, t.version#, o.stime
53 from obj$ o, obj$ o1, type$ t
54 where o.owner#=(select user# from user$ where name=schema2) and
55 o1.owner#=(select user# from user$ where name=schema1) and
56 o1.name= o.name and o.type#=13 and o1.type#=13 and o.oid$=t.tvoid
57 and o.subname is NULL;
58
59 -- query to get relevant type info if type name is given
60 cursor c5 is
61 select o.name, o.obj#, o.oid$, t.hashcode, t.version#, o.stime
62 from obj$ o, obj$ o1, type$ t
63 where o.owner#=(select user# from user$ where name=schema2) and
64 o1.owner#=(select user# from user$ where name=schema1) and
65 o1.name= o.name and o.type#=13 and o1.type#=13 and o.oid$=t.tvoid
66 and o.name = typename and o.subname is NULL;
67
68 cursor c2 is
69 select user# from user$ where name=schema1;
70
71 -- hashcode sanity check query
72 cursor c3 (c3name varchar2, c3hashcode raw) is
73 select 1 from obj$ o, type$ t where o.name=c3name and o.owner#=userid and
74 t.tvoid=o.oid$ and t.hashcode = c3hashcode and o.subname is NULL;
75
76 -- version sanity check query
77 cursor c4 (c3name varchar2, c3version number) is
78 select 1 from obj$ o, type$ t where o.name=c3name and o.owner#=userid and
79 t.tvoid=o.oid$ and t.version# = c3version and o.subname is NULL;
80
81 -- sanity check to make sure that none of the tables are now dependent on
82 -- schema1
83 cursor c6 is
84 select 1 from dependency$ d, obj$ o where o.type#=2 and o.obj#=d.d_obj# and
85 d.p_obj# in (select obj# from obj$ where type#=13 and subname is null and
86 owner#=userid);
87
88 begin
89
90 -- populate the list of types from schema2 ... and the relevant info
91 i := 1;
92 if typename is null then
93 i := 1;
94 open c1;
95 loop
96 fetch c1 into lname, lobjid, ltoid, lhashcode, lversion, lstime;
97 exit when c1%notfound;
98 vtabtinfo(i) := dbms_objects_utils_tinfo(lname, lobjid, ltoid, lhashcode, lversion, lstime);
99 i := i + 1;
100 end loop;
101 close c1;
102 numtypes := i-1;
103 else
104 open c5;
105 loop
106 fetch c5 into lname, lobjid, ltoid, lhashcode, lversion, lstime;
107 exit when c5%notfound;
108 vtabtinfo(i) := dbms_objects_utils_tinfo(lname, lobjid, ltoid, lhashcode, lversion, lstime);
109 i := i + 1;
110 end loop;
111 close c5;
112 numtypes := i-1;
113 end if;
114
115 -- now get the user# for schema1
116 open c2;
117 fetch c2 into userid;
118 close c2;
119
120 -- now lets do a wee little check to make sure that the types that we
121 -- are replacing are structurally similar, using hashcode check
122 for i in 1..numtypes loop
123 ltinfo := vtabtinfo(i);
124 open c3 (ltinfo.name, ltinfo.hashcode);
125 fetch c3 into j;
126 if c3%notfound then
127 close c3;
128 goto error1;
129 end if;
130 close c3;
131 end loop;
132
133
134 -- Also make sure that the versions match
135 for i in 1..numtypes loop
136 ltinfo := vtabtinfo(i);
137 open c4 (ltinfo.name, ltinfo.version);
138 fetch c4 into j;
139 if c4%notfound then
140 close c4;
141 goto error2;
142 end if;
143 close c4;
144 end loop;
145
146 -- ok, we are ready to update the metadata. We do 3 updates
147 -- 1. update the toid value in coltype$
148 -- 2. update the toid value in subcoltype$
149 -- 3. update the p_obj# and p_timestamp in dependency$ for the type,
150 -- for all table dependents
151 allgood := 1;
152 for i in 1..numtypes loop
153 ltinfo := vtabtinfo(i);
154 -- coltype$ update
155 update coltype$ set toid=ltinfo.toid where toid=(select oid$ from
156 obj$ where name=ltinfo.name and owner#=userid and type#=13 and subname is NULL);
157 -- subcoltype$ update
158 update subcoltype$ set toid=ltinfo.toid where toid=(select oid$ from
159 obj$ where name=ltinfo.name and owner#=userid and type#=13 and subname is NULL);
160 -- dependency$ update
161 update dependency$ set p_obj#=ltinfo.objid, p_timestamp=ltinfo.stime
162 where p_obj#=(select obj# from obj$ where name=ltinfo.name and
163 owner#=userid and type#=13 and subname is NULL)
164 and d_obj# in (select obj# from obj$ where type#=2);
165 end loop;
166
167 -- check to make sure that none of the table are now dependent on schema1 types
168 -- do this only if check_update is TRUE
169 if (check_update = TRUE) then
170 open c6;
171 j := 0;
172 fetch c6 into j;
173 if (j=1) then
174 allgood := 0;
175 close c6;
176 rollback;
177 goto error3;
178 end if;
179 end if;
180
181 -- all is well ...
182 commit;
183
184 -- clear SGA
185 execute immediate 'alter system flush shared_pool';
186
187 <<error1>>
188 if (allgood = 0) then
189 -- ah, the hashcode check failed ...
190 dbms_output.put_line('Type ' || ltinfo.name ||
191 ' failed structural sanity check');
192 end if;
193
194 allgood := 1;
195
196 <<error2>>
197 if (allgood = 0) then
198 -- version mismatch ...
199 dbms_output.put_line('Versions do not match for type ' || ltinfo.name);
200 end if;
201
202 allgood := 1;
203
204 <<error3>>
205 if (allgood = 0) then
206 -- not all columns/tables updated
207 dbms_output.put_line('Not all tables/columns dependent on ' ||
208 schema1 || ' updated');
209 end if;
210
211 exception
212 when others then
213 if (allgood = 1) then
214 dbms_output.put_line('Error updating dictionary');
215 end if;
216 raise;
217
218 end; /* end of procedure update types */
219
220 -- Function SPLIT_SOURCE
221 -- This function will take 3 arguments, schema name, type name and an OUT
222 -- argument sources and will split the source$ entry for the latest version
223 -- of the type to its CREATE and ALTERs and will return it in sources, with
224 -- with the corresponding obj#. The number or split sources will be the return
225 -- value of the functions.
226 -- The obj# here can be used to determine the order of execution of connected
227 -- types. For example if t2 is dependent on t1 and the order is create t1,
228 -- create t2, alter t1, alter t2, then to get the right version for t2 we need
229 -- to execute in that order, which can be got from obj#.
230
231 function split_source
232 (tschema char, tname char, sources OUT dbms_objects_utils_tsource)
233 return number is
234
235 source varchar2(32767);
236 new_source varchar2(32767);
237 tmpsource_line varchar2(4000);
238 source_line varchar2(4000);
239 newsource_line varchar2(4000);
240 sourceelem dbms_objects_utils_tselem;
241 objid number;
242 line_len number;
243 total_lines number;
244 obj_id number;
245 total_len number;
246 i number;
247 src_pos number;
248 source_len number;
249 new_pos number;
250 j number;
251 line_no number;
252
253 cursor c1 is
254 select obj# from obj$ where name= tname and owner#=
255 (select user# from user$ where name=tschema)
256 and type#=13 and subname is null;
257
258 cursor c2 is
259 select line, source, length(source) from source$ where obj#= obj_id;
260
261 cursor c3 is
262 select obj# from obj$ o, type$ t where t.version#=i and o.name=tname and
263 o.oid$=t.tvoid and o.type#=13;
264
265 begin
266
267 -- get the obj#
268 open c1;
269 fetch c1 into obj_id;
270 close c1;
271
272 -- Get the type's source entries
273 total_len := 0;
274 source := '';
275 new_source := '';
276 -- First get the total no: of lines
277 select count(*) into total_lines from source$ where obj# = obj_id;
278 OPEN C2;
279 LOOP
280 FETCH C2 INTO line_no, tmpsource_line, line_len;
281 EXIT WHEN c2%notfound;
282 -- First get rid of the newline, if existing. That would be the case for all
283 -- except the last line.
284 IF (line_no < total_lines) THEN
285 line_len := line_len-1;
286 END IF;
287 source_line := substr(tmpsource_line, 1, line_len);
288 source := concat(source, source_line);
289 total_len := total_len + line_len;
290 END LOOP;
291 CLOSE C2;
292 -- store away total lines
293 total_lines := line_no;
294
295
296 -- Go through the source and split it based on seeing alter
297 src_pos := 1;
298 i := 1;
299 -- initialize with a create
300 sources := dbms_objects_utils_tsource(dbms_objects_utils_tselem(0,'create '),dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''),dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''), dbms_objects_utils_tselem(0,''));
301
302
303 WHILE (src_pos < total_len) LOOP
304
305 /* Ok, where's the alter. */
306 new_pos := instr(upper(source),' ALTER ', src_pos+3, 1);
307 /* If no alter, copy till end */
308 if (new_pos = 0) then
309 if (i = 1) then
310 sources(i).source := concat(sources(i).source, substr(source, src_pos, total_len));
311 else
312 sources(i).source := substr(source, src_pos, total_len);
313 end if;
314 exit;
315 end if;
316
317 /* copy the source until ALTER. This is one create type/alter type */
318 if (i = 1) then
319 sources(i).source := concat(sources(i).source,
320 substr(source, src_pos, (new_pos-src_pos+1)));
321 else
322 sources(i).source := substr(source, src_pos, (new_pos-src_pos+1));
323 end if;
324 -- get the obj# for the version
325 open c3;
326 fetch c3 into sources(i).objid;
327 close c3;
328
329 i := i+1;
330
331 src_pos := new_pos+1;
332 END LOOP;
333
334 -- get the obj# for latest version
335 open c3;
336 fetch c3 into sources(i).objid;
337 close c3;
338
339 -- print out the type sources
340 -- for j in 1..i loop
341 -- dbms_output.put_line('* ' || to_char(j) || ' : ' ||
342 -- sources(j).objid || ' ' || sources(j).source);
343 -- end loop;
344
345 return i;
346
347 end; /* end of split_source */
348
349 -- Procedure recompile_types
350 -- This procedure will take in an array (nested table) of type name and
351 -- invalidate them with status 6. This status will keep the spec timestamp
352 -- when recompiling, this keeping the dependencies valid. This is used during
353 -- upgrade/downgrade predominently and the only way to recompile a type with
354 -- table dependents. Afterwards this will call utl_recomp.recomp_parallel
355 -- to revalidate the invalidated types.
356
357 procedure recompile_types(
358 names dbms_objects_utils_tnamearr) is
359 i number;
360 numtypes number;
361 temp number;
362 closec boolean := FALSE;
363 typname dbms_objects_utils_tname;
364
365 cursor c1 is
366 select 1 from sys.obj$ o,sys.type$ t1 ,sys.user$ u where o.oid$=t1.tvoid and
367 o.name=typname.typname and o.subname is NULL and o.OWNER# = USER# and
368 u.name= typname.schema and substr(t1.hashcode,1,1)='2';
369
370 begin
371
372 -- invalidate the types
373 numtypes := 0;
374 for i in names.first .. names.last loop
375 if (closec = TRUE) then
376 close c1;
377 end if;
378 closec := TRUE;
379 typname := names(i);
380 dbms_output.put_line('Processing ' || typname.schema || '.' ||
381 typname.typname);
382 -- see if this one is in vsn 1 hashcode
383 open c1;
384 fetch c1 into temp;
385 continue when c1%notfound;
386 close c1;
387 closec := FALSE;
388
389 -- increment the count of types invalidated
390 numtypes := numtypes+1;
391 dbms_output.put_line('Invalidating ' || typname.schema || '.' ||
392 typname.typname);
393 update obj$ set status=6 where type#=13 and subname is null and
394 name=typname.typname and owner#=(select user# from user$ where
395 name=typname.schema);
396 end loop;
397 commit;
398
399 -- if no types were invalidated, exit
400 if (numtypes = 0) then
401 goto end1;
402 end if;
403
404 -- clear SGA
405 execute immediate 'alter system flush shared_pool';
406
407 -- now recompile
408 utl_recomp.recomp_parallel(null);
409
410 /*
411 for i in names.first .. names.last loop
412 typname := names(i);
413 execute immediate 'ALTER TYPE ' || typname.schema || '.' ||
414 typname.typname || ' compile specification reuse settings';
415 end loop;
416 */
417
418 <<end1>>
419 numtypes := 0;
420
421 end; /* end of procedure recompile_types */
422
423 -- invoked during type owner migration to update object cache
424 PROCEDURE owner_migrate_update_tdo
425 (toid raw, new_owner varchar2) IS
426 LANGUAGE C
427 NAME "OWNER_MIGRATE_TDO"
428 LIBRARY UTL_OBJECTS_LIB
429 parameters(toid OCIRaw, new_owner String);
430
431 -- invoked during type owner migration to update type hashcode
432 FUNCTION owner_migrate_update_hashcode
433 (toid raw)
434 return raw IS
435 LANGUAGE C
436 NAME "OWNER_MIGRATE_HC"
437 LIBRARY UTL_OBJECTS_LIB
438 parameters(toid OCIRaw,
439 return OCIRaw);
440
441 end; /* end of package dbms_objects_apps_utils */
[Home] [Help]