[Home] [Help]
PACKAGE BODY: APPS.ASG_COMPILE_PKG
Source
1 PACKAGE BODY ASG_COMPILE_PKG as
2 /* $Header: asgcompb.pls 120.1 2005/08/12 02:43:35 saradhak noship $ */
3
4 -- HISTORY
5
6 -- Dec 30, 2003 yazhang add overload method
7 -- JULY 24, 2002 ytian created.
8
9 PROCEDURE compile_all_objects (schema_name in VARCHAR2) as
10
11 -- compile package specifications first, then views, then bodies
12 -- this is because a view could reference a package header
13 cursor c1 is
14 select object_name, object_type from all_objects
15 where status = 'INVALID'
16 and object_type = 'PACKAGE'
17 and owner=schema_name;
18
19 cursor c2 is
20 select object_name, object_type from all_objects
21 where status = 'INVALID'
22 and object_type = 'VIEW'
23 and owner=schema_name ;
24
25 --
26 -- The select statement here is more complicated because we
27 -- have coded it to ignore disabled triggers (even if invalid)
28 --
29 cursor c3 is
30 select decode(o.type#,9,1,2) dummy,
31 o.name object_name,
32 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE',
33 3, 'CLUSTER',
34 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
35 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
36 11, 'PACKAGE BODY', 12, 'TRIGGER',
37 13, 'TYPE', 14, 'TYPE BODY',
38 19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
39 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
40 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
41 30, 'JAVA RESOURCE',
42 32, 'INDEXTYPE', 33, 'OPERATOR',
43 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
44 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
45 43, 'DIMENSION',
46 44, 'CONTEXT', 47, 'RESOURCE PLAN',
47 48, 'CONSUMER GROUP',
48 51, 'SUBSCRIPTION', 52, 'LOCATION',
49 'UNDEFINED') object_type
50 from sys.obj$ o, sys.user$ u
51 where o.owner# = u.user#
52 and u.name = schema_name
53
54 and o.status <> 1 /* not valid status */
55 -- and o.name like '%%'
56 and o.type# not in (28, 29, 30,12) /* exclude Java stuff for now + trigger*/
57 -- and not exists ( select 'x' from sys.trigger$ t
58 -- where o.obj# = t.obj#
59 -- and t.enabled = 0)
60 order by 1;
61 c integer;
62
63 rows_processed integer;
64 statement varchar2(100);
65 object_type1 varchar2(30);
66 object_type2 varchar2(30);
67
68 success_with_comp_error exception;
69 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
70 begin
71 -- first compile all invalid packages specifications
72 for c1rec in c1 loop
73 -- for each invalid object compile
74 begin
75 statement := 'ALTER PACKAGE '||schema_name ||'.' ||c1rec.object_name||
76 ' COMPILE SPECIFICATION';
77
78 c := dbms_sql.open_cursor;
79 dbms_sql.parse(c, statement, dbms_sql.native);
80 rows_processed := dbms_sql.execute(c);
81 dbms_sql.close_cursor(c);
82 exception
83 when success_with_comp_error then
84 --
85 -- Trap and ignore ORA-24344: success with compilation error
86 -- This only happens on ORACLE 8
87 --
88 dbms_sql.close_cursor(c);
89 when others then
90 dbms_sql.close_cursor(c);
91 raise;
92 end;
93
94 end loop; -- loop over all invalid packages
95 -- next compile all invalid views
96 for c2rec in c2 loop
97 -- for each invalid object compile
98 begin
99 statement := 'ALTER VIEW '||schema_name||'.'||c2rec.object_name||' COMPILE';
100
101 c := dbms_sql.open_cursor;
102 dbms_sql.parse(c, statement, dbms_sql.native);
103 rows_processed := dbms_sql.execute(c);
104 dbms_sql.close_cursor(c);
105 exception
106 when success_with_comp_error then
107 dbms_sql.close_cursor(c);
108 when others then
109 dbms_sql.close_cursor(c);
110 raise;
111 end;
112 end loop; -- loop over all invalid views
113 -- last, get all remaining invalid objects, which could be package bodies
114 -- unpackaged procedures or functions, or triggers
115 for c3rec in c3 loop
116 -- for each invalid object compile
117 begin
118 object_type1 := c3rec.object_type;
119 object_type2 := null;
120
121 if object_type1 = 'PACKAGE BODY' then
122 object_type1 := 'PACKAGE';
123 object_type2 := 'BODY';
124 elsif object_type1 = 'PACKAGE' then
125 object_type1 := 'PACKAGE';
126 object_type2 := 'SPECIFICATION';
127 elsif object_type1 = 'TYPE' then
128 object_type1 := 'TYPE';
129 object_type2 := 'SPECIFICATION';
130 elsif object_type1 = 'TYPE BODY' then
131 object_type1 := 'TYPE';
132 object_type2 := 'BODY';
133 end if;
134
135 statement := 'ALTER '||object_type1||' '||schema_name||'.'||c3rec.object_name||
136 ' COMPILE '||object_type2;
137
138 if c3rec.object_type <> 'UNDEFINED' then
139 c := dbms_sql.open_cursor;
140 dbms_sql.parse(c, statement, dbms_sql.native);
141 rows_processed := dbms_sql.execute(c);
142 dbms_sql.close_cursor(c);
143 end if;
144
145 exception
146 when success_with_comp_error then
147 dbms_sql.close_cursor(c);
148 when others then
149 dbms_sql.close_cursor(c);
150 raise;
151 end;
152 end loop; -- loop over all remaining invalid objects
153 end compile_all_objects;
154
155
156 PROCEDURE compile_all_objects as
157
158 -- compile package specifications first, then views, then bodies
159 -- this is because a view could reference a package header
160 cursor c1 is
161 select object_name, object_type from user_objects
162 where status = 'INVALID'
163 and object_type = 'PACKAGE' ;
164
165 cursor c2 is
166 select object_name, object_type from user_objects
167 where status = 'INVALID'
168 and object_type = 'VIEW' ;
169
170 --
171 -- The select statement here is more complicated because we
172 -- have coded it to ignore disabled triggers (even if invalid)
173 --
174 cursor c3 is
175 select decode(o.type#,9,1,2) dummy,
176 o.name object_name,
177 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE',
178 3, 'CLUSTER',
179 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
180 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
181 11, 'PACKAGE BODY', 12, 'TRIGGER',
182 13, 'TYPE', 14, 'TYPE BODY',
183 19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
184 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
185 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
186 30, 'JAVA RESOURCE',
187 32, 'INDEXTYPE', 33, 'OPERATOR',
188 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
189 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
190 43, 'DIMENSION',
191 44, 'CONTEXT', 47, 'RESOURCE PLAN',
192 48, 'CONSUMER GROUP',
193 51, 'SUBSCRIPTION', 52, 'LOCATION',
194 'UNDEFINED') object_type
195 from sys.obj$ o
196 where o.owner# = userenv('SCHEMAID')
197 and o.status <> 1 /* not valid status */
198 -- and o.name like '%%'
199 and o.type# not in (28, 29, 30,12) /* exclude Java stuff for now + trigger*/
200 -- and not exists ( select 'x' from sys.trigger$ t
201 -- where o.obj# = t.obj#
202 -- and t.enabled = 0)
203 order by 1;
204 c integer;
205
206 rows_processed integer;
207 statement varchar2(100);
208 object_type1 varchar2(30);
209 object_type2 varchar2(30);
210
211 success_with_comp_error exception;
212 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
213 begin
214 -- first compile all invalid packages specifications
215 for c1rec in c1 loop
216 -- for each invalid object compile
217 begin
218 statement := 'ALTER PACKAGE '||c1rec.object_name||
219 ' COMPILE SPECIFICATION';
220
221 c := dbms_sql.open_cursor;
222 dbms_sql.parse(c, statement, dbms_sql.native);
223 rows_processed := dbms_sql.execute(c);
224 dbms_sql.close_cursor(c);
225 exception
226 when success_with_comp_error then
227 --
228 -- Trap and ignore ORA-24344: success with compilation error
229 -- This only happens on ORACLE 8
230 --
231 dbms_sql.close_cursor(c);
232 when others then
233 dbms_sql.close_cursor(c);
234 raise;
235 end;
236 end loop; -- loop over all invalid packages
237 -- next compile all invalid views
238 for c2rec in c2 loop
239 -- for each invalid object compile
240 begin
241 statement := 'ALTER VIEW '||c2rec.object_name||' COMPILE';
242
243 c := dbms_sql.open_cursor;
244 dbms_sql.parse(c, statement, dbms_sql.native);
245 rows_processed := dbms_sql.execute(c);
246 dbms_sql.close_cursor(c);
247 exception
248 when success_with_comp_error then
249 dbms_sql.close_cursor(c);
250 when others then
251 dbms_sql.close_cursor(c);
252 raise;
253 end;
254 end loop; -- loop over all invalid views
255 -- last, get all remaining invalid objects, which could be package bodies
256 -- unpackaged procedures or functions, or triggers
257 for c3rec in c3 loop
258 -- for each invalid object compile
259 begin
260 object_type1 := c3rec.object_type;
261 object_type2 := null;
262
263 if object_type1 = 'PACKAGE BODY' then
264 object_type1 := 'PACKAGE';
265 object_type2 := 'BODY';
266 elsif object_type1 = 'PACKAGE' then
267 object_type1 := 'PACKAGE';
268 object_type2 := 'SPECIFICATION';
269 elsif object_type1 = 'TYPE' then
270 object_type1 := 'TYPE';
271 object_type2 := 'SPECIFICATION';
272 elsif object_type1 = 'TYPE BODY' then
273 object_type1 := 'TYPE';
274 object_type2 := 'BODY';
275 end if;
276
277 statement := 'ALTER '||object_type1||' '||c3rec.object_name||
278 ' COMPILE '||object_type2;
279
280 if c3rec.object_type <> 'UNDEFINED' then
281 c := dbms_sql.open_cursor;
282 dbms_sql.parse(c, statement, dbms_sql.native);
283 rows_processed := dbms_sql.execute(c);
284 dbms_sql.close_cursor(c);
285 end if;
286
287 exception
288 when success_with_comp_error then
289 dbms_sql.close_cursor(c);
290 when others then
291 dbms_sql.close_cursor(c);
292 raise;
293 end;
294 end loop; -- loop over all remaining invalid objects
295 end compile_all_objects;
296
297
298 END ASG_COMPILE_PKG;