1 PACKAGE BODY AD_ZD_PREP as
2 /* $Header: ADZDPRPB.pls 120.46.12020000.12 2013/05/20 02:28:33 sstomar ship $ */
3
4 c_package constant varchar2(80) := 'ad.plsql.ad_zd_prep.';
5 g_apps_ne_schema constant varchar2(30) :='APPS_NE';
6 g_xla_schema constant varchar2(10) :='XLA';
7
8 type col_owners is table of number index by varchar2(150);
9 type udt_obj is record (owner varchar2(30),
10 type_name varchar2(30) );
11 type evolved_type_src_rec is record
12 ( owner varchar2(30),
13 type_name varchar2(30),
14 objid number ,
15 source varchar2(4000)
16 );
17
18 -- store udt and source udt owner
19 type udt_obj_t is table of udt_obj index by binary_integer;
20 type evolved_type_src_tab is table of evolved_type_src_rec index by binary_integer;
21
22 -- Package level global variables
23 -- ***********************************************************************
24 g_udt_obj_list udt_obj_t;
25 g_udt_obj_indx pls_integer := 0;
26 -- List of source obj of evolved-types
27 g_evolved_types_src_list evolved_type_src_tab;
28 g_evolved_type_index number;
29
30 g_obj_list_to_recompile sys.dbms_objects_utils_tnamearr := sys.dbms_objects_utils_tnamearr();
31
32 -- ************************************************************************
33 --
34 -- Private: Utility or Validation APIs
35 --
36 -- **************************************************************************
37
38 -- log shortcut
39 procedure log(x_module varchar2,
40 x_log_type varchar2,
41 x_message varchar2 ) is
42 begin
43 ad_zd_log.Message( x_module=>x_module, x_log_type => x_log_type, x_message => x_message );
44 end;
45
46 --
47 -- execute dynamic SQL statement
48 -- x_sql - statement to execute
49 -- x_log_mod - calling module (for logging)
50 -- x_ignore - ignore errors
51 --
52 procedure exec(X_SQL in clob, X_LOG_MOD in varchar2, X_IGNORE in boolean default false)
53 is
54 l_module varchar2(80) := c_package || 'exec';
55 begin
56 ad_zd.exec(x_sql, x_log_mod, x_ignore);
57 exception
58 when others then
59 -- Ignore DROP TYPE error but log error at STATEMENT level.
60 -- -02303: cannot drop or replace a type with type or table dependents
61 if(sqlcode= -04043 or sqlcode = -02303) then
62 log(l_module, 'STATEMENT', 'ERROR: ->[' || substr(sqlerrm,1,400) || ' ] ');
63 elsif (not x_ignore ) then
64 log(l_module, 'ERROR', 'ERROR: ->['|| substr(sqlerrm,1,400) || ' ] ' );
65 raise;
66 else
67 log(l_module, 'ERROR', 'ERROR: ->['|| substr(sqlerrm,1,400) || ' ] ' );
68 end if;
69 end exec;
70
71 -- Calls ad_zd_parallel_exec.load with same parameters.
72 procedure LOAD(
73 X_PHASE varchar2,
74 X_SQL clob)
75 is
76 begin
77 -- SQLs from DB Prep flow can NOT be duplicate, so
78 -- should not be checked for duplicity i.e. x_unique => false.
79 ad_zd_parallel_exec.load(x_phase, x_sql, false);
80 end;
81
82 --
83 --
84 -- INTERNAL Function to remove hardcoded schema names from the input string
85 --
86 -- it looks for pattern matching "<schema name>.<something>" and if found it removes
87 -- the hard coded schema name from the input string.
88 --
89 function REMOVE_SCHEMA_QUALIFIERS(X_DDL in clob) return clob
90 as
91 L_OUT clob;
92 L_INDEX number;
93 cursor C_EBS_SCHEMA is
94 select trim(oracle_username) oracle_username
95 from SYSTEM.fnd_oracle_userid
96 where read_only_flag in ('A','B', 'E', 'U', 'C')
97 order by 1;
98
99 begin
100
101 l_out := x_ddl;
102 for list in c_ebs_schema loop
103 -- Get the first index of ["Schema".] or [Schema.], for example: "APPS".UDT or APPS.UDT
104 -- in the given DDL.
105 l_index := REGEXP_INSTR(l_out,
106 '[[:space:]]+("?' || list.oracle_username || '"?)[.]', 1,1,0, 'i' );
107
108 -- Replace all such occurrences with space ' '
109 if(l_index > 0 ) then
110 l_out := REGEXP_REPLACE(l_out,
111 '[[:space:]]+("?' || list.oracle_username || '"?)[.]',
112 ' ',
113 l_index, 0, 'i' );
114 end if;
115 end loop;
116
117 return l_out;
118 end REMOVE_SCHEMA_QUALIFIERS;
119
120 --
121 --
122 -- Utility function to check whether a given type is an EVOLVED type in an schema or not.
123 --
124 function IS_TYPE_EVOLVED(X_OWNER in varchar2, X_NAME in varchar2 )
125 return varchar2
126 is
127 l_obj_cnt pls_integer :=0;
128 begin
129
130 select count(obj#) into l_obj_cnt
131 from sys.obj$ o
132 where owner# =(select user# from sys.user$ where name = x_owner)
133 and o.NAME = x_name
134 and o.type# = 13 --13 = TYPE,
135 and o.subname is not null; --only for evloved type
136
137 if(l_obj_cnt > 0 ) then
138 return 'Y';
139 else
140 return 'N';
141 end if;
142
143 end IS_TYPE_EVOLVED;
144
145
146 --
147 -- This function checks if TYPE and OWNER exist in the specified collection
148 --
149 function IS_TYPE_EXISTS_IN_LIST(X_OWNER varchar2,
150 X_NAME varchar2 ,
151 X_TYPE_LIST in udt_obj_t )
152 return boolean
153 is
154 is_exists boolean := false;
155 begin
156 for idx in 1..x_type_list.count loop
157 if( x_type_list(idx).owner = x_owner and
158 x_type_list(idx).type_name = x_name ) then
159 is_exists := true;
160 end if;
161 end loop;
162 return is_exists;
163 end IS_TYPE_EXISTS_IN_LIST;
164
165 --
166 -- **********************************************************
167 -- Utility function to check whether a given type exists or not in a schema?
168 --
169 --
170 function IS_TYPE_EXISTS(X_OWNER varchar2, X_NAME varchar2 ) return boolean
171 is
172 l_obj_cnt pls_integer :=0;
173 begin
174
175 select count(obj#) into l_obj_cnt
176 from sys.obj$ o
177 where owner# =(select user# from sys.user$ where name = x_owner)
178 and o.NAME = x_name
179 and o.type# = 13;
180
181 if(l_obj_cnt > 0 ) then
182 return true;
183 else
184 return false;
185 end if;
186
187 end IS_TYPE_EXISTS;
188
189 --
190 -- **********************************************************
191 -- Utility function to check whether a given type BODY exists or not ?
192 --
193 --
194 function IS_TYPE_BODY_EXISTS(X_OWNER varchar2, X_NAME varchar2 ) return boolean
195 is
196 l_obj_cnt pls_integer :=0;
197 begin
198
199 select count(obj#) into l_obj_cnt
200 from sys.obj$ o
201 where owner# =(select user# from sys.user$ where name = x_owner)
202 and o.NAME = x_name
203 and o.type# = 14; -- 14: TYPE BODY
204
205 if(l_obj_cnt > 0 ) then
206 return true;
207 else
208 return false;
209 end if;
210
211 end IS_TYPE_BODY_EXISTS;
212
213 --
214 -- Procedure to compile Non editionable apps ( apps_ne ) schema
215 --
216 --
217 procedure compile_ne_schema(x_exec boolean) as
218 l_module varchar2(80) := c_package || '.compile_ne_schema';
219 begin
220 if(x_exec) then
221 exec('begin dbms_utility.compile_schema(schema=>'''||g_apps_ne_schema||'''); end;', l_module);
222 else
223 load(x_phase=> ad_zd_parallel_exec.c_phase_compile_type,
224 x_sql => 'begin dbms_utility.compile_schema(schema=>'''|| g_apps_ne_schema||'''); end;');
225 end if;
226 end compile_ne_schema;
227
228 --
229 -- This procedure recompiles TYPES from source schema so that new db version compatible
230 -- hash-code is generated. This is specifically for TYPEs compiled in Pre-11G DB.
231 --
232 -- This API uses the type list prepared from:
233 -- COPY_TYPE
234 -- COPY_EVOLVED_TYPE APIs.
235 --
236 -- Usage:
237 -- Should be called after call of dependent (COPY_TYPE, COPY_EVOLVED_TYPE ) are being
238 -- called.
239 --
240 procedure recompile_types as
241 l_module varchar2(80) := c_package || '.recompile_types';
242 begin
243 log(l_module, 'PROCEDURE', 'begin');
244 --
245 -- Bug 12747238 : If hash-code conforming to 10g db
246 -- then sys.dbms_objects_utils.update_types will not work.
247 -- Solution is: recompile all such UDTs then it will have 11g db compatible hash-code.
248 --
249 if(g_obj_list_to_recompile is not null and
250 g_obj_list_to_recompile.count > 0 ) then
251 log(l_module, 'STATEMENT', 'Recompiling Types');
252 sys.dbms_objects_utils.recompile_types(names=> g_obj_list_to_recompile);
253 end if;
254 log(l_module, 'PROCEDURE', 'end');
255 end recompile_types;
256
257
258 --
259 -- This procedure populates g_obj_list_to_recompile
260 --
261 procedure put_obj_to_recompile(x_owner varchar2, x_type_name varchar2 )
262 is
263 l_type_names sys.dbms_objects_utils_tname;
264 begin
265
266 l_type_names := sys.dbms_objects_utils_tname(x_owner, x_type_name);
267 g_obj_list_to_recompile.extend;
268 g_obj_list_to_recompile(g_obj_list_to_recompile.last):= l_type_names;
269
270 end put_obj_to_recompile;
271
272 -- **********************************************************************************
273 --
274 -- E-business Suite Database Preparation APIs for Editionning .
275 --
276 --
277 -- ***********************************************************************************
278
279 --
280 -- Create patch service and logon trigger
281 --
282 procedure CREATE_PATCH_SERVICE
283 is
284 l_module varchar2(80) := c_package || 'create_patch_service';
285 l_exists number;
286
287 begin
288 log(l_module, 'PROCEDURE', 'begin');
289
290 -- Since service name is case-insensitive, even sqlplus accepts that.
291 -- so check service as case-insensitive
292 select count(service_id) into l_exists
293 from dba_services
294 where upper(name)=upper(c_patch_service);
295
296 if l_exists = 0 then
297 log(l_module, 'EVENT', 'Creating ' || c_patch_service || ' database service');
298 dbms_service.create_service(c_patch_service, c_patch_service);
299 end if;
300
301 -- start patch service if needed
302 select count(service_id) into l_exists
303 from sys.v_$active_services
304 where name=c_patch_service;
305
306 if l_exists = 0 then
307 log(l_module, 'EVENT', 'Starting ' || c_patch_service || ' database service');
308 dbms_service.start_service(c_patch_service);
309 end if;
310 log(l_module, 'PROCEDURE', 'end');
311 end CREATE_PATCH_SERVICE;
312
313
314 --
315 -- Migrates XML schema from E-business Suite users and known or seeded Oracle
316 -- XML schemas from SYSTEM to APPS_NE database user.
317 --
318 procedure MOVE_XML_SCHEMAS
319 is
320 L_MODULE varchar2(80) := c_package|| 'move_xml_schemas';
321 L_SCHEMA_OWNER varchar2(30);
322 L_SCHEMA_URL varchar2(30);
323 L_FOUND boolean := false;
324 L_COUNT_PRE number;
325 L_COUNT_POST number;
326
327 cursor C_XML_SCHEMAS is
328 /*
329 --TODO: Waiting for George's reply on it.
330 --
331 -- adding DISTINCT as two tabls might refer same xml schema
332 select distinct xmls.owner, xmls.schema_url
333 from dba_dependencies dep,
334 dba_xml_schemas xmls
335 where dep.type='TABLE'
336 and dep.owner in
337 (select oracle_username
338 from fnd_oracle_userid fu
339 where fu.read_only_flag in ('A','B', 'E', 'U', 'C') )
340 and dep.referenced_type = 'XML SCHEMA'
341 and dep.referenced_name = xmls.int_objname
342 -- XML schema owner either is SYSTEM or any e-biz user
343 and ((xmls.owner = 'SYSTEM') or
344 xmls.owner in
345 (select oracle_username
346 from fnd_oracle_userid fu
347 where fu.read_only_flag in ('A','B', 'E', 'U', 'C')));
348 */
349 select owner, schema_url
350 from dba_xml_schemas
351 where (
352 /* oracle seeded xml schemas in SYSTEM user */
353 (schema_url in (
354 'http://isetup.oracle.com/2006/diffresultdata.xsd' ,
355 'http://isetup.oracle.com/2006/selectionsets.xsd' ,
356 'http://isetup.oracle.com/2006/reporterdata.xsd' )
357 and owner='SYSTEM'
358 )
359 or
360 owner in
361 (
362 select oracle_username
363 from fnd_oracle_userid fu
364 where fu.read_only_flag in ('A','B', 'E', 'U', 'C')
365 )
366 );
367
368 begin
369
370 log(l_module, 'PROCEDURE', 'begin');
371
372 -- Clear off rows from xdb table
373 delete from sys.xdb$moveSchemaTab;
374 commit;
375
376 for xml_schema in c_xml_schemas loop
377 insert into sys.xdb$moveSchemaTab(schema_url, schemaownerfrom, schemaownerto, schema)
378 values(xml_schema.schema_url, xml_schema.owner, g_apps_ne_schema, null);
379
380 log(l_module, 'STATEMENT', 'Inserted XML schema into sys.xdb$moveSchemaTab ('||
381 xml_schema.schema_url || ',' || xml_schema.owner ||
382 ',' || g_apps_ne_schema ||', null)' );
383
384 -- re-using same var to check if any XML SCHEMA needs to be migrated?
385 l_found := true;
386 end loop;
387
388 if( l_found ) then
389 -- reset
390 l_found := false;
391
392 -- Pre-migration invalid count only in current edition
393 select count(1) into l_count_pre
394 from dba_invalid_objects;
395
396 log(l_module, 'EVENT', 'Move xml schema: Invoke sys.xdb_migrateschema.moveSchemas ');
397
398 sys.xdb_migrateschema.moveSchemas;
399 log(l_module, 'STATEMENT', 'Done with sys.xdb_migrateschema.moveSchemas');
400
401 -- Post migration invalid count only in current edition
402 select count(1) into l_count_post
403 from dba_invalid_objects;
404
405 -- Check if xml schemas are still owned by source user?
406 open c_xml_schemas;
407 fetch c_xml_schemas into l_schema_owner, l_schema_url;
408 l_found := c_xml_schemas%found;
409 close c_xml_schemas;
410
411 if(l_found or (l_count_post > l_count_pre) ) then
412 ad_zd.error(l_module, 'XML SCHEMAS not migrated to target user successfully ' ||
413 'or migration process introduced some new Invalid-objects');
414 end if;
415
416 -- Clean up DB table; in case of error, we want to preserve it for debugging.
417 delete from sys.xdb$moveSchemaTab;
418 commit;
419
420 end if;
421 log(l_module, 'PROCEDURE', 'end');
422 exception
423 when others then
424 ad_zd.error(l_module, substr(sqlerrm, 1, 255));
425
426 end MOVE_XML_SCHEMAS;
427
428
429
430 --
431 -- procedure to stop an AQ.
432 --
433 procedure STOP_QUEUE(X_QUEUE_NAME varchar2) as
434 l_module varchar2(80) := c_package || 'stop_queue';
435 begin
436 log(l_module, 'PROCEDURE', 'begin: '|| x_queue_name);
437 exec('begin dbms_aqadm.stop_queue('''|| x_queue_name|| '''); end;', l_module);
438 log(l_module, 'PROCEDURE', 'end');
439 end stop_queue;
440
441
442 -- procedure to start an AQ.
443 procedure START_QUEUE(X_QUEUE_NAME in varchar2) as
444 l_module varchar2(80) := c_package || 'start_queue';
445 begin
446 log(l_module, 'PROCEDURE', 'begin: ' || x_queue_name);
447 exec('begin dbms_aqadm.start_queue('''|| x_queue_name||'''); end;', l_module);
448 log(l_module, 'PROCEDURE', 'end');
449 end start_queue;
450
451
452 -- procedure to drop an AQ.
453 procedure DROP_QUEUE (X_QUEUE_NAME in varchar2) as
454 l_module varchar2(80) := c_package || 'drop_queue';
455 begin
456 log(l_module, 'PROCEDURE', 'begin: ' || x_queue_name);
457 exec('begin dbms_aqadm.drop_queue('''|| x_queue_name||'''); end;', l_module);
458 log(l_module, 'PROCEDURE', 'end');
459 end drop_queue;
460
461
462 --
463 -- Stops and then drops specified queue
464 --
465 procedure DROP_QUEUES(X_OWNER varchar2, X_QUEUE_NAME varchar2)
466 as
467 l_module varchar2(80) := c_package || 'drop_queues';
468 begin
469 log(l_module, 'PROCEDURE', 'begin: ' || x_owner || '.'|| x_queue_name);
470 stop_queue('"'||x_owner ||'"."' || x_queue_name ||'"' );
471 drop_queue('"'||x_owner ||'"."' || x_queue_name || '"');
472 log(l_module, 'PROCEDURE', 'end');
473 end DROP_QUEUES;
474
475 --
476 -- Drops all temporary queue which had been left over due to
477 -- some bug which was fixed via patch: 8284764:R12.XLA.A or 9131790:R12.XLA.B
478 -- The AQs still exist after the patch applied so dropping them.
479 --
480 procedure DROP_TEMP_QUEUES is
481
482 cursor c_queues is
483 select aq.owner , aq.name
484 from dba_queues aq
485 where aq.owner = g_xla_schema
486 and (aq.name like 'XLA_%_DOC_Q' or aq.name like 'XLA_%_COMP_Q');
487
488 begin
489
490 for queue in c_queues loop
491 load(x_phase => ad_zd_parallel_exec.c_phase_drop_unused_object,
492 x_sql => 'begin ad_zd_prep.drop_queues('
493 || '''' || queue.owner || ''', '''
494 || queue.name || '''); end; ');
495 end loop;
496 end DROP_TEMP_QUEUES;
497
498
499 -- Update table references to APPS_NE type
500 procedure FIX_TYPES(X_SOURCE_SCHEMA varchar2,
501 X_TARGET_SCHEMA varchar2)
502 is
503 L_MODULE varchar2(80) := c_package || 'fix_types';
504
505 cursor C_TYPES is
506 with p(referenced_owner, referenced_name) as
507 (
508 select referenced_owner, referenced_name
509 from dba_dependencies d
510 where d.type = 'TABLE'
511 and d.name = upper (name)
512 and d.referenced_type = 'TYPE'
513 and d.referenced_name = upper(d.referenced_name)
514 and (
515 -- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
516 d.referenced_owner in
517 ( select oracle_username
518 from SYSTEM.fnd_oracle_userid
519 where read_only_flag in ('A','B', 'E', 'U', 'C')
520 )
521 or
522 exists
523 ( select 1
524 from dba_dependencies
525 where owner = d.referenced_owner
526 and name = d.referenced_name
527 and type = d.referenced_type
528 and referenced_type = 'TYPE'
529 and referenced_owner in
530 (
531 select oracle_username
532 from SYSTEM.fnd_oracle_userid
533 where read_only_flag in ('A','B', 'E', 'U', 'C')
534 )
535 )
536 )
537 /* filter out XDB types */
538 and not exists ( select null
539 from xdb.xdb$element e
540 where e.xmldata.property.sqltype = d.referenced_name )
541 and not exists (select null
542 from xdb.xdb$element e
543 where e.xmldata.property.sqlcolltype = d.referenced_name)
544 union all
545 select d.referenced_owner referenced_owner,
546 d.referenced_name referenced_name
547 from dba_dependencies d,
548 p
549 where d.referenced_type = 'TYPE'
550 and d.referenced_name = upper (d.referenced_name )
551 and d.name = p.referenced_name
552 and d.owner = p.referenced_owner
553 and d.referenced_owner in
554 ( select oracle_username
555 from system.fnd_oracle_userid
556 where read_only_flag in ('A','B', 'E', 'U', 'C')
557 )
558 )
559 cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
560 select distinct referenced_owner, referenced_name
561 from p where cyclemarker = 'N';
562
563
564 begin
565 log(l_module, 'PROCEDURE', 'begin: '||x_source_schema||', '||x_target_schema);
566
567 for l_type in c_types loop
568 if ((l_type.referenced_owner = x_source_schema ) and
569 (is_type_exists(x_target_schema, l_type.referenced_name)= false)) then
570
571 log(l_module, 'ERROR', 'User Defined Type ' || x_target_schema || '.' || l_type.referenced_name ||
572 ' does not exist');
573
574 -- Raise critical error code 20978 from defined unexpected errors (in ADZDPEXS.pls ) instead
575 -- of defining a new one: -20978 /* Cannot drop a type with table dependents */
576 -- so that parallel worker won't execute subsequent jobs.
577 raise_application_error(-20978, 'User Defined Type ' || x_target_schema || '.' || l_type.referenced_name ||
578 ' does not exist');
579 end if;
580 end loop;
581
582 log(l_module, 'EVENT', 'Update TYPE reference from '||x_source_schema||' to '||x_target_schema);
583 -- Without any specific TYPE
584 sys.dbms_objects_utils.update_types
585 ( schema1 => x_source_schema,
586 schema2 => x_target_schema,
587 typename => null,
588 check_update => true ) ;
589
590 --NOTE: Not adding a CHECK here to make sure that none of the table are now dependent
591 -- on x_source_schema types, reason being: since sys.dbms_objects_utils.update_types
592 -- checkes if all TYPES from source schema are created in target scheam but if source
593 -- schema is SYSTEM then it does not fall true, so data-dictionary updates still would be
594 -- half-way, so let that validation be handled withih FIX_TYPE API.
595
596 log(l_module, 'PROCEDURE', 'end');
597 end FIX_TYPES;
598
599
600 -- Convert Editioned Type to equivalent Non-Editioned Type synonym
601 -- Update table references to NE type if needed
602 -- Convert dependant types, if needed
603 -- Drop type, replace with synonym to NE type
604 procedure FIX_TYPE(X_TYPE_OWNER varchar2, X_TYPE_NAME varchar2)
605 is
606 L_MODULE varchar2(80) := c_package || 'fix_type';
607 L_REF_OWNER varchar2(30);
608 L_REF_NAME varchar2(30);
609 L_FOUND boolean;
610
611 -- tables that reference the specified type
612 cursor C_TABLE_REFS(TYPE_OWNER varchar2, TYPE_NAME varchar2) is
613 select dep.owner, dep.name
614 from dba_dependencies dep
615 where dep.type = 'TABLE'
616 and dep.referenced_owner= type_owner
617 and dep.referenced_name = type_name
618 and dep.referenced_type = 'TYPE';
619
620 -- types that reference the specified type and have a replacement in APPS_NE
621 cursor C_TYPE_REFS(XX_TYPE_OWNER varchar2, XX_TYPE_NAME varchar2) is
622 select ot.owner, ot.name
623 from dba_dependencies ot,
624 dba_types nt
625 where ot.type = 'TYPE'
626 and ot.referenced_owner= xx_type_owner
627 and ot.referenced_name = xx_type_name
628 and ot.referenced_type = 'TYPE'
629 and nt.owner = g_apps_ne_schema
630 and nt.type_name = ot.name;
631
632 -- tables that references the specific type or its dependent type
633 cursor C_TABLE_REFS2(TYPE_OWNER varchar2, TYPE_NAME varchar2) is
634 select dep.owner, dep.name
635 from dba_dependencies dep
636 where dep.type = 'TABLE'
637 and dep.referenced_owner <> g_apps_ne_schema
638 start with
639 dep.referenced_owner = type_owner
640 and dep.referenced_name = type_name
641 and dep.referenced_type = 'TYPE'
642 connect by nocycle
643 prior dep.owner = dep.referenced_owner
644 and prior dep.name = dep.referenced_name
645 and prior dep.type = dep.referenced_type;
646
647 begin
648
649 log(l_module, 'PROCEDURE', 'begin: '||x_type_owner||'.'||x_type_name);
650
651 -- check for table references to type
652 open c_table_refs(x_type_owner, x_type_name);
653 fetch c_table_refs into l_ref_owner, l_ref_name;
654 l_found := c_table_refs%found;
655 close c_table_refs;
656
657 -- fix table references if any
658 if l_found then
659 -- update column types to new owner
660 -- Note: "check_update" paremeter must be false for single type update
661 log(l_module, 'EVENT', 'Fixing table references to '||x_type_owner||'.'||x_type_name);
662 sys.dbms_objects_utils.update_types
663 ( schema1 => x_type_owner,
664 schema2 => 'APPS_NE',
665 typename => x_type_name,
666 check_update => false ) ;
667 execute immediate 'alter system flush shared_pool';
668 commit;
669
670 -- re-check for table references to type
671 open c_table_refs(x_type_owner, x_type_name);
672 fetch c_table_refs into l_ref_owner, l_ref_name;
673 l_found := c_table_refs%found;
674 close c_table_refs;
675
676 -- if there are still references to the type, then we have failed
677 if l_found then
678 log(l_module, 'ERROR', 'Could not fix table references to '||x_type_owner||'.'||x_type_name);
679 raise_application_error(-20978, 'Could not fix table references to '||x_type_owner||'.'||x_type_name);
680 end if;
681 end if;
682
683 -- recursively drop any types that depend on this type and have NE replacements
684 for l_types in c_type_refs(x_type_owner, x_type_name) loop
685 log(l_module, 'STATEMENT', 'Fixing type reference: ' || l_types.owner||'.'||l_types.name);
686 fix_type(l_types.owner, l_types.name);
687 end loop;
688
689 -- verify type still exists (could be missing now due to concurrent threads)
690 if is_type_exists(x_type_owner, x_type_name) then
691
692 -- Make sure no table references to type or its dependent types
693 open c_table_refs2(x_type_owner, x_type_name);
694 fetch c_table_refs2 into l_ref_owner, l_ref_name;
695 l_found := c_table_refs2%found;
696 close c_table_refs2;
697
698 -- if there are still references to the type or its dependent types, then we have failed
699 if l_found then
700 log(l_module, 'ERROR', 'Could not fix table references to '||x_type_owner||'.'||x_type_name);
701 raise_application_error(-20978, 'Could not fix table references to '||x_type_owner||'.'||x_type_name);
702 end if;
703
704 -- drop the type, replace with synonym
705 log(l_module, 'EVENT', 'Converting Type to Synonym: '||x_type_owner||'.'||x_type_name);
706
707 exec('drop type "'||x_type_owner||'"."'||x_type_name||'" force', l_module, true);
708 exec('create or replace synonym "'||x_type_owner||'"."'||x_type_name||'" for '
709 ||'"APPS_NE"."'||x_type_name||'"', l_module, true);
710 end if;
711
712 log(l_module, 'PROCEDURE', 'end');
713 end FIX_TYPE;
714
715 --
716 -- The procedure works as follows:
717 -- Gets the DDL by using dbms_metadata and creates in APPS_NE schema
718 --
719 --
720 procedure COPY_TYPE(X_OWNER in varchar2,
721 X_NAME in varchar2,
722 X_NEW_OWNER in varchar2)
723 is
724 L_MODULE varchar2(80) := c_package || 'copy_type';
725 DM_H number;
726 DM_T number;
727 L_DDL clob;
728 L_EXIST pls_integer;
729 begin
730
731 log(l_module, 'PROCEDURE', 'begin: '||x_owner||'.'||x_name ||', '|| x_new_owner);
732
733 begin
734 select 1 into l_exist
735 from dba_types
736 where owner = x_owner
737 and type_name = x_name;
738 exception
739 when no_data_found then
740 log(l_module, 'STATEMENT', 'Type: '||x_owner||'.'||x_name || ' does not exist - noop');
741 return;
742 end;
743
744 dm_h := dbms_metadata.open('TYPE');
745 dbms_metadata.set_filter(dm_h, 'SCHEMA', X_OWNER);
746 dbms_metadata.set_filter(dm_h, 'NAME', X_NAME);
747 dbms_metadata.set_filter(dm_h, 'SPECIFICATION', true);
748 dbms_metadata.set_filter(dm_h, 'BODY', false);
749 dm_t := dbms_metadata.add_transform(dm_h, 'MODIFY');
750 dbms_metadata.set_remap_param(dm_t, 'REMAP_SCHEMA', X_OWNER, X_NEW_OWNER);
751 dm_t := dbms_metadata.add_transform(dm_h, 'DDL');
752
753 l_ddl := dbms_metadata.fetch_clob(dm_h);
754 dbms_metadata.close(dm_h);
755
756 if (l_ddl is not null ) then
757 l_ddl := remove_schema_qualifiers(l_ddl );
758 if(is_type_exists(x_new_owner, x_name) = false ) then
759 log(l_module, 'EVENT', 'Creating TYPE: '||x_new_owner||'.'||x_name);
760 exec(x_sql => l_ddl, x_log_mod => l_module, x_ignore=>true);
761 end if;
762 else
763 log(l_module, 'ERROR', 'dbms_metadata returned null for User-Defined-Type Specification: '||x_owner||'.'||x_name );
764 -- Shall we raise an error from here?
765 raise_application_error(-20996,
766 'dbms_metadata returned null for User-Defined-Type Specification: ' ||x_owner||'.'||x_name);
767 end if;
768 if(dbms_lob.istemporary(l_ddl) =1 ) then
769 dbms_lob.freetemporary(l_ddl);
770 end if;
771
772 -- STEP#2 Follow same steps for OBJECT Body
773 dm_h := dbms_metadata.open('TYPE');
774 dbms_metadata.set_filter(dm_h, 'SCHEMA', X_OWNER);
775 dbms_metadata.set_filter(dm_h, 'NAME', X_NAME);
776 dbms_metadata.set_filter(dm_h, 'SPECIFICATION', FALSE);
777 dbms_metadata.set_filter(dm_h, 'BODY', TRUE);
778
779 dm_t := dbms_metadata.add_transform(dm_h, 'MODIFY');
780 dbms_metadata.set_remap_param(dm_t, 'REMAP_SCHEMA', X_OWNER, X_NEW_OWNER);
781 dm_t := dbms_metadata.add_transform(dm_h, 'DDL');
782
783 l_ddl := dbms_metadata.fetch_clob(dm_h);
784 dbms_metadata.close(dm_h);
785
786 if (l_ddl is not null ) then
787 l_ddl := remove_schema_qualifiers(l_ddl);
788 if(is_type_body_exists(x_new_owner, x_name) = false ) then
789 log(l_module, 'EVENT', 'Creating TYPE BODY: '||x_new_owner||'.'||x_name);
790 exec(x_sql => l_ddl, x_log_mod => l_module, x_ignore=>true);
791 end if;
792 else
793 log(l_module, 'STATEMENT', 'dbms_metadata returned null for User-Defined-Type Body: '||x_owner||'.'||x_name );
794 end if;
795
796 if(dbms_lob.istemporary(l_ddl) =1 ) then
797 dbms_lob.freetemporary(l_ddl);
798 end if;
799
800 -- Grant execute priviledge to
801 exec(x_sql => 'GRANT EXECUTE ON "'||x_new_owner||'"."'||x_name||'" to PUBLIC', x_log_mod => l_module, x_ignore=>true);
802 log(l_module, 'PROCEDURE', 'end');
803 end COPY_TYPE;
804
805 --
806 -- Copy UDT columns of tables to APPS_NE
807 --
808 -- procedure to find all UDTs which are referenced by table columns and calling
809 -- procedure to copy them to APPSNE schema
810 --
811 procedure COPY_TYPES is
812 l_module varchar2(80) := c_package || 'copy_types';
813 l_flag boolean := false;
814
815 cursor all_types is
816 with p(referenced_owner, referenced_name) as
817 (
818 select referenced_owner, referenced_name
819 from dba_dependencies d
820 where d.type = 'TABLE'
821 and d.name = upper (name)
822 and d.referenced_type = 'TYPE'
823 and d.referenced_name = upper(d.referenced_name)
824 and (
825 -- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
826 d.referenced_owner in
827 ( select oracle_username
828 from SYSTEM.fnd_oracle_userid
829 where read_only_flag in ('A','B', 'E', 'U', 'C')
830 )
831 OR
832 exists
833 ( select 1
834 from dba_dependencies
835 where owner = d.referenced_owner
836 and name = d.referenced_name
837 and type = d.referenced_type
838 and referenced_type = 'TYPE'
839 and referenced_owner in
840 (
841 select oracle_username
842 from SYSTEM.fnd_oracle_userid
843 where read_only_flag in ('A','B', 'E', 'U', 'C')
844 )
845 )
846 )
847 -- /* filter out XDB types */
848 and not exists ( select null
849 from xdb.xdb$element e
850 where e.xmldata.property.sqltype = d.referenced_name )
851 and not exists (select null
852 from xdb.xdb$element e
853 where e.xmldata.property.sqlcolltype = d.referenced_name)
854
855 union all
856 select d.referenced_owner referenced_owner,
857 d.referenced_name referenced_name
858 from dba_dependencies d,
859 p
860 where d.referenced_type = 'TYPE'
861 and d.referenced_name = upper (d.referenced_name )
862 and d.name = p.referenced_name
863 and d.owner = p.referenced_owner
864 and d.referenced_owner in
865 ( select oracle_username
866 from system.fnd_oracle_userid
867 where read_only_flag in ('A','B', 'E', 'U', 'C')
868 )
869 )
870 cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
871 select distinct referenced_owner, referenced_name
872 from p where cyclemarker = 'N';
873
874 l_evolved_idx pls_integer := 0;
875
876 begin
877 log(l_module, 'PROCEDURE', 'begin');
878
879 for t in all_types loop
880 if( is_type_evolved(t.referenced_owner, t.referenced_name) <> 'Y') then
881 -- normal UDTs
882 load(x_phase => ad_zd_parallel_exec.c_phase_copy_type,
883 x_sql => 'begin ad_zd_prep.copy_type('''
884 || t.referenced_owner || ''', '''
885 || t.referenced_name || ''','''
886 || g_apps_ne_schema || '''); end;');
887
888 -- BUG 12747238 :
889 -- Source TYPES will recompiled during DDL generation time
890 -- to make sure that they have 11g conforming hash-code
891 -- before DDL execution starts.
892 put_obj_to_recompile(t.referenced_owner, t.referenced_name);
893
894 -- FIX_TYPE will check dependency, if exist, will try to fix before
895 -- dropping a type.
896 load(x_phase => ad_zd_parallel_exec.c_phase_fix_type,
897 x_sql => 'begin ad_zd_prep.fix_type('''
898 || t.referenced_owner || ''', '''
899 || t.referenced_name || '''); end; ');
900
901 end if;
902 l_flag := true;
903 end loop;
904
905 if(l_flag) then
906 -- Recompile source TYPES to make hash-code compatible if they were NOT before.
907 -- This API should be called during ddl-population time.
908 recompile_types;
909
910 -- STEP#3 : Adding it here also so, all UDT become VALID before EVOLVED type creation
911 log(l_module, 'STATEMENT', 'Populating compile_ne_schema(x_exec=>false) API.' );
912 compile_ne_schema(false);
913 end if;
914
915 log(l_module, 'PROCEDURE', 'end');
916 end COPY_TYPES;
917
918
919 --
920 -- Loads the DDLs into AD_ZD_DDL_HANDLER from the global evolved_type or
921 -- from sorted global list.
922 --
923 procedure INSTALL_EVOLVED_TYPES
924 as
925 l_idx number;
926 l_objid number;
927 l_flag boolean := true;
928 l_evolved_type_rec evolved_type_src_rec;
929 l_owner varchar2(30);
930 l_type_name varchar2(30);
931 l_source varchar(32676); -- max will be 4000 but
932 l_evolve_type_list udt_obj_t ;
933 l_evolve_type_index pls_integer;
934 l_module varchar2(80) := c_package || 'install_evolved_types';
935 begin
936
937 log(l_module, 'PROCEDURE', 'begin');
938 -- Generic algo.
939 -- Sorting based on [objid]
940 --
941 -- **********************************************************
942 while(l_flag) loop
943 l_flag := false;
944 for i in 1..(g_evolved_types_src_list.count-1) loop
945 if (g_evolved_types_src_list(i).objid > g_evolved_types_src_list(i+1).objid ) then
946 l_evolved_type_rec := g_evolved_types_src_list(i+1);
947 g_evolved_types_src_list(i+1) := g_evolved_types_src_list(i);
948 g_evolved_types_src_list(i) := l_evolved_type_rec;
949 l_flag := true;
950 end if;
951 end loop;
952 end loop;
953
954 -- **************************************************************
955
956 l_idx := g_evolved_types_src_list.FIRST;
957 l_evolve_type_index := 0;
958
959 while ( l_idx is not null ) loop
960 l_evolved_type_rec := g_evolved_types_src_list(l_idx);
961 l_source := l_evolved_type_rec.source;
962 -- It is possible that TYPE has been evolved / exists in APPS_NE schema but table referecne might
963 -- not have changed?
964 if(is_type_evolved(l_evolved_type_rec.type_name, g_apps_ne_schema ) ='Y') then
965 continue;
966 end if;
967 exec(l_source, l_module, true);
968
969 if(is_type_exists_in_list(l_evolved_type_rec.owner,
970 l_evolved_type_rec.type_name ,
971 l_evolve_type_list ) = false ) then
972
973 l_evolve_type_index := l_evolve_type_index + 1;
974 l_evolve_type_list(l_evolve_type_index).owner := l_evolved_type_rec.owner;
975 l_evolve_type_list(l_evolve_type_index).type_name := l_evolved_type_rec.type_name;
976 end if;
977
978 l_idx := g_evolved_types_src_list.next(l_idx);
979
980 end loop;
981
982 -- not optimized but okay to re-iterate .
983 for i in 1..(l_evolve_type_list.count ) loop
984 l_owner := l_evolve_type_list(i).owner;
985 l_type_name := l_evolve_type_list(i).type_name;
986
987 exec('GRANT EXECUTE ON "'||g_apps_ne_schema||'"."' || l_type_name || '" TO PUBLIC ', l_module, true);
988
989 load(x_phase=> ad_zd_parallel_exec.c_phase_fix_type,
990 x_sql => 'begin ad_zd_prep.fix_type('
991 || '''' || l_owner || ''', '''
992 || l_type_name || '''); end; ');
993
994 end loop;
995 log(l_module, 'PROCEDURE', 'end');
996 end INSTALL_EVOLVED_TYPES;
997
998 --
999 -- Gets the DDL of a given Evolved-Types by using
1000 -- sys.dbms_objects_utils.split_source() and replaces
1001 -- source schemas with APPS_NE.
1002 --
1003 -- TODO: If an APPS.evolved type dependent on another E-biz schema
1004 -- e.g. ECX then we need to check how dbms_objects_utils.split_source()
1005 -- returns and may be we have to replace them.
1006 --
1007 procedure COPY_EVOLVED_TYPE (X_OWNER varchar2,
1008 X_NAME varchar2,
1009 X_NEW_OWNER varchar2 )
1010 as
1011 e_source sys.dbms_objects_utils_tsource;
1012 l_source varchar2(4000);
1013 l_objid number;
1014 sql_count number; /* number of DDLs returned in e_source for an EVOLVED type */
1015 l_index number;
1016 l_module varchar2(80) := c_package || 'copy_evolved_type';
1017 begin
1018
1019 log(l_module, 'PROCEDURE', 'begin: '||x_owner||'.'||x_name || ', '|| x_new_owner);
1020 sql_count := sys.dbms_objects_utils.split_source(x_owner, x_name, e_source);
1021
1022 -- For each evolved type
1023 for cntr in 1..sql_count loop
1024 l_source := e_source(cntr).source;
1025 l_objid := e_source(cntr).objid;
1026 l_index := REGEXP_INSTR(l_source,
1027 '[[:space:]]*("?' || x_owner || '"?)[.]?"?'|| x_name ||'"?[[:space:]]',
1028 1,1,0, 'i' ) ;
1029
1030 if(l_index > 0 ) then
1031 l_source := REGEXP_REPLACE(l_source,
1032 '[[:space:]]*("?' || x_owner || '"?)[.]?"?'|| x_name ||'"?[[:space:]]',
1033 ' "' || x_new_owner || '"."' || x_name || '" ' ,
1034 1,1,'i' ) ;
1035 else
1036 -- No <schema.type> exist, may be only TYPE name, replace with "APPS_NE"."TYPE"
1037 --
1038 l_source := REGEXP_REPLACE(l_source,
1039 '[[:space:]]*("?' || x_name ||'"?)[[:space:]]',
1040 ' "' || x_new_owner || '"."' || x_name || '" ' ,
1041 1,1,'i' ) ;
1042
1043 end if;
1044
1045 -- insert in GLOBAL LIST to be used by INSTALL_EVOLVED_TYPES
1046 g_evolved_type_index := g_evolved_type_index + 1;
1047 g_evolved_types_src_list(g_evolved_type_index).owner := x_owner ;
1048 g_evolved_types_src_list(g_evolved_type_index).type_name := x_name;
1049 g_evolved_types_src_list(g_evolved_type_index).objid := l_objid;
1050 g_evolved_types_src_list(g_evolved_type_index).source := l_source ;
1051 end loop;
1052 log(l_module, 'PROCEDURE', 'end');
1053 end COPY_EVOLVED_TYPE;
1054
1055 --
1056 --
1057 -- Get all evolved type referenced by tables as column type
1058 -- NOTE: Evolved-type can be created properly only when ALL DEPENDENT
1059 -- TYPES have been created and are VALID. Otherwise ALTER DDL
1060 -- statement on evolved type will fail.
1061 --
1062 procedure COPY_EVOLVED_TYPES
1063 as
1064 l_module varchar2(80) := c_package || 'copy_evolved_types';
1065 l_evolved_type_list udt_obj_t;
1066 l_index pls_integer :=0;
1067
1068 -- Since this SQL is very slow, so better to get the list of evolved types from
1069 -- COPY_TYPES API (owner, evolved_type_name) but this API will have dependency on
1070 -- COPY_TYPES API i.e. COPY_TYPES should be run before this API.
1071 --
1072 cursor all_evolved_types is
1073 with p(referenced_owner, referenced_name) as
1074 (
1075 select referenced_owner, referenced_name
1076 from dba_dependencies d
1077 where type = 'TABLE'
1078 and name = upper ( name )
1079 and referenced_type = 'TYPE'
1080 and referenced_name = upper (referenced_name)
1081 and (
1082 -- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
1083 referenced_owner in
1084 ( select oracle_username
1085 from SYSTEM.fnd_oracle_userid
1086 where read_only_flag in ('A','B', 'E', 'U','C')
1087 )
1088 or
1089 exists
1090 ( select 1
1091 from dba_dependencies
1092 where owner = d.referenced_owner
1093 and name = d.referenced_name
1094 and type = d.referenced_type
1095 and referenced_type = 'TYPE'
1096 and referenced_owner in
1097 (
1098 select oracle_username
1099 from SYSTEM.fnd_oracle_userid
1100 where read_only_flag in ('A','B', 'E', 'U', 'C')
1101 )
1102 )
1103 )
1104 -- Only take care evolved types i.e. exclude normal UDT
1105 --
1106 and exists
1107 ( select 1
1108 from sys.obj$ o
1109 where owner# =(select user# from sys.user$ where name = referenced_owner)
1110 and o.NAME = referenced_name
1111 and o.type# = 13
1112 and o.subname is not null
1113 )
1114 /* filter out XDB types */
1115 and not exists ( select null
1116 from xdb.xdb$element e
1117 where e.xmldata.property.sqltype = d.referenced_name )
1118 and not exists (select null
1119 from xdb.xdb$element e
1120 where e.xmldata.property.sqlcolltype = d.referenced_name)
1121
1122 union all
1123 select d.referenced_owner referenced_owner,
1124 d.referenced_name referenced_name
1125 from dba_dependencies d,
1126 p
1127 where d.referenced_type = 'TYPE'
1128 and d.referenced_name = upper(d.referenced_name )
1129 and d.name = p.referenced_name
1130 and d.owner = p.referenced_owner
1131 and d.referenced_owner in
1132 ( select oracle_username
1133 from SYSTEM.fnd_oracle_userid
1134 where read_only_flag in ('A','B', 'E', 'U', 'C')
1135 )
1136 and exists
1137 ( select 1
1138 from sys.obj$ o
1139 where owner# =(select user# from sys.user$ where name = d.referenced_owner)
1140 and o.name = d.referenced_name
1141 and o.type# = 13 --13 = TYPE,
1142 and o.subname is not null -- sstomar:
1143 -- This predicate is important becuase for evolved type more than
1144 -- one rows will be exist,
1145 -- one with "subname= null" and other one as "subname= <value>"
1146 )
1147
1148 )
1149 cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
1150 select distinct referenced_owner, referenced_name
1151 from p where cyclemarker = 'N';
1152 begin
1153 log(l_module, 'PROCEDURE', 'begin');
1154 -- reset in each call of this API
1155 g_evolved_type_index := 0;
1156 for et in all_evolved_types loop
1157
1158 -- we will get only DISTINCT Evoled-type and dependent evolved-type
1159 -- So for a case where
1160 -- T2 -> ( dependes on) T1
1161 -- T3 -> T1
1162 --
1163 -- T1 will come thru above cursor and no need to find dependent evolved-type
1164 -- for each ( T2 and T3) type then.
1165 --
1166 --
1167 copy_evolved_type(et.referenced_owner, et.referenced_name, g_apps_ne_schema);
1168 -- bug 12747238
1169 put_obj_to_recompile(et.referenced_owner, et.referenced_name);
1170 end loop; -- For ALL EVOLVED-TYPES
1171
1172 -- Install DDLs from global list to AD_ZD_DDL_HANDLER table
1173 install_evolved_types;
1174 recompile_types;
1175 compile_ne_schema(true);
1176 log(l_module, 'PROCEDURE', 'end');
1177 end COPY_EVOLVED_TYPES;
1178
1179
1180 --
1181 -- Get all evolved type referenced by tables as column type
1182 -- TODO: Currently this API is being populated as single API, reason being
1183 -- evolved type should be created in a proper order otherwise
1184 -- ALTER DDL command will fail and we may NOT get required hash-code.
1185 --
1186 procedure COPY_EVOLVED_TYPES_WRAPPER
1187 as
1188 l_module varchar2(80) := c_package || 'copy_evolved_types_wrapper';
1189 l_count number;
1190 begin
1191 log(l_module, 'PROCEDURE', 'begin');
1192
1193 select count(1) into l_count
1194 from dba_dependencies d
1195 where d.type = 'TABLE'
1196 and d.name = upper(name)
1197 and d.referenced_type = 'TYPE'
1198 and d.referenced_name = upper(d.referenced_name)
1199 and (
1200 d.referenced_owner in
1201 ( select oracle_username
1202 from SYSTEM.fnd_oracle_userid
1203 where read_only_flag in ('A','B', 'E', 'U','C')
1204 )
1205 OR
1206 exists
1207 ( select 1
1208 from dba_dependencies
1209 where owner = d.referenced_owner
1210 and name = d.referenced_name
1211 and type = d.referenced_type
1212 and referenced_type = 'TYPE'
1213 and referenced_owner in
1214 (
1215 select oracle_username
1216 from SYSTEM.fnd_oracle_userid
1217 where read_only_flag in ('A','B', 'E', 'U', 'C')
1218 )
1219 )
1220 )
1221 and exists
1222 ( select 1
1223 from sys.obj$ o
1224 where owner# =(select user# from sys.user$ where name = referenced_owner)
1225 and o.NAME = referenced_name
1226 and o.type# = 13
1227 and o.subname is not null
1228 )
1229 and not exists ( select null
1230 from xdb.xdb$element e
1231 where e.xmldata.property.sqltype = d.referenced_name )
1232 and not exists (select null
1233 from xdb.xdb$element e
1234 where e.xmldata.property.sqlcolltype = d.referenced_name);
1235
1236 if(l_count > 0 ) then
1237 -- Note: Evolved types should be created in a proper order and dependent TYPEs should be VALID.
1238 -- otherwise ALTER TYPE command will fail. So let it be run by a single worker.
1239 load(x_phase => ad_zd_parallel_exec.c_phase_copy_evolved_type,
1240 x_sql => 'begin ad_zd_prep.copy_evolved_types; end; ');
1241
1242 end if;
1243 log(l_module, 'PROCEDURE', 'end');
1244 end COPY_EVOLVED_TYPES_WRAPPER;
1245
1246
1247 --
1248 -- TODO: Rename or CLEANUP this API
1249 -- Populates column owner (of an UDT type column) of a table in a list,
1250 -- which will be used by FIX_COL_SYS() API
1251 --
1252 -- Note: we assume that the equivalent non-editioned type has already been
1253 -- been created in the non-editioned schema via the copy_type procedure.
1254
1255 procedure FIX_COLUMN(X_OWNER in varchar2,
1256 X_TABLE in varchar2,
1257 X_COLUMN in varchar2)
1258 is
1259 l_type_owner varchar2(30);
1260 l_type_name varchar2(30);
1261 l_autofix number :=0;
1262 l_module varchar2(80) := c_package || 'fix_column';
1263 l_comma varchar2(2) := ', ';
1264 begin
1265 log(l_module, 'PROCEDURE', 'begin: '|| x_owner || l_comma || x_table || l_comma
1266 || x_column);
1267 -- get old column type information
1268 select col.data_type_owner, col.data_type
1269 into l_type_owner, l_type_name
1270 from dba_tab_columns col
1271 where col.owner = x_owner
1272 and col.table_name = x_table
1273 and col.column_name = x_column;
1274 --
1275 -- 02-jun-2011: sstomar:
1276 -- Currently, including: schema1 =>'SYSTEM', schema2 => 'APPS_NE', typename => 'CCT_QDE_RESPONSE'
1277 --
1278 -- Not exist in <owner, type> list then only insert it
1279 if (is_type_exists_in_list(l_type_owner, l_type_name, g_udt_obj_list)= false )then
1280 g_udt_obj_indx := g_udt_obj_indx + 1;
1281 g_udt_obj_list(g_udt_obj_indx).owner := l_type_owner;
1282 g_udt_obj_list(g_udt_obj_indx).type_name := l_type_name;
1283 end if;
1284 log(l_module, 'PROCEDURE', 'end');
1285 end FIX_COLUMN;
1286
1287 --
1288 -- PRIVATE
1289 -- Fix data dictionary to use non-editioned data type
1290 --
1291 -- FIX_COLUMNS
1292 -- |
1293 -- -->FIX_COLUMN
1294 -- |
1295 -- --> FIX_COL_SYS
1296
1297 procedure FIX_COL_SYS as
1298 l_sql varchar2(4000);
1299 l_type_owner col_owners ;
1300 l_flag boolean := false;
1301 l_module varchar2(80) := c_package || 'fix_col_sys';
1302 begin
1303
1304 log(l_module, 'PROCEDURE', 'begin');
1305
1306 for idx in 1..g_udt_obj_list.count loop
1307 begin
1308 if(l_type_owner.count > 0 ) then
1309 if(l_type_owner( g_udt_obj_list(idx).owner) = 1 ) then
1310 l_flag := false;
1311 end if;
1312 else
1313 l_flag := true; -- first time.
1314 end if;
1315 exception
1316 when no_data_found then
1317 l_flag := true; -- not in already updated list
1318 end;
1319 -- ST ([email protected]): Suggested to use
1320 -- sys.dbms_objects_utils.update_types('APPS', APPS_NE', NULL, TRUE) , instead of
1321 -- calling for specific TYPE
1322 --
1323 if(l_flag) then
1324 l_sql := 'begin ad_zd_prep.fix_types(' ||
1325 'x_source_schema =>''' || g_udt_obj_list(idx).owner || ''', ' ||
1326 'x_target_schema => '''|| g_apps_ne_schema || ''') ; end;' ;
1327
1328 load(x_phase => ad_zd_parallel_exec.c_phase_fix_column,
1329 x_sql => l_sql);
1330
1331 l_type_owner(g_udt_obj_list(idx).owner) := 1;
1332 end if;
1333 end loop;
1334
1335 log(l_module, 'PROCEDURE', 'end');
1336 end FIX_COL_SYS;
1337
1338 --
1339 -- Fix AQ to use Non-Editioned payload type
1340 --
1341 -- Process works as follows:
1342 -- 1) Stop queue
1343 -- 2) Fix up payload column
1344 -- 3) Start queue
1345 --
1346 -- Note: multiple queues can be attached to the same queue table, we need to
1347 -- deal with all of them at once.
1348 --
1349 --
1350 procedure FIX_QUEUE(x_owner in varchar2, x_table in varchar2, x_column in varchar2)
1351 is
1352 l_module varchar2(80) := c_package || 'fix_queue';
1353 l_queue varchar2(80);
1354 l_queue_type varchar2(30);
1355
1356 cursor C_QUEUES(X_OWNER VARCHAR2, X_TABLE VARCHAR2) IS
1357 select '"'||aq.owner || '"."' || aq.name ||'"',
1358 aq.queue_type
1359 from dba_queues aq
1360 where aq.owner = x_owner
1361 and aq.queue_table = x_table
1362 and not ( aq.name like 'XLA_%_COMP_Q'
1363 OR aq.name like 'XLA_%_DOC_Q' );
1364
1365 begin
1366 log(l_module, 'PROCEDURE', 'begin: ' || x_owner || '.'|| x_table || ', ' || x_column);
1367 -- Stop related queues
1368 open c_queues(x_owner, x_table);
1369 fetch c_queues INTO l_queue, l_queue_type;
1370 --
1371 -- Merging with COPY_TYPE phase as both are independent.
1372 while (c_queues%found) loop
1373 load(x_phase => ad_zd_parallel_exec.c_phase_copy_type,
1374 x_sql => 'begin ad_zd_prep.stop_queue(''' || l_queue ||'''); end;');
1375
1376 fetch c_queues INTO l_queue, l_queue_type;
1377 end loop;
1378 -- close cursor
1379 CLOSE c_queues;
1380 -- Fix payload column
1381 fix_column(x_owner, x_table, x_column);
1382
1383 -- This phase should be executed after DROP_OBJECT, so if still there is any UDT reference
1384 -- , can be fixed.
1385 load(x_phase => ad_zd_parallel_exec.c_phase_recreate_aq_object,
1386 x_sql => 'begin ad_zd_prep.recreate_aq_object(''' || x_owner ||''', ''' || x_table ||'''); end;');
1387
1388 log(l_module, 'PROCEDURE', 'end');
1389 end FIX_QUEUE;
1390
1391 --
1392 --
1393 -- Recreates associated objects of an AQ after UDT reference fix.
1394 --
1395 --
1396 procedure RECREATE_AQ_OBJECT(X_OWNER in varchar2, X_TABLE in varchar2)
1397 is
1398 l_module varchar2(80) := c_package || 'recreate_aq_object';
1399 l_qt_flags number;
1400 cursor c_queues is
1401 select aq.name,
1402 aq.queue_type
1403 from dba_queues aq
1404 where aq.owner = x_owner
1405 and aq.queue_table = x_table;
1406
1407 begin
1408
1409 log(l_module, 'PROCEDURE', 'begin: ' || x_owner || '.' || x_table);
1410 -- Regenerate derived objects
1411 select flags into l_qt_flags
1412 from system.aq$_queue_tables
1413 where schema = x_owner
1414 and name = x_table;
1415
1416 begin
1417 -- RDBMS BUG 14206186: SHBOSE:
1418 -- Note that sys.dbms_prvtaqim.create_base_view internally calls
1419 -- sys.dbms_prvtaqim.create_deq_view. So you may not need to call another
1420 -- create_deq_view.
1421 --
1422 if (sys.dbms_aqadm_sys.mcq_8_1(l_qt_flags)) then
1423 -- For new multiconsumer queue (8.1 style and above)
1424 sys.dbms_aqadm_sys.create_buffer_view(x_owner, x_table, true);
1425 log(l_module, 'EVENT',
1426 'Call sys.dbms_prvtaqim.create_base_view('|| x_owner||', '||x_table ||', '|| l_qt_flags||')');
1427 sys.dbms_prvtaqim.create_base_view(x_owner, x_table, l_qt_flags);
1428 else
1429 -- only for 8.0 style queues
1430 log(l_module, 'EVENT',
1431 'Call sys.dbms_aqadm_sys.create_base_view('|| x_owner||', '||x_table||', '|| l_qt_flags||')');
1432 sys.dbms_aqadm_sys.create_base_view(x_owner, x_table, l_qt_flags);
1433 end if;
1434 exception
1435 when others then
1436 log(l_module, 'ERROR', 'Error: '|| x_owner ||'.'|| x_table || ' '|| substr(sqlerrm,1,2000));
1437 end;
1438
1439 -- Start queue
1440 -- TODO: Start EXCEPTION AQ Also ?
1441 --
1442 for queue in c_queues loop
1443 if (queue.queue_type = 'NORMAL_QUEUE') then
1444 start_queue('"'|| x_owner ||'"."'|| queue.name || '"');
1445 end if;
1446 end loop;
1447 log(l_module, 'PROCEDURE', 'end');
1448 exception
1449 when no_data_found then
1450 null;
1451 end RECREATE_AQ_OBJECT;
1452
1453
1454 --
1455 -- Fix all columns that depend on editioned types
1456 --
1457 -- Note: we assume that the equivalent non-editioned types have already
1458 -- been created in the non-editioned schema via the copy_type procedure.
1459 --
1460 procedure FIX_COLUMNS
1461 is
1462 L_OWNER varchar2(30);
1463 L_TABLE varchar2(30);
1464 L_COLUMN varchar2(30);
1465 L_QUEUE_COUNT number;
1466 l_orig_util_id number;
1467
1468 -- problem column query
1469 cursor C_COLUMNS is
1470 select
1471 atab.owner table_owner
1472 , atab.table_name table_name
1473 , acol.column_name column_name
1474 from
1475 dba_tables atab
1476 , dba_tab_columns acol
1477 where atab.owner in
1478 ( select oracle_username
1479 from SYSTEM.fnd_oracle_userid
1480 where read_only_flag in ('A','B', 'E', 'U', 'C')
1481 )
1482 and acol.owner = atab.owner
1483 and acol.table_name = atab.table_name
1484 and acol.data_type_owner in -- User defined data type (UDT)
1485 ( select oracle_username
1486 from SYSTEM.fnd_oracle_userid
1487 where read_only_flag in ('A','B', 'E', 'U', 'C')
1488 )
1489 order by 1, 2, 3;
1490
1491 -- This cursor for columns of SYSTEM.<UDT> AND SYSTEM.<UDT> depends
1492 -- on <EBS schema>.UDT
1493 --
1494 cursor C_COLUMNS2 is
1495 select
1496 atab.owner table_owner
1497 , atab.table_name table_name
1498 , acol.column_name column_name
1499 from
1500 dba_tables atab
1501 , dba_tab_columns acol
1502 where acol.owner = atab.owner
1503 and acol.table_name = atab.table_name
1504 and atab.owner IN -- table owner
1505 ( select oracle_username
1506 from SYSTEM.fnd_oracle_userid
1507 where read_only_flag in ('A','B', 'E', 'U', 'C')
1508 )
1509 and acol.data_type_owner='SYSTEM'
1510 and exists
1511 (
1512 select 1
1513 from dba_dependencies dep
1514 where dep.type = 'TYPE'
1515 start with dep.owner = acol.data_type_owner
1516 and dep.name = acol.DATA_TYPE
1517 and dep.type = 'TYPE'
1518 and dep.referenced_type= 'TYPE'
1519 and dep.referenced_owner <> 'SYSTEM'
1520 connect by
1521 prior dep.referenced_name = dep.name
1522 and prior dep.referenced_type = dep.type
1523 and prior dep.referenced_type = 'TYPE'
1524 and dep.referenced_owner in (
1525 select oracle_username
1526 from SYSTEM.fnd_oracle_userid
1527 where read_only_flag in ('A','B', 'E', 'U', 'C')
1528 )
1529 )
1530 order by 1,2,3;
1531
1532 l_module varchar2(80) := c_package || 'fix_columns';
1533
1534 begin
1535 log(l_module, 'PROCEDURE', 'begin');
1536 -- convert each problem column to use Non-Editioned type definition
1537 open c_columns;
1538 fetch c_columns INTO l_owner, l_table, l_column;
1539 while (c_columns%found) loop
1540
1541 select count(name) into l_queue_count
1542 from dba_queues
1543 where owner = l_owner
1544 and queue_table = l_table;
1545
1546 -- If it is a queue table, then fix the overall queue,
1547 -- otherwise, just fix the column
1548 if l_queue_count > 0 then
1549 fix_queue(l_owner, l_table, l_column);
1550 else
1551 fix_column( l_owner, l_table, l_column);
1552 end if;
1553 fetch c_columns INTO l_owner, l_table, l_column;
1554 end loop;
1555
1556 close c_columns;
1557
1558 -- for SYSTEM.UDT columns
1559 -----------------------------------------------
1560
1561 open c_columns2;
1562 fetch c_columns2 into l_owner, l_table, l_column;
1563 while (c_columns2%found) loop
1564
1565 -- check if this is a queue table,
1566 select count(name) into l_queue_count
1567 from dba_queues
1568 where owner = l_owner
1569 and queue_table = l_table;
1570
1571 -- If it is a queue table, then fix the overall queue,
1572 -- otherwise, just fix the column
1573 if l_queue_count > 0 then
1574 fix_queue(l_owner, l_table, l_column);
1575 else
1576 fix_column( l_owner, l_table, l_column);
1577 end if;
1578 fetch c_columns2 INTO l_owner, l_table, l_column;
1579
1580 end loop;
1581
1582 close c_columns2;
1583
1584 -----------------------------------------------
1585 -- Special query by to update SYS tables
1586 -- Reason: There would be other DATABASE objects like table which would be dependent
1587 -- on UTDs, now update their dependency to refer to APPS_NE UDT.
1588 --
1589 --
1590 fix_col_sys;
1591 log(l_module, 'PROCEDURE', 'end');
1592 end FIX_COLUMNS;
1593
1594 --
1595 -- - PUBLIC Synonyms point to editioned EBS objects must be dropped,
1596 -- - PUBLIC Synonyms cannot be editioned so they should be replaced by
1597 -- an equivalent private synonyms.
1598 --
1599 procedure FIX_PUBLIC_SYNONYMS as
1600 L_MODULE varchar2(80) := c_package || 'fix_public_synonyms';
1601
1602 cursor C_PUBLIC_SYNONYMS is
1603 select syn.synonym_name ,
1604 syn.table_owner ,
1605 syn.table_name ,
1606 syn.db_link
1607 from dba_synonyms syn
1608 where syn.owner='PUBLIC'
1609 and syn.table_owner in
1610 (select oracle_username
1611 from SYSTEM.fnd_oracle_userid
1612 where read_only_flag in ('A','B', 'E', 'C', 'U'))
1613 and (syn.table_owner, syn.table_name) in
1614 (select obj.owner, obj.object_name
1615 from dba_objects obj
1616 where obj.owner = syn.table_owner -- To avoid GSCC error
1617 and obj.object_type in ('TYPE', 'PACKAGE', 'VIEW' ,
1618 'SYNONYM','PROCEDURE',
1619 'TRIGGER','FUNCTION'));
1620 begin
1621 log(l_module, 'PROCEDURE', 'begin');
1622 for rec in c_public_synonyms loop
1623 load(x_phase=> ad_zd_parallel_exec.c_phase_fix_public_synonym,
1624 x_sql => 'begin ad_zd_prep.fix_public_synonym(''' || rec.synonym_name || ''', ''' ||
1625 rec.table_owner || ''',''' || rec.table_name ||''','''|| rec.db_link || '''); end;');
1626
1627 end loop;
1628 log(l_module, 'PROCEDURE', 'end');
1629 end FIX_PUBLIC_SYNONYMS;
1630
1631
1632 --
1633 -- Drops public synonym
1634 -- and recreate private synonym if not exist.
1635 --
1636 procedure FIX_PUBLIC_SYNONYM(
1637 X_SYNONYM_NAME in varchar2,
1638 X_TABLE_OWNER in varchar2,
1639 X_TABLE_NAME in varchar2,
1640 X_DB_LINK in VARCHAR2 )
1641 is
1642 L_MODULE varchar2(80) := c_package || 'fix_public_synonym';
1643 L_EXIST pls_integer;
1644 L_OBJECT_TYPE varchar2(30);
1645
1646 cursor C_DEPNDENT_USERS is
1647 select distinct owner
1648 from dba_dependencies
1649 where referenced_owner = 'PUBLIC'
1650 and referenced_type = 'SYNONYM'
1651 and referenced_name = x_synonym_name
1652 and owner in ( select oracle_username
1653 from system.fnd_oracle_userid
1654 where read_only_flag in ('A','B', 'E', 'C', 'U'));
1655
1656
1657 begin
1658 log(l_module, 'PROCEDURE', 'begin: ' || x_synonym_name||', ' || x_table_owner||', '
1659 || x_table_name || ', '|| x_db_link);
1660
1661 for l_users in c_depndent_users loop
1662 begin
1663 -- Check if private synonym exists?
1664 select 1 into l_exist
1665 from dba_synonyms
1666 where owner = l_users.owner
1667 and synonym_name = x_synonym_name
1668 and table_owner = x_table_owner
1669 and table_name = x_table_name
1670 and ((db_link is null
1671 and x_db_link is null
1672 )
1673 OR
1674 (db_link is not null
1675 and x_db_link is not null
1676 and db_link = x_db_link
1677 )
1678 ) ;
1679
1680 log(l_module, 'STATEMENT', 'Synonym with same name: ' ||
1681 l_users.owner ||'.' || x_synonym_name ||' already exists.');
1682 exception
1683 when no_data_found then
1684 -- check if another object with same <owner, name, object-namespace > exists?
1685 begin
1686 select object_type into l_object_type
1687 from dba_objects
1688 where owner = l_users.owner
1689 and object_name = x_synonym_name
1690 and namespace in /* not hard-coding namespace=1 */
1691 ( select namespace
1692 from dba_objects
1693 where object_type = 'SYNONYM'
1694 and owner = 'PUBLIC'
1695 and object_name = x_synonym_name
1696 );
1697 log(l_module, 'ERROR',
1698 'Object name conflict: There is an object with same name as '
1699 ||l_users.owner||'.'||x_synonym_name||' of type ' || l_object_type ||
1700 ' in the same object namespace.');
1701 exception
1702 when no_data_found then
1703 log(l_module, 'EVENT', 'Creating private synonym: ' || l_users.owner ||'.' || x_synonym_name);
1704 exec('create or replace synonym "'||
1705 l_users.owner ||'"."' || x_synonym_name || '" for "'||
1706 x_table_owner || '"."' || x_table_name ||'"', l_module);
1707 end;
1708 end;
1709 end loop;
1710
1711 log(l_module, 'EVENT', 'Dropping PUBLIC synonym: ' || x_synonym_name);
1712 exec('drop public synonym "'|| x_synonym_name ||'"' , l_module, true);
1713 log(l_module, 'PROCEDURE', 'end');
1714
1715 end FIX_PUBLIC_SYNONYM;
1716
1717 --
1718 -- Drop CTXSYS SYNONYMS
1719 --
1720 procedure DROP_CTXSYS_SYNONYM(X_SYNONYM_NAME varchar2) is
1721 l_module varchar2(80) := c_package || 'drop_ctxsys_synonym';
1722 begin
1723 log(l_module, 'PROCEDURE', 'begin: CTXSTS.' || X_SYNONYM_NAME);
1724 exec(X_SQL =>'drop synonym CTXSYS."'||X_SYNONYM_NAME ||'"' , X_LOG_MOD=>l_module, X_IGNORE=> true);
1725 log(l_module, 'PROCEDURE', 'end');
1726 end DROP_CTXSYS_SYNONYM;
1727
1728 --
1729 -- Drop CTXSYS package
1730 --
1731 procedure DROP_CTXSYS_PKG(X_PACKAGE_NAME varchar2) is
1732 l_module varchar2(80) := c_package || 'drop_ctxsys_pkg';
1733 begin
1734 log(l_module, 'PROCEDURE', 'begin: CTXSYS.'|| X_PACKAGE_NAME);
1735 exec(X_SQL =>'drop package CTXSYS."'||X_PACKAGE_NAME ||'"', X_LOG_MOD=> l_module, X_IGNORE=> true);
1736 log(l_module, 'PROCEDURE', 'end');
1737 end DROP_CTXSYS_PKG;
1738
1739 --
1740 -- Fix PLSQL packages installed in CTXSYS (context indexing)
1741 --
1742 -- TODO: migrate packages to APPS and repair CTXSYS definition.
1743 -- HACK: For now we just drop them.
1744 --
1745 -- NOTE: BUGS has already been filed against product teams to fix their objects.
1746 -- Here, we are just droping them from CTXSYS
1747 --
1748 procedure FIX_CTXSYS
1749 is
1750 l_module varchar2(80) := c_package || 'fix_ctxsys';
1751 cursor ctxpkg is
1752 select distinct name
1753 from dba_dependencies
1754 where owner='CTXSYS'
1755 and TYPE='PACKAGE BODY'
1756 and referenced_owner in
1757 ( select oracle_username
1758 from SYSTEM.fnd_oracle_userid
1759 where read_only_flag in ('A','B', 'E', 'U', 'C')
1760 )
1761 and referenced_type in
1762 ( 'TYPE',
1763 'PACKAGE',
1764 'VIEW' ,
1765 'SYNONYM',
1766 'PROCEDURE',
1767 'TRIGGER',
1768 'FUNCTION'
1769 );
1770 --
1771 -- Synonyms in CTXSYS for EBS objects.
1772 cursor C_SYNONYMS is
1773 select aps.synonym_name
1774 from dba_synonyms aps
1775 where aps.owner = 'CTXSYS'
1776 and aps.table_owner in
1777 ( select oracle_username
1778 from SYSTEM.fnd_oracle_userid
1779 where read_only_flag in ('A','B', 'E', 'U','C')
1780 )
1781 order by 1;
1782
1783 begin
1784 log(l_module, 'PROCEDURE', 'begin');
1785 for rec in ctxpkg loop
1786 load(x_phase => ad_zd_parallel_exec.c_phase_drop_object,
1787 x_sql => 'begin ad_zd_prep.drop_ctxsys_pkg(''' || rec.name || '''); end;');
1788
1789 end loop;
1790
1791 -- drop problematic synonyms
1792 for rec in c_synonyms loop
1793 load(x_phase => ad_zd_parallel_exec.c_phase_drop_object,
1794 x_sql => 'begin ad_zd_prep.drop_ctxsys_synonym(''' || rec.synonym_name || '''); end;');
1795 end loop;
1796 log(l_module, 'PROCEDURE', 'end');
1797 end FIX_CTXSYS;
1798
1799
1800 --
1801 -- This is used to fix EBR violations for custom users
1802 --
1803 procedure FIX_CUSTOM_OBJECTS(x_user varchar2)
1804 is
1805 L_MODULE varchar2(80) := c_package || 'fix_custom_objects';
1806
1807 -- Objects which would become INVALID when specified user is enabled for
1808 -- editions.
1809 cursor C_INVALIDATED_OBJECTS(P_USER varchar2) is
1810 select dep.d_obj# d_obj, do.name d_name, do.type# d_type,
1811 dep.p_obj# p_obj, po.name p_name, po.type# p_type
1812 from sys.dependency$ dep,
1813 sys.obj$ do,
1814 sys.obj$ po
1815 where do.obj# = dep.d_obj#
1816 and po.obj# = dep.p_obj#
1817 -- Only Non-Editionable objects
1818 and do.type# not in (4,5,7,8,9,10,11,12,13,14,22,55,87)
1819 start with dep.p_obj# in (select o.obj#
1820 from sys.obj$ o,
1821 sys.user$ u
1822 where u.name = P_USER
1823 and u.user# = o.owner#
1824 and o.type# in (4,5,7,8,9,10,11,12,13,14,22,55,87) )
1825 connect by nocycle prior dep.d_obj# = dep.p_obj#;
1826
1827 cursor C_COLUMNS is
1828 select
1829 tab.owner table_owner
1830 , tab.table_name table_name
1831 , col.column_name column_name
1832 , col.data_type type_name
1833 , col.data_type_owner type_owner
1834 from
1835 dba_tables tab
1836 , dba_tab_columns col
1837 where tab.owner = x_user
1838 and col.owner = tab.owner
1839 and col.table_name = tab.table_name
1840 and col.data_type_owner in -- User defined data type (UDT)
1841 ( select oracle_username
1842 from SYSTEM.fnd_oracle_userid
1843 where read_only_flag in ('A','B', 'E', 'U', 'C')
1844 )
1845 order by 1, 2, 3;
1846
1847 -- Fix public synonyms
1848 cursor C_PUBLIC_SYNONYMS is
1849 select syn.synonym_name ,
1850 syn.table_owner ,
1851 syn.table_name ,
1852 syn.db_link
1853 from dba_synonyms syn
1854 where syn.owner='PUBLIC'
1855 and syn.table_owner = x_user
1856 and (syn.table_owner, syn.table_name) in
1857 (select obj.owner, obj.object_name
1858 from dba_objects obj
1859 where obj.owner = syn.table_owner -- To avoid GSCC error
1860 and obj.object_type in ('TYPE', 'PACKAGE', 'VIEW' ,
1861 'SYNONYM','PROCEDURE',
1862 'TRIGGER','FUNCTION'));
1863
1864
1865 -- XML schema owned by custom user
1866 cursor C_XML_SCHEMAS is
1867 select owner, schema_url
1868 from dba_xml_schemas
1869 where owner =x_user;
1870
1871 -- MVs
1872 cursor C_MVS is
1873 select m.owner owner,
1874 m.mview_name name
1875 from dba_mviews m
1876 where m.owner =x_user
1877 and not exists
1878 ( select 'X'
1879 from dba_views v
1880 where v.owner = m.owner
1881 and v.view_name = ad_zd_mview.get_mvq_name(m.mview_name) )
1882 union
1883 select v.owner owner,
1884 ad_zd_mview.get_mv_name(v.view_name) name
1885 from dba_views v
1886 where v.view_name like '%'||'#'
1887 and v.editioning_view = 'N'
1888 and v.owner =x_user
1889 and not exists
1890 ( select 'X' from dba_objects m
1891 where m.owner = v.owner
1892 and m.object_name = ad_zd_mview.get_mv_name(v.view_name)
1893 and m.object_type = 'MATERIALIZED VIEW'
1894 and m.status = 'VALID' );
1895
1896
1897 begin
1898 log(l_module, 'PROCEDURE', 'begin: '|| x_user );
1899
1900 for l_invalidated_object in c_invalidated_objects(x_user) loop
1901 log(l_module, 'STATEMENT', 'Non-Editionable Object ('|| l_invalidated_object.d_name ||
1902 ',' || l_invalidated_object.d_type || '), dependent on (' ||
1903 l_invalidated_object.p_name || ',' || l_invalidated_object.p_type || ')');
1904 case
1905 --CASE#1: Table is dependent on TYPE
1906 -- This condition is also true when table is dependet on a TYPE descended
1907 -- from an XML schema
1908 when (l_invalidated_object.d_type = 2 and
1909 l_invalidated_object.p_type = 13) then
1910
1911 -- Fix each UDT column
1912 for l_tab_column in c_columns loop
1913 log(l_module, 'STATEMENT', 'Fix Table: which is dependent on ' ||
1914 l_tab_column.type_owner || '.' ||
1915 l_tab_column.type_name );
1916
1917 copy_type(l_tab_column.type_owner, l_tab_column.type_name, 'APPS_NE');
1918 fix_type(l_tab_column.type_owner, l_tab_column.type_name);
1919 end loop;
1920
1921 --(SSTOMAR)
1922 -- TODO: Fix the tables created directly by using a UDT.
1923 -- such tables will not show up in dba_tab_columns
1924 -- e.g. > create table sh_table of sh_type;
1925
1926 --CASE#2: Table dependent on an XML schema
1927 -- (and TYPES descended from XML schema will also be covered)
1928 when (l_invalidated_object.d_type = 2 and
1929 l_invalidated_object.p_type = 55) then
1930
1931 log(l_module, 'STATEMENT', 'Fix XML schema: ');
1932
1933 -- This is fine to run XML schema mover for all users as it will pick-up those
1934 -- users which are registered in fnd_oracle_userid (Only cost here is performance)
1935 -- TODO: Wait for DB fix 16286729, otherwise locking issue might occur.
1936 ad_zd_prep.move_xml_schemas;
1937
1938 -- CASE#3: MV is dependent on function, throw error as this can be fixed automatically.
1939 when (l_invalidated_object.d_type = 42 and
1940 l_invalidated_object.p_type = 8) then
1941 -- throw error
1942 log(l_module, 'ERROR', 'MV:' || l_invalidated_object.d_name ||
1943 ', dependent on: ' || l_invalidated_object.p_name);
1944 raise_application_error(-20005, 'Materialized view(s) which is/are dependent on a function, ' ||
1945 'can not be fixed automatically. Run Readiness Report from Note 1531121.1 ' ||
1946 'and fix them manually.');
1947
1948 -- CASE#4: MV is dependent any other type of object i.e. other than a function.
1949 when (l_invalidated_object.d_type = 42 and
1950 l_invalidated_object.p_type <> 8 ) then
1951
1952 -- Expand MVs
1953 -- TODO: This ideally should be done after upgrading Tables of this user
1954 for l_mv in C_MVS loop
1955 log(l_module, 'STATEMENT', 'Fix MV: ' || l_mv.owner || '.' || l_mv.name);
1956 ad_zd_mview.upgrade(l_mv.owner, l_mv.name);
1957 end loop;
1958 end case;
1959 end loop;
1960
1961 -- Fix public synonyms.
1962 -- NOTE: Public synonyms will NOT have any record in sys.dependencies$ table
1963 for l_rec in c_public_synonyms loop
1964 log(l_module, 'STATEMENT', 'Fix PUBLIC synonym: ' || l_rec.synonym_name);
1965 ad_zd_prep.fix_public_synonym(l_rec.synonym_name, l_rec.table_owner, l_rec.table_name, l_rec.db_link);
1966 end loop;
1967
1968 -- If still have noneditioned objects dependent on editioned objects?
1969 for l_invalidated_object in c_invalidated_objects(x_user) loop
1970 log(l_module, 'ERROR', 'ORA-20005: One or more noneditioned objects dependent on objects whose type is editionable.' ||
1971 'Run and review Readiness Report: Note 1531121.1 and then fix noneditioned objects' );
1972 raise_application_error(-20005, 'One or more noneditioned objects dependent on objects whose type is editionable.' ||
1973 'Run and review Readiness Report: Note 1531121.1 and then fix noneditioned objects' );
1974 end loop;
1975
1976 log(l_module, 'PROCEDURE', 'end');
1977 end FIX_CUSTOM_OBJECTS;
1978
1979
1980 --
1981 -- Registers a schema with fnd_oracle_userid with READ_ONLY_FLAG='B'
1982 --
1983 procedure REGISTER_CUSTOM_USER(X_USER varchar2) is
1984 L_STMT varchar2(1000);
1985 begin
1986 -- use execute immediate so that AD won't have any hard dependency on FND
1987 l_stmt := 'begin fnd_oracle_user_pkg.load_row (:1, ''CUSTOM'', ''INVALID'', NULL, ''N'', ''B''); end;';
1988 execute immediate l_stmt using X_USER;
1989 commit;
1990
1991 end REGISTER_CUSTOM_USER;
1992 --
1993 -- Enables the user for EDITIONS.
1994 -- NOTE: This procedure uses "force" mode to enable users for editions.
1995 procedure ENABLE_USER_4EDITION(X_USERNAME varchar2) as
1996 L_MODULE varchar2(80) := c_package || 'enable_user_4edition';
1997 begin
1998 log(l_module, 'PROCEDURE', 'begin: '|| x_username || ' enabling for editions');
1999 exec('alter user "'||x_username||'" enable editions force', l_module);
2000 log(l_module, 'PROCEDURE', 'end');
2001 end ENABLE_USER_4EDITION;
2002
2003
2004 --
2005 -- Enables a custom schema for editions.
2006 -- - If schema is not registered with E-biz, it will register it.
2007 -- - Fixes custom objects owned by custom schema
2008 -- - Upgrade tables for EV, owned by custom schema
2009 --
2010 procedure ENABLE_CUSTOM_USER(X_USER varchar2)
2011 is
2012 L_MODULE varchar2(80) := c_package || 'enable_custom_user';
2013 L_ENABLE_EDITIONS_ERROR EXCEPTION;
2014 PRAGMA EXCEPTION_INIT(l_enable_editions_error, -38819);
2015 L_STMT varchar2(250);
2016
2017 cursor C_TABLES is
2018 select
2019 tab.owner table_owner
2020 , tab.table_name table_name
2021 from dba_tables tab
2022 where tab.owner = x_user
2023 and tab.temporary = 'N'
2024 and tab.secondary = 'N'
2025 and not exists /* not a queue table */
2026 ( select qt.owner, qt.queue_table
2027 from dba_queue_tables qt
2028 where qt.owner = tab.owner
2029 and qt.queue_table = tab.table_name )
2030 and not exists /* not an MV container table */
2031 ( select mv.owner, mv.container_name
2032 from dba_mviews mv
2033 where mv.owner = tab.owner
2034 and mv.container_name = tab.table_name )
2035 order by tab.owner, tab.table_name;
2036
2037 begin
2038
2039 log(l_module, 'PROCEDURE', 'begin: '|| x_user || ' user enabling for editions');
2040
2041 -- As the password is being set as INVALID not calling
2042 -- Register (Bug 16492268).
2043 -- register_custom_user(x_user);
2044
2045 begin
2046 l_stmt := 'alter user "'||x_user||'" enable editions';
2047 log(l_module, 'STATEMENT', 'Enable '|| x_user || ' user for editions');
2048 execute immediate l_stmt;
2049 exception
2050 when l_enable_editions_error then
2051 log(l_module, 'STATEMENT', 'User '|| x_user || 'has objects which violate Edition-Based-Redefinition rules');
2052 /*
2053 ORA-38819: user SH_USER owns one or more objects whose type is editionable and
2054 that have noneditioned dependent objects
2055 */
2056 fix_custom_objects(x_user);
2057
2058 -- retrye without force mode
2059 begin
2060 log(l_module, 'STATEMENT', 'Retry: Enable '|| x_user || ' user for editions');
2061 execute immediate l_stmt;
2062 log(l_module, 'STATEMENT', 'User '|| x_user || ' has been enabled for editions');
2063 exception
2064 when l_enable_editions_error then
2065 log(l_module, 'ERROR', 'User '|| x_user || 'Can not enable user for editions. ' || sqlerrm);
2066 raise_application_error(-20006, 'Can not enable user for editions. ' || sqlerrm);
2067 end;
2068 end;
2069
2070 log(l_module, 'STATEMENT', 'Upgrade custom tables owned by: '|| x_user || ' user');
2071 for l_rec in c_tables loop
2072 ad_zd_table.upgrade(l_rec.table_owner, l_rec.table_name);
2073 end loop;
2074
2075 commit;
2076 log(l_module, 'STATEMENT', 'end');
2077
2078 end ENABLE_CUSTOM_USER;
2079
2080 --
2081 --
2082 -- NOTE: This should be run after completing all the DDLs i.e.
2083 -- EBR violations has been removed from target database.
2084 --
2085 -- This API should be (SYS or SYSTEM) as SYSDBA
2086 --
2087 -- Enable Editions for ALL users
2088 --
2089 procedure ENABLE_EDITIONS as
2090 l_module varchar2(80) := c_package || 'enable_editions';
2091 cursor c_users is
2092 select fou.oracle_username username
2093 from system.fnd_oracle_userid fou
2094 , dba_users du
2095 where fou.read_only_flag in ('A','B', 'E', 'U', 'C')
2096 and du.editions_enabled = 'N'
2097 and du.username = fou.oracle_username
2098 and not exists
2099 (select u.name
2100 from sys.registry$ r,
2101 sys.user$ u
2102 where r.status in (1,3,5)
2103 and r.namespace = 'SERVER'
2104 and r.schema# = u.user#
2105 and u.name = du.username
2106 union
2107 select u.name
2108 from sys.registry$ r,
2109 sys.registry$schemas s,
2110 sys.user$ u
2111 where r.status in (1,3,5)
2112 and r.namespace = 'SERVER'
2113 and r.cid = s.cid
2114 and s.schema# = u.user#
2115 and u.name = du.username
2116 );
2117 begin
2118 log(l_module, 'PROCEDURE', 'begin');
2119
2120 for rec in c_users loop
2121 load(x_phase => ad_zd_parallel_exec.c_phase_enable_editioning,
2122 x_sql => 'begin ad_zd_prep.enable_user_4edition('''||rec.username||'''); end;');
2123
2124 end loop;
2125 log(l_module, 'PROCEDURE', 'end');
2126 end ENABLE_EDITIONS;
2127
2128
2129 --
2130 -- This API generates DDLs or PL/SQL blocks for EBR violations as well
2131 -- as TABLE, MV upgrades.
2132 --
2133 --
2134 procedure DO_PREP
2135 as
2136 c_module varchar2(80) := c_package || 'DO_PREP';
2137
2138 begin
2139
2140 log(c_module, 'PROCEDURE', 'begin' );
2141 dbms_application_info.set_module('AD_ZD_PREP', 'DDL GENERATION');
2142
2143 log(c_module, 'STATEMENT', 'Cleaning up AD_ZD_DDL_HANDLER table' );
2144
2145 -- **** Clean ONLY DB PREP data, as SEED-UPGRADE data
2146 -- has already been populated *****.
2147 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_drop_unused_object);
2148 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_copy_type);
2149 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_compile_type);
2150 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_copy_evolved_type);
2151 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_fix_column);
2152 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_fix_type);
2153 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_fix_public_synonym);
2154 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_recreate_aq_object);
2155 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_drop_object);
2156 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_enable_editioning);
2157 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_upgrade_table);
2158 ad_zd_parallel_exec.cleanup(ad_zd_parallel_exec.c_phase_upgrade_mview);
2159
2160 -- STEP#1)
2161 log(c_module, 'STATEMENT', 'Invoking drop_temp_queues ' );
2162 drop_temp_queues;
2163 commit;
2164
2165 --STEP#2 : copy_types also populates compile_ne_schema API.
2166 copy_types;
2167 commit;
2168
2169 --step#3
2170 copy_evolved_types_wrapper;
2171 --step#5
2172 log(c_module, 'STATEMENT','Invoking fix_columns API.' );
2173 fix_columns;
2174 commit;
2175
2176 -- STEP#6) Objects from CTXSYS as CTXSYS is Non-Editioned
2177 log(c_module, 'STATEMENT', 'Invoking fix_ctxsys API.' );
2178 fix_ctxsys;
2179 commit;
2180
2181 -- STEP#7) : fix_synonyms;
2182 log(c_module, 'STATEMENT', 'Invoking fix_public_synonyms API.' );
2183 fix_public_synonyms;
2184 commit;
2185 --step#8
2186 log(c_module, 'STATEMENT', 'Invoking enable_editions API.' );
2187 enable_editions;
2188 commit;
2189
2190 -- STEP#2) Materialized Views
2191 log(c_module, 'STATEMENT', 'Invoking ad_zd_mview.upgrade_db API.' );
2192 ad_zd_mview.upgrade_db(0);
2193 commit; --
2194
2195 log(c_module, 'STATEMENT', 'Invoking ad_zd_table.upgrade_db API.' );
2196 ad_zd_table.upgrade_db;
2197 commit;
2198 log(c_module, 'PROCEDURE','end' );
2199 exception
2200 when others then
2201 log(c_module, 'ERROR','E-Business Suite Database Preparation for Editions:'
2202 || sqlcode || ' ' || substr(sqlerrm, 1, 64) );
2203 raise_application_error(-20997,
2204 'E-Business Suite Database Preparation for Editions: '
2205 || sqlcode || ' ' || substr(sqlerrm, 1, 64));
2206 end do_prep;
2207
2208 end AD_ZD_PREP;
[Home] [Help]