1 package body ad_compile as
2 /* $Header: adcmpb.pls 120.0 2005/05/25 11:39:11 appldev noship $ */
3
4 --
5 -- PL/SQL procedure to find and recompile all invalid APPS_DDL and
6 -- APPS_ARRAY_DDL packages in the database
7 --
8
9 procedure compile_apps_ddl is
10 cursor c1 is
11 select owner, object_type, object_name
12 from dba_objects
13 where object_type in ('PACKAGE', 'PACKAGE BODY')
14 and object_name in ('APPS_DDL', 'APPS_ARRAY_DDL')
15 and status = 'INVALID'
16 order by object_type, object_name desc;
17
18 success_with_comp_error exception;
19 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
20
21 c integer;
22 rows_processed integer;
23 statement varchar2(500);
24 error_buf varchar2(31744);
25 begin
26 for c1rec in c1 loop
27
28 -- build statement
29
30 if c1rec.object_type = 'PACKAGE' then
31
32 statement := 'alter package '||c1rec.owner||'.'||c1rec.object_name||
33 ' compile specification';
34
35 elsif c1rec.object_type = 'PACKAGE BODY' then
36
37 statement := 'alter package '||c1rec.owner||'.'||c1rec.object_name||
38 ' compile body';
39
40 else
41
42 statement := 'Why does '||c1rec.object_type||' '||c1rec.owner||'.'||
43 c1rec.object_name||' fail?';
44
45
46 end if;
47
48 --
49 -- Execute compile command in a sub-block so we can trap and handle errors
50 --
51 begin
52 c := dbms_sql.open_cursor;
53
54 dbms_sql.parse(c, statement, dbms_sql.native);
55 rows_processed := dbms_sql.execute(c);
56 dbms_sql.close_cursor(c);
57
58 exception
59 when success_with_comp_error then
60 --
61 -- Trap and ignore ORA-24344: success with compilation error
62 -- This only happens on ORACLE 8
63 --
64 dbms_sql.close_cursor(c);
65 when others then
66 dbms_sql.close_cursor(c);
67 raise_application_error(-20000, SQLERRM || ' [Statement is: '||
68 statement||']');
69 end;
70
71 end loop;
72
73 --
74 -- We should not have any invalid packages left
75 -- Look for them again, and fail if we find any
76 --
77
78 error_buf := null;
79
80 for c1rec in c1 loop
81
82 error_buf := error_buf || ' "' || c1rec.object_type ||' '||
83 c1rec.owner||'.'||c1rec.object_name||'"';
84
85 end loop;
86
87 if error_buf is not null then
88 raise_application_error(-20000,
89 'Please manually fix the following invalid packages:' || error_buf);
90 end if;
91
92 end compile_apps_ddl;
93
94 end ad_compile;
[Home] [Help]