1 package body ad_parallel_compile_pkg as
2 /* $Header: adpcpspb.pls 120.1 2005/12/22 03:30:04 vpalakur noship $ */
3
4 procedure get_invalid
5 (schema_to_compile in varchar2,
6 obj_type_to_exclude in varchar2)
7 is
8 -- |*=====================================================================*
9 -- | DESCRIPTON
10 -- | This procedure is used to get a list of invalid objects and insert
11 -- | the name and related information into the AD_PARALLEL_COMPILE table.
12 -- |
13 -- | USAGE
14 -- | Specify obj_type_to_exclude as "NONE" to get all invalid objects in
15 -- | the schema. Specify obj_type_to_exclude as a special code for the
16 -- | type of object to exclude. Currently-supported codes are:
17 -- |
18 -- | "BODY" to exclude package bodies, "NONE" to not exclude anything
19 -- |
20 -- | EXAMPLE
21 -- | get_invalid('APPS','NONE');
22 -- |
23 -- | HISTORY
24 -- | 06-Mar-02 W Jenkins Bug 2236912: Modified to use the base tables
25 -- | rather than the dba_objects view for
26 -- | performance reasons
27 -- |*=====================================================================*
28
29 -- declare local variable.
30 sName sys.user$.name%type;
31
32 begin
33
34 -- uppercase the schema name once rather
35 -- than each time it is used in a select statement
36
37 sName := upper(schema_to_compile);
38
39 --
40 -- get invalid objects in schema_to_compile
41 --
42 begin
43 if ( upper(obj_type_to_exclude) = 'NONE' )
44 then
45 insert into
46 ad_parallel_compile (obj#,
47 owner,
48 name,
49 type,
50 depend_level,
51 worker_number,
52 status,
53 sys_stat)
54 select /*+ leading(o) */
55 o.obj#,
56 u.name,
57 o.name,
58 decode(o.type#, 4,'VIEW',
59 7,'PROCEDURE',
60 8,'FUNCTION',
61 9,'PACKAGE',
62 11,'PACKAGE BODY',
63 12,'TRIGGER',
64 29,'JAVA CLASS',
65 42,'MATERIALIZED VIEW',
66 'UNDEFINED'),
67 0,
68 0,
69 'INVALID',
70 o.status
71 from
72 sys.obj$ o,
73 sys.user$ u
74 where
75 u.user# = o.owner#
76 and u.name = sName
77 and o.name not like 'BIN$%'
78 and o.type# in (4,7,8,9,11,12,29,42)
79 and o.status >1;
80
81 elsif ( upper(obj_type_to_exclude) = 'BODY' )
82 then
83 insert into
84 ad_parallel_compile (obj#,
85 owner,
86 name,
87 type,
88 depend_level,
89 worker_number,
90 status,
91 sys_stat)
92 select /*+ leading(o) */
93 o.obj#,
94 u.name,
95 o.name,
96 decode(o.type#, 4,'VIEW',
97 7,'PROCEDURE',
98 8,'FUNCTION',
99 9,'PACKAGE',
100 11,'PACKAGE BODY',
101 12,'TRIGGER',
102 29,'JAVA CLASS',
103 42,'MATERIALIZED VIEW',
104 'UNDEFINED'),
105 0,
106 0,
107 'INVALID',
108 o.status
109 from
110 sys.obj$ o,
111 sys.user$ u
112 where
113 u.user# = o.owner#
114 and u.name = sName
115 and o.name not like 'BIN$%'
116 and o.type# in (4,7,8,9,12,29,42)
117 and o.status > 1;
118
119 else
120 raise_application_error(-20000,
121 'Invalid obj_type_to_exclude: '||obj_type_to_exclude||'.');
122 end if;
123
124 exception
125 when others then
126 raise_application_error(-20000,SQLERRM||' while inserting into AD_PARALLEL_COMPILE in '||
127 'AD_PARALLEL_COMPILE_PKG.GET_INVALID()');
128 end;
129
130 --
131 -- delete rows for disabled triggers
132 -- we won't try to compile disabled triggers even if they are invalid
133 --
134 begin
135 delete from ad_parallel_compile
136 where obj# in
137 (select /*+ leading(t) use_nl(o) */
138 o.obj#
139 from
140 sys.obj$ o,
141 sys.trigger$ t,
142 sys.user$ u
143 where
144 u.user# = o.owner#
145 and u.name = sName
146 and o.obj# = t.obj#
147 and o.type# = 12
148 and o.status = 3
149 and t.enabled = 0);
150 exception
151 when others then
152 raise_application_error(-20000,SQLERRM||' while deleting from AD_PARALLEL_COMPILE in '||
153 'AD_PARALLEL_COMPILE_PKG.GET_INVALID()');
154 end;
155 end get_invalid;
156
157 procedure delete_errobjs
158 (schema_to_compile in varchar2)
159 is
160 -- |*=====================================================================*
161 -- | DESCRIPTON
162 -- | Delete the objects from the parallel compile table
163 -- | that are also in the dba_errors table. There is no
164 -- | point in trying to recompile these objects, as they clearly
165 -- | have errors. We just want to catch any potentially valid
166 -- | objects that slipped through the cracks...
167 -- |
168 -- | USAGE
169 -- | Specify the schema name to delete the error ojjects from.
170 -- |
171 -- | EXAMPLE
172 -- | delete_errobjs('APPS');
173 -- |
174 -- | HISTORY
175 -- | 06-Mar-02 W Jenkins Bug 2236912: Modified to prepare the
176 -- | schema name for the select statement as Oracle
177 -- | won't use the index if a function is used.
178 -- |
179 -- |*=====================================================================*
180
181 -- Declare local variable
182 sName sys.user$.name%type;
183
184 begin
185
186 -- Uppercase the schema name once rather
187 -- than each time it is used in a select statement
188
189 sName := upper(schema_to_compile);
190
191 delete from ad_parallel_compile
192 where obj# in
193 (select
194 o.obj#
195 from
196 sys.obj$ o,
197 sys.error$ e,
198 sys.user$ u
199 where
200 u.name = sName
201 and o.owner# = u.user#
202 and e.obj# = o.obj#
203 and e.sequence# = 1);
204
205 end delete_errobjs;
206
207 procedure update_done
208 is
209 begin
210 --
211 -- possible corner-case error: object changes from one invalid status
212 -- to another invalid status. I hope this doesn't happen
213 --
214 update ad_parallel_compile
215 set status = 'VALID'
216 where obj# in
217 (select pc.obj#
218 from ad_parallel_compile pc, sys.obj$ o
219 where pc.obj# = o.obj#
220 and pc.sys_stat <> o.status);
221 end update_done;
222
223
224 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
225 -- sraghuve (07/05/2004)
226
227
228 procedure comp_dependencies
229 (schema_to_compile in varchar2)
230 is
231 begin
232 comp_dependencies
233 (schema_to_compile => schema_to_compile,
234 use_stored_dependencies => 'FALSE');
235
236 end;
237
238
239 procedure comp_dependencies
240 (schema_to_compile in varchar2,
241 use_stored_dependencies in varchar2)
242 is
243 -- |*=====================================================================*
244 -- | DESCRIPTON
245 -- | Compute interdependencies between invalid objects using the
246 -- | sys.dependency$ table
247 -- |
248 -- | USAGE
249 -- | Compiles dependencies between invalid objects.
250 -- |
251 -- | EXAMPLE
252 -- | comp_dependencies(schema_to_compile,use_stored_dependencies);
253 -- |
254 -- | HISTORY
255 -- | 06-Mar-02 W Jenkins Bug 2236912: Modified to prepare the
256 -- | schema name for the select statement as Oracle
257 -- | won't use the index if a function is used.
258 -- |
259 -- |*=====================================================================*
260
261 -- Declare local variable
262 sName sys.user$.name%type;
263 rows_processed integer;
264 tree_depth integer;
265 max_tree_depth integer;
266 begin
267
268 -- Uppercase the schema name once rather
269 -- than each time it is used in a select statement
270 sName := upper(schema_to_compile);
271 if ( upper(nvl(use_stored_dependencies,'FALSE')) <> 'TRUE' )
272 then
273 tree_depth := 0;
274 rows_processed := 1;
275 --
276 -- Just in case we get a circular dependency. The RDBMS should not
277 -- allow this for packages, but we've seen it on dev115 several
278 -- times now...
279 --
280 max_tree_depth := 20;
281
282 while rows_processed <> 0 loop
283
284 tree_depth := tree_depth + 1;
285
286 -- BUG 3610682, sallamse 31/05/2004
287 -- Added d_owner# while querying sys.dependency$ table.
288
289 update ad_parallel_compile
290 set depend_level = tree_depth
291 where obj# in
292 (select d.d_obj#
293 from sys.dependency$ d
294 where d.p_obj# in
295 (select p.obj#+0
296 from ad_parallel_compile p
297 where p.owner = nvl(sName, p.owner)
298 and p.depend_level = tree_depth - 1)
299 and d.d_owner# = (select user#
300 from sys.user$ where name = sName));
301
302 rows_processed := SQL%ROWCOUNT;
303
304 -- Handle circular depencencies, if any
305
306 if ( tree_depth >= max_tree_depth )
307 then
308 rows_processed := 0;
309 end if;
310 end loop;
311 else
312 --
313 -- Load object interdependency information from ad_apps_dependencies
314 -- ad_apps_dependencies has interdependency information from
315 -- a fresh install DB
316 --
317
318 --
319 -- get correct object IDs for objects stored in ad_apps_dependencies
320 --
321
322 update ad_apps_dependencies d
323 set obj# =
324 (select object_id
325 from dba_objects
326 where owner = sName
327 and object_name = d.name
328 and object_type = d.type);
329
330 --
331 -- Set depencency levels in ad_parallel_compile based on dependency
332 -- levels in ad_apps_dependencies
333 --
334
335 update ad_parallel_compile c
336 set c.depend_level =
337 (select d.depend_level
338 from ad_apps_dependencies d
339 where d.obj# = c.obj#)
340 where exists (
341 select 'x'
342 from ad_apps_dependencies d
343 where d.obj# = c.obj#);
344
345 end if;
346 end comp_dependencies;
347
348 --
349 -- get full name for a Java object. We are using dynamic SQL so as to
350 -- remove dependency on DBMS_JAVA.
351 --
352 function get_complete_name(object_name in varchar2) return varchar2
353 is
354 l_complete_name varchar2(3000);
355 begin
356 EXECUTE IMMEDIATE
357 'select dbms_java.longname(:object_name) from dual'
358 INTO l_complete_name
359 USING object_name;
360
361 return(l_complete_name);
362 exception
363 when others then
364 --
365 -- if dbms_java does not exist, handle the error and return the
366 -- original object name
367 --
368 if (sqlcode = -904) then
369 return(object_name);
370 else
371 raise;
372 end if;
373 end;
374
375 --
376 -- function to check the status of a given object and return TRUE if
377 -- the object is in INVALID state.
378 --
379 function is_object_invalid(schema_to_check in varchar2,
380 object_name_to_check in varchar2,
381 object_type_to_check in varchar2) return boolean
382 is
383 -- |*=====================================================================*
384 -- | DESCRIPTON
385 -- | Check for the status of the object name passed in and return TRUE
386 -- | or FALSE depending on the state.
387 -- |
388 -- | USAGE
389 -- | Checks for invalid state of an object in the dba_objects table.
390 -- |
391 -- |
392 -- | HISTORY
393 -- | 06-Mar-02 W Jenkins Bug 2236912: Modified to prepare the
394 -- | schema name for the select statement as Oracle
395 -- | won't use an index if a function is used.
396 -- |
397 -- |*=====================================================================*
398
399 -- Declare local variable
400 sName sys.user$.name%type;
401 l_status varchar2(30);
402 begin
403 -- Uppercase the schema name once rather
404 -- than each time it is used in a select statement
405 sName := upper(schema_to_check);
406 select status
407 into l_status
408 from dba_objects
409 where owner = sName
410 and object_name = object_name_to_check
414 return(TRUE);
411 and object_type = object_type_to_check
412 and status = 'INVALID';
413
415 exception
416 when no_data_found then
417 return(FALSE);
418 end is_object_invalid;
419
420
421 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
422 -- sraghuve (07/05/2004)
423
424
425 procedure assign_tasks
426 (schema_to_compile in varchar2,
427 number_of_workers in integer)
428 is
429 begin
430
431 assign_tasks
432 (schema_to_compile => schema_to_compile,
433 number_of_workers => number_of_workers,
434 distribute_java => 'N');
435
436 end;
437
438 procedure assign_tasks
439 (schema_to_compile in varchar2,
440 number_of_workers in integer,
441 distribute_java in varchar2)
442 is
443 -- |*=====================================================================*
444 -- | DESCRIPTON
445 -- | Assigns tasks to workers.
446 -- |
447 -- | HISTORY
448 -- | 06-Mar-02 W Jenkins Bug 2236912: Modified to prepare the
449 -- | schema name for the select statement as Oracle
450 -- | won't use an index if a function is used.
451 -- |
452 -- |*=====================================================================*
453
454 cursor cs1 (c_schema_to_compile varchar2,
455 c_distribute_java varchar2) is
456 select * from ad_parallel_compile
457 where owner = c_schema_to_compile
458 and (c_distribute_java = 'Y'
459 or
460 (c_distribute_java = 'N'
461 and
462 type <> 'JAVA CLASS')
463 )
464 order by depend_level,
465 decode(type, 'PACKAGE',1,'VIEW',2,'PACKAGE BODY',4,3),
466 name;
467
468 -- Declare local variable
469 sName sys.user$.name%type;
470 counter pls_integer;
471 num_workers_local pls_integer;
472 num_java_classes pls_integer := 0;
473 num_jobs_per_worker pls_integer := 0;
474
475 begin
476
477 -- Uppercase the schema name once rather
478 -- than each time it is used in a select statement
479 sName := upper(schema_to_compile);
480
481 num_workers_local := number_of_workers;
482 counter := 1;
483 --
484 -- first assign all java classes to worker 1
485 --
486 if (distribute_java = 'N')
487 then
488 update ad_parallel_compile
489 set worker_number = 1
490 where owner = sName
491 and type = 'JAVA CLASS';
492
493 num_java_classes := sql%rowcount;
494 end if;
495
496 for csrec in cs1(sName, distribute_java) loop
497
498 if mod(counter, num_workers_local + 1) = 0
499 then
500 --
501 -- don't assign jobs to worker 1 until the number of
502 -- jobs assigned to other workers exceed the number
503 -- of java class compilation jobs assigned to worker 1
504 --
505 if (distribute_java = 'N' and number_of_workers > 2)
506 then
507 if (num_jobs_per_worker < num_java_classes)
508 then
509 counter := 2;
510 else
511 counter := 1;
512 end if;
513 else
514 counter := 1;
515 end if;
516
517 num_jobs_per_worker := num_jobs_per_worker + 1;
518 end if;
519
520 update ad_parallel_compile
521 set worker_number = counter
522 where obj# = csrec.obj#;
523
524 counter := counter + 1;
525
526 end loop;
527 end assign_tasks;
528
529 procedure assign_tasks_single
530 (schema_to_compile in varchar2,
531 log_worker_num in integer)
532 is
533 begin
534 update ad_parallel_compile
535 set worker_number = log_worker_num
536 where upper(owner) = upper(schema_to_compile);
537 end assign_tasks_single;
538
539 procedure comp
540 (worker_number in integer,
541 target_schema in varchar2)
542 is
543 success_with_comp_error exception;
544 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
545
546 cursor cs1 (c_worker_number number,
547 c_target_schema varchar2) is
548 select type, substr(name,1,30) name
549 from ad_parallel_compile
550 where owner = c_target_schema
551 and worker_number = c_worker_number
552 and status = 'INVALID'
553 order by depend_level,
554 decode(type, 'PACKAGE',1,'VIEW',2,'PACKAGE BODY',4,3),
555 name;
556
557 l_target_schema varchar2(100);
558 l_chkobj_status boolean;
559 tmp_worker integer;
560 begin
561 l_target_schema := upper(target_schema);
562
563 -- clear error buffer
564 ad_apps_private.error_buf := null;
565
566 -- Check for APPS*DDL packages in target schema
567 ad_apps_private.check_for_apps_ddl(l_target_schema);
568
569 for csrec in cs1 (worker_number, l_target_schema) loop
573 timestamp varchar2(20);
570 declare
571 statement varchar2(500);
572 sql_error varchar2(1996);
574 begin
575
576 l_chkobj_status := FALSE;
577
578 if csrec.type = 'PACKAGE' then
579 statement := 'ALTER PACKAGE "'|| csrec.name ||
580 '" COMPILE SPECIFICATION';
581 elsif csrec.type = 'PACKAGE BODY' then
582 statement := 'ALTER PACKAGE "'|| csrec.name ||
583 '" COMPILE BODY';
584 elsif csrec.type = 'MATERIALIZED VIEW' then
585 statement := 'ALTER MATERIALIZED VIEW "'|| csrec.name ||
586 '" COMPILE ';
587 elsif csrec.type = 'JAVA CLASS' then
588 l_chkobj_status := TRUE;
589 statement := 'ALTER JAVA CLASS "'|| get_complete_name(csrec.name) ||
590 '" COMPILE';
591 else
592 statement := 'ALTER '|| csrec.type ||' "'||
593 csrec.name || '" COMPILE';
594 end if;
595
596 if (l_chkobj_status) then
597 if (is_object_invalid(l_target_schema, csrec.name, csrec.type)) then
598 ad_apps_private.do_apps_ddl(l_target_schema, statement);
599 end if;
600 else
601 ad_apps_private.do_apps_ddl(l_target_schema, statement);
602 end if;
603 exception
604 when success_with_comp_error then
605 --
606 -- Trap and ignore ORA-24344: success with compilation error
607 -- This only happens on ORACLE 8
608 --
609 ad_apps_private.error_buf := null;
610 when others then
611 --
612 -- Insert SQL errors we encounter while compiling invalid objects
613 -- into AD_PARALLEL_COMPILE_ERRORS table. Most of these are RDBMS
614 -- bugs, but we need to trap (and later display) them anyway.
615 --
616 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
617
618
619 if (statement IS NULL) then
620 statement := 'Statement is NULL!';
621 end if;
622 if (l_target_schema IS NULL) then
623 l_target_schema := 'Unknown schema';
624 end if;
625
626 -- Since we can't modify worker_number (it is an IN parameter)
627 -- set the tmp_worker variable to whatever the worker_number is
628 -- unless it is null.
629 if (worker_number IS NULL) then
630 tmp_worker := -1;
631 else
632 tmp_worker := worker_number;
633 end if;
634
635 if (timestamp IS NULL) then
636 timestamp := 'Unknown timestamp';
637 end if;
638
639 insert into
640 ad_parallel_compile_errors (owner,
641 worker_number,
642 timestamp,
643 type,
644 text)
645 values (l_target_schema,
646 tmp_worker,
647 timestamp,
648 '1 SQL TEXT',
649 statement);
650
651 sql_error := SQLERRM;
652
653 insert into
654 ad_parallel_compile_errors (owner,
655 worker_number,
656 timestamp,
657 type,
658 text)
659 values (l_target_schema,
660 tmp_worker,
661 timestamp,
662 '2 ORACLE_ERROR',
663 sql_error);
664
665 if ad_apps_private.error_buf is not null then
666 insert into
667 ad_parallel_compile_errors (owner,
668 worker_number,
669 timestamp,
670 type,
671 text)
672 values (l_target_schema,
673 tmp_worker,
674 timestamp,
675 '3 ERROR_BUF',
676 substr(ad_apps_private.error_buf,
677 1,1996));
678 end if;
679
680 -- clear error buf
681 ad_apps_private.error_buf := null;
682 end;
683 end loop;
684 end comp;
685
686 end ad_parallel_compile_pkg;
[Home] [Help]