[Home] [Help]
PACKAGE BODY: SYS.AD_ZD_SYS
Source
1 PACKAGE BODY AD_ZD_SYS AS
2 /* $Header: adgrants.sql 120.67.12020000.13 2013/05/15 11:19:38 mkumandu ship $ */
3
4 C_PACKAGE constant varchar2(80) := 'ad.plsql.ad_zd_sys.';
5
6 /*
7 ** Exceptions we handle
8 */
9 SUCCESS_WITH_COMPILE_ERR exception;
10 pragma exception_init(success_with_compile_err, -24344);
11
12 OBJECT_DOES_NOT_EXIST exception;
13 pragma exception_init(object_does_not_exist, -4043);
14
15 TRIGGER_DOES_NOT_EXIST exception;
16 pragma exception_init(trigger_does_not_exist, -4080);
17
18 OBJECT_MARKED_FOR_DELETE exception;
19 pragma exception_init(object_marked_for_delete, -21700);
20
21 TYPE_NOT_FOUND exception;
22 pragma exception_init(type_not_found, -22303);
23
24 SYNONYM_DOES_NOT_EXIST exception;
25 pragma exception_init(synonym_does_not_exist, -1434);
26
27 /*
28 ** Write Log Message
29 */
30 procedure LOG(X_MODULE varchar2, X_LEVEL varchar2, X_MESSAGE varchar2)
31 is
32 L_APPLSYS varchar2(30);
33 L_MODULE varchar2(80) := c_package||x_module;
34 begin
35
36 -- get applsys schema
37 select oracle_username into l_applsys
38 from system.fnd_oracle_userid
39 where read_only_flag = 'E';
40
41 -- insert log message
42 execute immediate
43 'insert into '||l_applsys||'.ad_zd_logs '||
44 ' (log_sequence, module, message_text, session_id, type, timestamp) '||
45 ' values ('||l_applsys||'.ad_zd_logs_s.nextval, '||
46 ''''||l_module||''', '||
47 'substrb('''||x_message||''',1, 3900), '||
48 'sys_context(''USERENV'',''SESSIONID''), '||
49 ''''||x_level||''', SYSDATE) ';
50 commit;
51
52 exception
53 when others then
54 null;
55 end;
56
57 /*
58 ** Update LOGON trigger status.
59 ** - X_STATUS: ENABLE | DISABLE
60 **
61 ** This is being called from ad_zd.alter_logon_trigger API.
62 */
63 procedure ALTER_LOGON_TRIGGER(X_STATUS varchar2)
64 is
65 C_MODULE varchar2(80) := 'alter_logon_trigger';
66 begin
67 log(c_module, 'EVENT', 'alter logon trigger : '|| x_status);
68 execute immediate 'alter trigger SYSTEM.EBS_LOGON ' || x_status ;
69 end ALTER_LOGON_TRIGGER;
70
71
72 /*
73 ** Retire Edition
74 ** Revoke grants to USE edition, if it exists
75 ** x_edition_name - edition to retire
76 **
77 ** Note: eats all errors because they are not helpful
78 */
79 procedure RETIRE_EDITION(x_edition_name in varchar2)
80 is
81 C_MODULE varchar2(80) := 'retire_edition';
82 begin
83 log(c_module, 'EVENT', 'Retire Edition: '||x_edition_name);
84 begin
85 execute immediate 'revoke use on edition ' || x_edition_name || ' from PUBLIC';
86 exception
87 when others then
88 log(c_module, 'STATEMENT', 'Note: '||SQLERRM);
89 null;
90 end;
91 end;
92
93
94 /*
95 ** Retire old editions
96 ** Retires all old editions that can be used
97 */
98 procedure RETIRE_OLD_EDITIONS
99 is
100 C_MODULE varchar2(80) := 'retire_old_editions';
101
102 -- old editions that can be used
103 cursor C_OLD_EDITIONS is
104 select oe.object_name edition_name
105 from
106 dba_objects_ae oe,
107 dba_objects_ae re,
108 database_properties RUN
109 where run.property_name = 'DEFAULT_EDITION'
110 /* run edition */
111 and re.owner = 'SYS'
112 and re.object_type = 'EDITION'
113 and re.object_name = run.property_value
114 /* old edition */
115 and oe.owner = 'SYS'
116 and oe.object_type = 'EDITION'
117 and oe.created < re.created
118 /* can be used */
119 and exists
120 ( select 1
121 from dba_tab_privs
122 where privilege = 'USE'
123 and owner = oe.owner
124 and table_name = oe.object_name
125 and grantee = 'PUBLIC' );
126
127 begin
128 log(c_module, 'EVENT', 'Retiring Old Editions');
129 for erec in c_old_editions loop
130 retire_edition(erec.edition_name);
131 end loop;
132 end;
133
134
135 /*
136 ** Drop unwanted database edition
137 ** x_edition_name - name of the edition to drop
138 */
139 procedure DROP_EDITION(x_edition_name in varchar2) is
140 C_MODULE varchar2(80) := 'drop_edition';
141 L_EDITION varchar2(30) := x_edition_name;
142 L_DEFAULT varchar2(30);
143 begin
144 log(c_module, 'PROCEDURE', 'begin '||x_edition_name);
145
146 if (l_edition is null) then
147 log(c_module, 'ERROR', 'Edition NULL does not exist');
148 raise_application_error(-20010, 'Edition NULL does not exist');
149 end if;
150
151 /* Get default edition first */
152 select property_value into l_default
153 from database_properties
154 where property_name = 'DEFAULT_EDITION';
155
156 if (l_edition = l_default) then
157 log(c_module, 'ERROR', 'Cannot drop RUN edition');
158 raise_application_error(-20011, 'Cannot drop RUN edition');
159 end if;
160
161 log(c_module, 'EVENT', 'Drop Edition: '||l_edition);
162 begin
163 execute immediate 'drop edition '||l_edition||' cascade';
164 exception
165 when others then
166 log(c_module, 'ERROR', 'Note: '||SQLERRM);
167 raise;
168 end;
169
170 log(c_module, 'PROCEDURE', 'end');
171 end;
172
173
174 /*
175 ** Drop covered object
176 **
177 ** Before droppping an object, it checks if the edition is really a retired edition.
178 ** The above check is done to ensure that nobody can use this api to drop an object
179 ** in a RUN edition
180 **
181 ** x_old_edition - Edition not in usable state
182 */
183 procedure DROP_COVERED_OBJECT(
184 X_OWNER varchar2,
185 X_OBJECT_NAME varchar2,
186 X_OBJECT_TYPE varchar2,
187 X_EDITION_NAME varchar2)
188 is
189 C_MODULE varchar2(80) := 'drop_covered_object';
190 L_COUNT number := 0;
191 L_STMT varchar2(2000);
192 L_CURSOR integer;
193 L_OWNER varchar2(30) := 'SYS';
194 L_ERRMSG varchar2(2000);
195 begin
196
197 -- test if edition is retired
198 select count(*) into l_count
199 from dba_tab_privs
200 where privilege = 'USE'
201 and owner = l_owner /* Added for GSCC error */
202 and grantee = 'PUBLIC'
203 and table_name = x_edition_name;
204
205 -- If active edition, do nothing
206 if (l_count > 0) then
207 log(c_module, 'ERROR', 'Not a retired edition: '||x_edition_name);
208 return;
209 end if;
210
211
212 l_stmt := 'drop '||x_object_type||' '||'"'||x_owner||'"."'||x_object_name||'"';
213
214 if x_object_type = 'TYPE' or x_object_type = 'SYNONYM' then
215 l_stmt := l_stmt||' force';
216 elsif x_object_type = 'VIEW' then
217 l_stmt := l_stmt||' cascade constraints';
218 end if;
219
220 -- log(c_module, 'STATEMENT', 'SQL['||x_edition_name||': '||l_stmt;
221 l_cursor := dbms_sql.open_cursor(security_level=>2);
222 dbms_sql.parse(l_cursor, l_stmt, dbms_sql.native, x_edition_name, null, false);
223 dbms_sql.close_cursor(l_cursor);
224
225 exception
226 when success_with_compile_err or
227 object_does_not_exist or
228 trigger_does_not_exist or
229 object_marked_for_delete or
230 type_not_found or
231 synonym_does_not_exist
232 then
233 if dbms_sql.is_open(l_cursor) then
234 dbms_sql.close_cursor(l_cursor);
235 end if;
236 when others then
237 if dbms_sql.is_open(l_cursor) then
238 dbms_sql.close_cursor(l_cursor);
239 end if;
240 L_ERRMSG := SQLERRM;
241 log(c_module, 'ERROR', L_ERRMSG || '; SQL['||x_edition_name||': '||l_stmt);
242 raise;
243 end;
244
245
246 /*
247 ** Drop Covered Objects
248 **
249 ** Drop objects in retired editions that have a replacement object in any newer edition.
250 ** This is done for both ACTUAL objects and STUB objects.
251 **
252 ** x_execute
253 ** true: Execute the DDLs immediately
254 ** false: Save DDL to parallel execution service
255 */
256 procedure DROP_COVERED_OBJECTS(X_EXECUTE in boolean default true)
257 IS
258 C_MODULE varchar2(80) := 'drop_covered_objects';
259 L_PRIV_COUNT integer;
260 L_EDITION_NAME varchar2(30);
261 L_OBJECT_TYPE varchar2(30);
262 L_OBJECT_NAME varchar2(80);
263 L_STMT varchar2(1000);
264 L_OWNER varchar2(30) := 'SYS';
265 L_ERRMSG varchar2(2000);
266 L_APPLSYS varchar2(30);
267 L_CURRENT_EDITION varchar2(30);
268 L_SQL varchar2(2000);
269
270 -- Covered Objects
271 -- are in an Old Edition
272 -- have a replacement object in a newer edition
273 -- Note: to minimize stub invalidation the result order
274 -- drop objects in newer editions first
275 -- drop objects in dependancy order (dependent object, then parent)
276
277 -- Never drop objects in the RUN or PATCH edition
278 cursor C_COVERED_OBJECTS is
279 select
280 oe.created edition_date
281 , oe.object_name edition_name
282 , decode(co.object_type,
283 'TRIGGER', 1,
284 'PACKAGE BODY', 2,
285 'TYPE BODY', 3,
286 'PROCEDURE', 4,
287 'FUNCTION', 5,
288 'PACKAGE', 6,
289 'VIEW', 7,
290 'SYNONYM', 8,
291 'TYPE', 9,
292 /*other*/ 99 ) drop_order
293 , co.owner
294 , co.object_type
295 , co.object_name
296 from
297 ( select
298 eusr.edition_name
299 , eusr.user_name owner
300 , obj.name object_name
301 , obj.type#
302 , decode(obj.type#,
303 4, 'VIEW',
304 5, 'SYNONYM',
305 7, 'PROCEDURE',
306 8, 'FUNCTION',
307 9, 'PACKAGE',
308 10, 'NON-EXISTENT',
309 11, 'PACKAGE BODY',
310 12, 'TRIGGER',
311 13, 'TYPE',
312 14, 'TYPE BODY',
313 88, decode((select d.type# from sys.obj$ d
314 where d.obj# = obj.dataobj#),
315 4, 'VIEW',
316 5, 'SYNONYM',
317 7, 'PROCEDURE',
318 8, 'FUNCTION',
319 9, 'PACKAGE',
320 10, 'NON-EXISTENT',
321 11, 'PACKAGE BODY',
322 12, 'TRIGGER',
323 13, 'TYPE',
324 14, 'TYPE BODY')) object_type
325 , obj.namespace namespace
326 , obj.obj# object_id
327 from
328 sys.obj$ obj
329 , ( select
330 xusr.user#
331 , xusr.ext_username user_name
332 , ed.name edition_name
333 from
334 (select * from sys.user$ where type# = 2) xusr
335 , (select * from sys.obj$
336 where owner# = 0 and type# = 57) ed
337 where xusr.spare2 = ed.obj#
338 union
339 select
340 busr.user#
341 , busr.name user_name
342 , ed.name edition_name
343 from
344 (select * from sys.user$ where type#=1 or user#=1) busr
345 , (select * from sys.obj$ where owner#=0 and type#=57) ed
346 where ed.name = 'ORA$BASE' ) eusr
347 where obj.owner# = eusr.user#
348 and obj.type# not in (10, 0)
349 and obj.remoteowner is null ) co
350 , dba_objects_ae oe /* old edition */
351 , dba_objects_ae re /* run edition */
352 , database_properties run /* run edition name */
353 where run.property_name = 'DEFAULT_EDITION'
354 /* run edition */
355 and re.owner = 'SYS'
356 and re.object_type = 'EDITION'
357 and re.object_name = run.property_value
358 /* old edition */
359 and oe.owner = 'SYS'
360 and oe.object_type = 'EDITION'
361 and oe.created < re.created
362 /* covered object */
363 and co.edition_name = oe.object_name
364 and co.object_type in
365 ('SYNONYM', 'VIEW',
366 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY',
367 'PROCEDURE', 'FUNCTION', 'TRIGGER') /* editioned type */
368 and exists ( select null
369 from dba_objects_ae ro /* replacement object */
370 , dba_objects_ae ne /* newer edition */
371 where ro.owner = co.owner
372 and ro.object_type in (co.object_type, 'NON-EXISTENT')
373 and ro.object_name = co.object_name
374 and ro.namespace = co.namespace
375 and ro.edition_name = ne.object_name
376 and ne.owner = 'SYS'
377 and ne.object_type = 'EDITION'
378 and ne.created > oe.created
379 and ne.created <= re.created )
380 order by edition_date DESC, drop_order, co.owner, co.object_name;
381
382 BEGIN
383 if x_execute then
384 log(c_module, 'PROCEDURE', 'begin - execute');
385 else
386 log(c_module, 'PROCEDURE', 'begin - defer');
387 end if;
388
389 l_current_edition := sys_context('USERENV', 'CURRENT_EDITION_NAME');
390
391 -- verify all old editions are retired
392 -- this is not the exact right query
393 select count(*)
394 into l_priv_count
395 from dba_tab_privs
396 where privilege = 'USE'
397 and owner = l_owner /* Added for GSCC error */
398 and table_name in ( select parent_edition_name from dba_editions);
399
400 if l_priv_count > 0 then
404 select oracle_username into l_applsys
401 raise_application_error(-20007, 'Ancestors of Current RUN Edition are not retired');
402 end if;
403
405 from system.fnd_oracle_userid
406 where read_only_flag = 'E';
407
408 -- Drop each covered object, in correct order
409 for objrec in c_covered_objects loop
410 l_edition_name := objrec.edition_name;
411 l_owner := objrec.owner;
412 l_object_type := objrec.object_type;
413 l_object_name := objrec.object_name;
414
415 begin
416 if (x_execute) then
417 -- Drop object immediatly
418 drop_covered_object(l_owner, l_object_name, l_object_type, l_edition_name);
419 else
420 -- Store drop action in DDL Handler for parallel execution
421 l_sql := 'insert into '|| l_applsys ||'.ad_zd_ddl_handler ' ||
422 '(phase, ddl_id, sql_lob, executed, status) values (' ||
423 q'['DROP_COVERED_OBJS',]' || l_applsys ||'.ad_zd_ddl_handler_ddl_s.nextval, ' ||
424 q'['begin sys.ad_zd_sys.drop_covered_object('']' || l_owner||
425 q'['','']' || l_object_name ||
426 q'['','']' || l_object_type ||
427 q'['','']' || l_edition_name ||
428 q'[''); end;', 'N', 'NOT-EXEC')]' ;
429
430 execute immediate l_sql;
431 end if;
432 exception
433 when others then null;
434 end;
435 end loop;
436
437 commit;
438 log(c_module, 'PROCEDURE', 'end');
439 END;
440
441
442 function CONSTRUCT_ACTUALIZE_DDL(
443 X_OWNER in varchar2,
444 X_OBJECT_NAME in varchar2,
445 X_OBJECT_TYPE in varchar2) return varchar2
446 is
447 L_STMT_OBJECT_TYPE varchar2(30);
448 L_STMT varchar2(2000) := '';
449 begin
450 if x_object_type = 'PACKAGE BODY' then
451 l_stmt_object_type := 'PACKAGE';
452 elsif x_object_type = 'TYPE BODY' then
453 l_stmt_object_type := 'TYPE';
454 else
455 l_stmt_object_type := x_object_type;
456 end if;
457
458 l_stmt := 'alter '||l_stmt_Object_Type||' "'||x_Owner||'"."'||x_Object_Name||'"'||' compile';
459
460 -- Call out all schema OBJECT TYPES that are editionable
461 l_stmt := case x_object_type
462 when 'PROCEDURE' then l_stmt||' reuse settings'
463 when 'PACKAGE' then l_stmt||' SPECIFICATION reuse settings'
464 when 'PACKAGE BODY' then l_stmt||' BODY reuse settings'
465 when 'FUNCTION' then l_stmt||' reuse settings'
466 when 'TRIGGER' then l_stmt||' reuse settings'
467 when 'TYPE' then l_stmt||' SPECIFICATION reuse settings'
468 when 'TYPE BODY' then l_stmt||' BODY reuse settings'
469 else l_stmt
470 end;
471
472 return l_stmt;
473 END;
474
475
476 /*
477 ** Actualizes an object in a patch edition
478 **
479 */
480 procedure ACTUALIZE_OBJECT(
481 X_OWNER varchar2,
482 X_OBJECT_NAME varchar2,
483 X_OBJECT_TYPE varchar2)
484 is
485 L_COUNT number := 0;
486 L_STMT_OBJECT_TYPE varchar2(30);
487 L_STMT varchar2(2000);
488 L_CURSOR integer;
489 L_DEFAULT_EDITION varchar2(30);
493 select property_value into l_default_edition
490 L_PATCH_EDITION varchar2(30);
491 begin
492
494 from database_properties
495 where property_name = 'DEFAULT_EDITION';
496
497 begin
498 select aed.edition_name into l_patch_edition
499 from all_editions AED
500 where aed.parent_edition_name = l_default_edition;
501 exception
502 when no_data_found then
503 l_patch_edition := NULL;
504 end;
505
506 select count(*) into l_count
507 from dba_objects_ae
508 where owner = x_owner
509 and object_name = x_object_name
510 and object_type = x_object_type
511 and edition_name = l_patch_edition;
512
513 if (l_count = 0)
514 then
515 l_stmt := construct_actualize_ddl(x_owner,x_object_name,x_object_type);
516
517 l_cursor := dbms_sql.open_cursor(security_level=>2);
518
519 dbms_sql.parse(l_cursor, l_stmt ,
520 DBMS_SQL.NATIVE, l_patch_edition, null, false);
521
522 dbms_sql.close_cursor(l_cursor);
523 else
524 -- Object already actualized
525 null;
526 end if;
527
528 EXCEPTION
529 WHEN success_with_compile_err then
530 if dbms_sql.is_open(l_cursor) then
531 dbms_sql.close_cursor(l_cursor);
532 end if;
533 WHEN OTHERS THEN
534 if dbms_sql.is_open(l_cursor) then
535 dbms_sql.close_cursor(l_cursor);
536 end if;
537
538 raise;
539 END;
540
541
542 /*
543 ** Bug 12938985
544 ** Execute immediate mode actualizes all the objects in a single iteration
545 **
546 ** Parallel workers mode uses the foll. approach
547 ** Make 2 passes of dba_objects. During the first pass, select objects with no dependencies
548 ** (i.e LIST1) and in the second pass do the rest (i.e LIST2). This is done for performance
549 ** optimization and to minimize the locking contention while running in parallel workers mode.
550 **
551 ** LIST1 - contains all the objects with no dependencies. Used during parallel workers mode
552 ** LIST2 - Rest of the objects (i.e objects which have dependencies). Used during parallel workers mode
553 ** LIST3 - All the objects. This is used during execute immediate mode
554 **
555 ** During the parallel workers mode, utility ids are of no use anymore.
556 ** Ref bug#14026330. Utility ids are converted to PHASES
557 ** LIST PHASE OBJECT_TYPE
558 ** -----------------------------------------------------------------------------
559 ** LIST1 ACTUALIZE_PARENT_OBJS Any editionable object type other
560 ** than PACKAGE BODY, which has no dependencies
561 ** LIST1 ACTUALIZE_PARENT_OBJS PACKAGE BODYs which depends only on the
562 ** respective PACKAGE SPEC
563 ** LIST2 ACTUALIZE_CHILD_OBJS Editionable objects which depends on other objects
564 */
565 PROCEDURE PROCESS_INHERITED_OBJS_LIST(x_list in varchar2,
566 x_edition in varchar2,
567 x_execute in boolean default true)
568 IS
569 L_STMT varchar2(2000);
570 L_OWNER varchar2(30);
571 L_OBJECT_NAME varchar2(128);
572 L_OBJECT_TYPE varchar2(30);
573 L_OBJ_EDITION varchar2(30);
574 L_STMT_OBJECT_TYPE varchar2(30);
575 L_ERRCODE number;
576 L_ERRMSG varchar2(2000);
577 L_APPLSYS varchar2(30);
578 L_PHASE varchar2(50);
579 L_SQL varchar2(2000);
580 cursor C_INHERITED_OBJECTS(x_edition varchar2) is
581 select
582 o.owner
583 , o.object_name
584 , o.object_type
585 from
586 dba_objects o
587 where o.edition_name <> x_edition
588 and o.owner in ( select username from dba_users where editions_enabled = 'Y')
589 order by o.owner,
590 decode(o.object_type,
591 'TYPE', 1, /* most types depend on native or other types */
592 'SYNONYM', 2, /* synonyms point to tables and types */
593 'PACKAGE', 3, /* packages can depend on types */
594 'VIEW', 4, /* views depend on packages and synonyms */
595 'TYPE BODY', 5,
596 'PACKAGE BODY', 6,
597 7), o.object_name;
598
599
600 cursor C_INHERITED_OBJECTS_LIST1(x_edition varchar2) is
601 select * from
602 (select owner,object_name,object_type
603 from dba_objects o1
604 where o1.edition_name <> x_edition
605 and o1.owner in ( select username from dba_users where editions_enabled = 'Y')
606 and not exists ( select null from dba_dependencies d
607 where d.owner = o1.owner
608 and d.name = o1.object_name
609 and d.type = o1.object_type
610 and d.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
611 and d.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
615 from dba_objects o2
612 )
613 union
614 select owner,object_name,object_type
616 where o2.edition_name <> x_edition
617 and o2.owner in (select username from dba_users where editions_enabled = 'Y')
618 AND o2.object_type = 'PACKAGE BODY'
619 AND NOT EXISTS ( SELECT NULL
620 FROM dba_dependencies c
621 WHERE c.owner = o2.owner
622 AND c.name = o2.object_name
623 AND c.TYPE = 'PACKAGE BODY'
624 AND c.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
625 AND c.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
626 AND ( (c.referenced_name <> c.name) or (c.referenced_owner <> c.owner) OR (c.referenced_type <> 'PACKAGE'))
627 )
628 )
629 order by decode(object_type,
630 'PACKAGE BODY', 2,
631 1), object_name;
632
633
634 cursor C_INHERITED_OBJECTS_LIST2(x_edition varchar2) is
635 select * from
636 (select owner,object_name,object_type
637 from dba_objects o1
638 where o1.edition_name <> x_edition
639 and o1.owner in ( select username from dba_users where editions_enabled = 'Y')
640 minus
641 select owner,object_name,object_type
642 from dba_objects o2
643 where o2.edition_name <> x_edition
644 and o2.owner in ( select username from dba_users where editions_enabled = 'Y')
645 and not exists ( select null from dba_dependencies d
646 where d.owner = o2.owner
647 and d.name = o2.object_name
648 and d.type = o2.object_type
649 and d.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
650 and d.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
651 )
652 minus
653 select owner,object_name,object_type
654 from dba_objects o3
655 where o3.edition_name <> x_edition
656 and o3.owner in ( select username from dba_users where editions_enabled = 'Y')
657 AND o3.object_type = 'PACKAGE BODY'
658 AND NOT EXISTS ( SELECT NULL
659 FROM dba_dependencies c
660 WHERE c.owner = o3.owner
661 AND c.name = o3.object_name
662 AND c.TYPE = 'PACKAGE BODY'
663 AND c.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
664 AND c.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
665 AND ( (c.referenced_name <> c.name) or (c.referenced_owner <> c.owner) OR (c.referenced_type <> 'PACKAGE'))
666 )
667 )
668 order by decode(object_type,
669 'TYPE', 1, /* most types depend on native or other types */
670 'SYNONYM', 2, /* synonyms point to tables and types */
671 'PACKAGE', 3, /* packages can depend on types */
672 'VIEW', 4, /* views depend on packages and synonyms */
673 'TYPE BODY', 5,
674 'PACKAGE BODY', 6,
675 7), object_name;
676
677
678 cursor C_EDITION(x_owner varchar2 ,x_object_name varchar2 ,x_object_type varchar2) is
679 select edition_name
680 from dba_objects
681 where owner = x_owner
682 and object_name = x_object_name
683 and object_type = x_object_type;
684
685 BEGIN
686 --
687 -- Acualize all objects in current edition
688 -- construct alter <object type> <object_name> compile <reuse settings>
689 --
690
691 select oracle_username into L_APPLSYS
692 from system.fnd_oracle_userid
693 where read_only_flag = 'E';
694
695 if (x_list = 'LIST1')
696 then
697 L_PHASE := 'ACTUALIZE_PARENT_OBJS';
698 open c_inherited_objects_list1(x_edition);
699 elsif (x_list = 'LIST2')
700 then
701 L_PHASE := 'ACTUALIZE_CHILD_OBJS';
702 open c_inherited_objects_list2(x_edition);
703 else
704 open c_inherited_objects(x_edition);
705 end if;
706
707
708 loop
709 if (x_list = 'LIST1')
710 then
711 fetch c_inherited_objects_list1 into l_owner, l_object_name,l_object_type;
712 exit when (c_inherited_objects_list1%NOTFOUND or (c_inherited_objects_list1%NOTFOUND is null));
713 elsif (x_list = 'LIST2')
714 then
715 fetch c_inherited_objects_list2 into l_owner, l_object_name,l_object_type;
719 exit when (c_inherited_objects%NOTFOUND or (c_inherited_objects%NOTFOUND is null));
716 exit when (c_inherited_objects_list2%NOTFOUND or (c_inherited_objects_list2%NOTFOUND is null));
717 else
718 fetch c_inherited_objects into l_owner, l_object_name,l_object_type;
720 end if;
721
722 if (x_execute)
723 then
724 --
725 -- Check if the Object has already been
726 -- actualized due to dependencies
727 --
728 open c_edition (l_owner,l_object_name,l_object_type);
729 fetch c_edition into l_obj_edition;
730 close c_edition;
731
732 if l_obj_edition <> x_edition
733 then
734 l_stmt := construct_actualize_ddl(l_owner,l_object_name,l_object_type);
735
736 begin
737 execute immediate l_stmt;
738 exception
739 when success_with_compile_err then
740 null;
741 when others then
742 /*
743 ** Log the error-ed DDLs in AD_ZD_LOGS table
744 */
745 L_ERRMSG := sqlerrm;
746
747 begin
748 execute immediate 'insert into '|| L_APPLSYS ||'.AD_ZD_LOGS(LOG_SEQUENCE,MODULE,MESSAGE_TEXT,SESSION_ID,TYPE,TIMESTAMP) values
749 ('|| L_APPLSYS ||'.AD_ZD_LOGS_S.nextval,''ACTUALIZE_ALL'',''DDL : '|| l_stmt ||' ; error = '|| l_errmsg || ''',SYS_CONTEXT(''USERENV'', ''SESSIONID''),''ERROR'',SYSDATE) ';
750 exception
751 when others then
752 --raise;
753 null;
754 end;
755 end;
756 else
757 --
758 -- Object already Actualized
759 --
760 null;
761 end if;
762
763 else /* x_execute is false. Parallel workers mode */
764
765 l_sql := 'insert into '|| l_applsys ||'.ad_zd_ddl_handler ' ||
766 '(phase, ddl_id, sql_lob, executed, status) values (''' || l_phase ||
767 q'[',]' || l_applsys ||'.ad_zd_ddl_handler_ddl_s.nextval, ' ||
768 q'['begin sys.ad_zd_sys.actualize_object('']' || l_owner||
769 q'['','']' || l_object_name ||
770 q'['','']' || l_object_type ||
771 q'[''); end;', 'N', 'NOT-EXEC')]' ;
772
773 execute immediate l_sql;
774 end if;
775 end loop;
776
777 if (x_list = 'LIST1')
778 then
779 close c_inherited_objects_list1;
780 elsif (x_list = 'LIST2')
781 then
782 close c_inherited_objects_list2;
783 else
784 close c_inherited_objects;
785 end if;
786
787 END;
788
789
790 /*
791 ** Actualize all
792 ** - Actualize All Objects in the PATCH edition
793 **
794 ** x_execute - Default value, True means Execute the DDLs immediately
795 ** - False means, use the parallel workers architecture
796 */
797 PROCEDURE ACTUALIZE_ALL(x_execute in boolean default true)
798 IS
799 L_DEFAULT_EDITION varchar2(30);
800 L_CURRENT_EDITION varchar2(30);
801 L_PATCH_EDITION varchar2(30);
802 L_APPS_SCHEMA varchar2(30);
803 L_STMT varchar2(2000);
804 L_OWNER varchar2(30);
805 L_OBJECT_NAME varchar2(128);
806 L_OBJECT_TYPE varchar2(30);
807 C_MODULE varchar2(80) := c_package||'ACTUALIZE_ALL';
808
809 cursor C_ADZD_OBJECTS(x_edition varchar2) is
810 select
811 o.owner
812 , o.object_name
813 , o.object_type
814 from
815 dba_objects o
816 where o.edition_name <> x_edition
817 and o.owner in ( select username from dba_users where editions_enabled = 'Y')
818 and o.object_name like 'AD_ZD%'
819 and o.object_type in ('PACKAGE','PACKAGE BODY')
820 order by o.owner,
821 decode(o.object_type,
822 'PACKAGE', 1,
823 'PACKAGE BODY', 2), o.object_name;
824
825 begin
826 log(c_module, 'PROCEDURE', 'begin - execute');
827
828 l_current_edition := Sys_Context('Userenv', 'Current_Edition_Name');
829
830 select property_value into l_default_edition
831 from database_properties
832 where property_name = 'DEFAULT_EDITION';
833
834 begin
835 select aed.edition_name into l_patch_edition
836 from all_editions AED
837 where aed.parent_edition_name = l_default_edition;
838 exception
839 when no_data_found then
840 l_patch_edition := NULL;
841 end;
842
843 if l_current_edition <> l_patch_edition then
844 raise_application_error(-20005, 'ERROR: Actualize All can only be run in the PATCH edition');
845 end if;
846
847 select oracle_username into L_APPS_SCHEMA
848 from system.fnd_oracle_userid
849 where read_only_flag ='U';
850
851 log(c_module, 'EVENT', 'Begin Actualize for AD_ZD% packages');
852 /*
853 ** Bug#14469886
854 ** ALL AD_ZD packages are compiled before itself to
855 ** avoid errors while running acutalize all in parallel workers mode.
856 ** Especially it is needed for AD_ZD, AD_ZD_LOG, AD_ZD_PARALLEL_EXEC packages.
857 ** But it doesn't harm to compile all AD_ZD packages in before hand itself during execute
858 ** immediate mode also.
859 */
860 open c_adzd_objects(l_current_edition);
861 loop
862 fetch c_adzd_objects into l_owner, l_object_name,l_object_type;
863 exit when (c_adzd_objects%NOTFOUND or (c_adzd_objects%NOTFOUND is null));
864
865 l_stmt := construct_actualize_ddl(l_owner,l_object_name,l_object_type);
866
867 begin
868 execute immediate l_stmt;
869 exception
870 when others then
871 null;
872 end;
873 end loop;
874
875 close c_adzd_objects;
876
877 log(c_module, 'EVENT', 'End Actualize for AD_ZD% packages');
878
879 if (x_execute)
880 then
881 PROCESS_INHERITED_OBJS_LIST('LIST3',l_current_edition,x_execute);
882 else
883 PROCESS_INHERITED_OBJS_LIST('LIST1',l_current_edition,x_execute);
884 PROCESS_INHERITED_OBJS_LIST('LIST2',l_current_edition,x_execute);
885 end if;
886 commit;
887
888 end ACTUALIZE_ALL;
889
890
891 END AD_ZD_SYS;