1 PACKAGE BODY AD_ZD_PARALLEL_EXEC as
2 /* $Header: ADZDPEXB.pls 120.33.12020000.7 2013/05/21 08:52:29 seetsing ship $ */
3
4
5 /******************************************************************
6
7 Private APIs (Not defined in SPEC )
8
9 *******************************************************************/
10
11 -- log shortcut
12 procedure LOG(X_MODULE varchar2, X_LEVEL varchar2, X_MESSAGE varchar2) is
13 begin
14 ad_zd.log(x_module, x_level, x_message);
15 end;
16
17 -- error shortcut
18 procedure ERROR(X_MODULE varchar2, X_MESSAGE varchar2) is
19 begin
20 ad_zd.error(x_module, x_message);
21 end;
22
23 -- exec shortcut
24 procedure EXEC(X_SQL in varchar2, X_LOG_MOD in varchar2, X_IGNORE in boolean default false) is
25 begin
26 ad_zd.exec(x_sql, x_log_mod, x_ignore);
27 end;
28
29
30
31 /*
32 ** execute dynamic SQL statement
33 ** x_sql - statement to execute
34 ** x_log_mod - calling module (for logging)
35 ** x_ignore - ignore errors
36 */
37 procedure EXEC(
38 X_SQL in clob,
39 X_EDITION_NAME in varchar2,
40 X_LOG_MOD in varchar2,
41 X_IGNORE in boolean default false)
42 is
43 L_CUR integer;
44 L_RET integer;
45 begin
46
47 --log(x_log_mod, 'STATEMENT', 'SQL(CLOB): '||dbms_lob.substr(x_sql, 3900));
48
49 l_cur := dbms_sql.open_cursor(security_level=>2);
50 dbms_sql.parse(l_cur, x_sql, dbms_sql.native, x_edition_name, null, false);
51 l_ret := dbms_sql.execute(l_cur);
52 dbms_sql.close_cursor(l_cur);
53 commit;
54
55 exception
56
57 when others then
58 if dbms_sql.is_open(l_cur) then
59 dbms_sql.close_cursor(l_cur);
60 end if;
61 if x_ignore then
62 log(x_log_mod, 'STATEMENT', 'Ignored: '||SQLERRM);
63 else
64 log(x_log_mod, 'ERROR', 'ERROR: '||SQLERRM);
65 raise;
66 end if;
67
68 end exec;
69
70 --
71 -- Checks if given SQL is already exist?
72 function IS_DUPLICATE(X_PHASE varchar2,
73 X_SQL_LOB clob) return varchar2
74 is
75 L_EXIST varchar2(1);
76 begin
77
78 select 'Y' into l_exist from dual
79 where exists
80 ( select null
81 from ad_zd_ddl_handler
82 where phase = x_phase
83 and dbms_lob.compare(sql_lob, x_sql_lob) = 0
84 and nvl(executed, 'N') <> 'S' );
85 return 'Y';
86
87 exception
88 when no_data_found then
89 return 'N';
90
91 end IS_DUPLICATE;
92
93
94 --
95 -- Procedure to generate next batch id
96 --
97 FUNCTION GET_NEXT_DDL_ID RETURN NUMBER as
98 begin
99 return ad_zd_ddl_handler_ddl_s.nextval;
100 end GET_NEXT_DDL_ID;
101
102
103
104
105 /********************************************************************
106
107 Utility Functions / Procedures
108
109 *********************************************************************/
110
111
112 -- Returns the count of failed jobs.
113 --
114 -- If PHASE not supplied, it returns ALL failed jobs
115 -- TODO: This does not make sense, remove autoskip stuff
116 function GETFAILED_JOBS( X_PHASE VARCHAR2) return number
117 is
118 L_FAILED_COUNT pls_integer;
119 begin
120
121 -- 1. Check failed (FATAL, ERROR, RUNNING) count.
122 select count(1) into l_failed_count
123 from ad_zd_ddl_handler
124 where (x_phase is null or phase = x_phase)
125 and executed in (c_job_status_failed, c_job_status_running)
126 and nvl(status, 'ERROR') <> c_job_status_warning_desc;
127
128 return l_failed_count;
129 end GETFAILED_JOBS;
130
131
132 -- Get status of a stored DDL.
133 -- TODO: remove, not needed
134 function GET_STATUS(X_DDL_ID in number) return varchar2
135 is
136 L_STATUS varchar2(1) := 'S';
137 begin
138 select executed into l_status
139 from ad_zd_ddl_handler
140 where ddl_id = x_ddl_id;
141
142 return l_status;
143 exception
144 when no_data_found then
145 return l_status;
146 end GET_STATUS;
147
148
149 -- This function return the index of the phase ORDER.
150 function GET_PHASE_ORDER(X_PHASE varchar2) return number
151 is
152 begin
153
154 return
155 ( case x_phase
156 when C_PHASE_DROP_UNUSED_OBJECT then 1
157 when C_PHASE_COPY_TYPE then 2
158 when C_PHASE_COMPILE_TYPE then 3
159 when C_PHASE_COPY_EVOLVED_TYPE then 4
160 when C_PHASE_FIX_COLUMN then 5
161 when C_PHASE_FIX_TYPE then 6
162 when C_PHASE_FIX_PUBLIC_SYNONYM then 7
163 when C_PHASE_RECREATE_AQ_OBJECT then 8
164 when C_PHASE_DROP_OBJECT then 9
165 when C_PHASE_ENABLE_EDITIONING then 10
166 when C_PHASE_UPGRADE_TABLE then 11
167 when C_PHASE_UPGRADE_SEED then 12
168 when C_PHASE_COLLECT_STATS then 13
169 when C_PHASE_UPGRADE_MVIEW then 14
170 else 0
171 end );
172
173 end GET_PHASE_ORDER;
174
175
176 -- Returns the count of not_executed or running jobs
177 -- If PHASE not supplied, it returns ALL not-executed jobs
178 function GET_NOT_EXECUTED_JOBS(X_PHASE VARCHAR2) return number
179 is
180 L_NOT_EXECUTED_COUNT pls_integer;
181 begin
182 select count(1) into l_not_executed_count
183 from ad_zd_ddl_handler
184 where (x_phase is null or phase = x_phase)
185 and executed in (c_job_status_not_executed, c_job_status_running);
186
187 return l_not_executed_count;
188 end GET_NOT_EXECUTED_JOBS;
189
190
191 -- Updates DDL job status from I or R to N
192 -- Used when restarting a phase
193 procedure UPDATE_STATUS_R_TO_N(X_PHASE varchar2)
194 is
195 C_MODULE varchar2(127) := c_package||'update_status_r_to_n';
196 begin
197 log(c_module, 'EVENT', 'Reset jobs from R (running) to N (not executed) for phase '||x_phase);
198
199 update ad_zd_ddl_handler
200 set executed = c_job_status_not_executed,
201 execution_time = null
202 where (x_phase is null or phase= x_phase)
203 and executed = c_job_status_running;
204 commit;
205 end UPDATE_STATUS_R_TO_N;
206
207
208 --
209 -- Procedure to recompile INVALID objects
210 --
211 procedure RECOMPILE_OBJECTS
212 is
213 C_MODULE varchar2(80) := c_package||'recompile_objects';
214 begin
215 for obj in
216 ( select owner, object_name
217 from dba_invalid_objects
218 where object_name like 'AD_ZD%'
219 and object_type like 'PACKAGE%' )
220 loop
221 log(c_module, 'STATEMENT', 'Recompiling '||obj.owner||'.'||obj.object_name);
222 exec('ALTER PACKAGE '||obj.owner||'.'||obj.object_name||' COMPILE BODY', c_module, true);
223 end loop;
224 end recompile_objects;
225
226
227 -- Update job status
228 procedure UPDATEJOB_STATUS(
229 X_DDL_ID number,
230 X_EXECUTED varchar2,
231 X_STATUS varchar2,
232 X_ERROR CLOB)
233 is
234 C_MODULE varchar2(127) := c_package||'updatejob_status';
235 begin
236 update ad_zd_ddl_handler
237 set executed = x_executed,
238 status = nvl(x_status, c_job_status_success_desc),
239 execution_time =current_timestamp,
240 error = x_error
241 where ddl_id=x_ddl_id;
242 end UPDATEJOB_STATUS;
243
244
245 -- Return Error level based on error code
246 -- 'WARNING' - ignorable error
247 -- 'ERROR' - real error, attention required
248 -- 'FATAL' - unexpected error, processing must exit
249 function GET_ERROR_LEVEL(X_ERROR_CODE number, X_MESSAGE varchar2) return varchar2
250 is
251 begin
252
253 -- Special handling for 942: table or view does not exist
254 -- other than DROP statement, return as ERROR
255 if (x_error_code= -00942 and x_message is not null and
256 not (instr(x_message, 'DROP TABLE') > 0 or instr(x_message, 'DROP VIEW')>0) ) then
257 return c_job_status_error_desc;
258 end if;
259
260 return
261 ( case x_error_code
262 when -00942 then c_job_status_warning_desc /* table or view does not exist */
263 when -01031 then c_job_status_warning_desc /* Insufficient Privileges */
264 when -04043 then c_job_status_warning_desc /* object ... does not exist */
265 when -24344 then c_job_status_warning_desc /* success_with_compile_err */
266
267 when -00600 then c_job_status_fatal_desc /* Internal error code */
268 when -01013 then c_job_status_fatal_desc /* User cancel */
269 when -01652 then c_job_status_fatal_desc /* Unable to extend temp segment */
270 when -01653 then c_job_status_fatal_desc /* unable to extend table ... in tablespace ... */
271 when -01654 then c_job_status_fatal_desc /* unable to extend index ... in tablespace ... */
272 when -03113 then c_job_status_fatal_desc /* end-of-file on communication channel: */
273 when -04030 then c_job_status_fatal_desc /* out of process memory */
274 when -04031 then c_job_status_fatal_desc /* unable to allocate ... bytes of shared memory */
275 when -07445 then c_job_status_fatal_desc /* exception encountered: core dump */
276 when -20978 then c_job_status_fatal_desc /* Cannot drop a type with table dependents, EQ to ORA- */
277
278 else c_job_status_error_desc
279 end );
280
281 end GET_ERROR_LEVEL;
282
283
284
285
286 /******************************************************************
287
288 Processing APIs
289
290 *******************************************************************/
291 -- Loads a SQL into AD_ZD_DDL_HANDLER
292 -- X_PHASE : Phase of SQL
293 -- X_SQL : SQL or PL/SQL block
294 -- X_UNIQUE: If SQL should be checked for uniqueness within the given phase
295 --
296 -- NOTE: Currently this API does not validate if given "phase" is
297 -- a VALID phase or not.
298 PROCEDURE LOAD(
299 X_PHASE varchar2,
300 X_SQL clob,
301 X_UNIQUE boolean default false)
302 is
303 C_MODULE varchar2(127) := c_package||'load';
304 L_DDL_ID number;
305 begin
306
307 if (X_SQL is null or x_phase is null) then
308 error(c_module, 'Phase and SQL cannot be null');
309 return;
310 end if;
311
312 -- Supplied SQL should be checked for uniqueness?
313 if(x_unique) then
314 -- If already exists then don't do any thing.
315 if(is_duplicate(x_phase, x_sql) = 'Y' ) then
316 log(c_module, 'STATEMENT', 'SQL already exists, ignoring duplicate');
317 return;
318 end if;
319 end if;
320
321 -- If here, this SQL needs to be inserted.
322 l_ddl_id := get_next_ddl_id;
323
324 insert into ad_zd_ddl_handler(
325 phase ,
326 ddl_id ,
327 sql_lob ,
328 executed ,
329 status ,
330 error ,
331 edition_name ,
332 execution_time)
333 values(
334 x_phase,
335 l_ddl_id,
336 x_sql,
337 c_job_status_not_executed,
338 c_job_status_not_exec_desc,
339 null,
340 null,
341 null);
342
343 commit;
344 end LOAD;
345
346
347
348 -- Delete from AD_ZD_DDL_HANDLER
349 PROCEDURE CLEANUP(X_PHASE varchar2)
350 is
351 C_MODULE varchar2(127) := c_package||'cleanup';
352 begin
353 log(c_module, 'STATEMENT', 'Cleanup DDL handler, phase='||nvl(x_phase, 'ALL'));
354
355 delete from ad_zd_ddl_handler
356 where ( x_phase is null or phase =x_phase );
357 commit;
358
359 end CLEANUP;
360
361
362 -- Parallel Execute Worker - execute DDLs from ad_zd_ddl_handler table
363 --
364 -- X_PHASE - phase of work to select and execute
365 -- X_MAX_WORKERS - total number of parallel workers
366 -- X_CURRENT_WORKER - worker number for this thread [1..X_MAX_WORKERS]
367 -- X_STATUS - unused, pass null for now
368 --
369 -- Note: work is evenly distributed across parallel workers by rownumber
370 procedure EXECUTE(
371 X_PHASE varchar2,
372 X_MAX_WORKERS number,
373 X_CURRENT_WORKER number,
374 X_MODE varchar2)
375 is
376 C_MODULE varchar2(127) := c_package||'execute';
377 L_WORKER_HDR varchar2(127) := '['||x_phase||' '||x_max_workers||':'||x_current_worker||'] ';
378 L_JOB_HDR varchar2(250);
379 L_ROWCOUNT integer;
380 L_CUR integer;
381 L_RET number;
382 L_ERROR clob := null;
383
384 -- Store temporary sub string from SQL_LOB to find DDL keywords
385 L_SQL_SUBSTR varchar2(1024);
386 L_LONG_ERROR varchar2(1024);
387 L_ERROR_LEVEL varchar2(30);
388
389 cursor C_JOBS(P_PHASE varchar2) is
390 select mod((rownum-1), x_max_workers)+1 worker_id,
391 row_id, ddl_id, sql_lob, edition_name, executed
392 from
393 ( select rowid row_id, ddl_id, sql_lob, edition_name, executed
394 from ad_zd_ddl_handler
395 where phase=p_phase
396 order by ddl_id );
397
398 begin
399
400 log(c_module, 'PROCEDURE', l_worker_hdr||'begin');
401
402 dbms_application_info.set_module('AD_ZD', x_phase);
403
404 -- Open cursor
405 l_cur := dbms_sql.open_cursor;
406
407 -- Loop over Jobs
408 for job in c_jobs(x_phase) loop
409 -- if job needs to be executed
410 if (job.worker_id = x_current_worker and job.executed <> c_job_status_succeeded) then
411 begin
412 l_error := null;
413 l_job_hdr := l_worker_hdr||'ddl_id='||job.ddl_id||'] ';
414 l_job_hdr := '['||x_phase||' '||x_max_workers||':'||x_current_worker||' ddl_id='||job.ddl_id||'] ';
415
416 -- If job still needs to be executed, mark it as running;
417 update ad_zd_ddl_handler
418 set executed = c_job_status_running
419 , status = c_job_status_running_desc
420 , execution_time = current_timestamp
421 , error = NULL
422 where rowid = job.row_id
423 and executed not in (c_job_status_running, c_job_status_succeeded);
424
425 l_rowcount := sql%rowcount;
426 commit;
427
428 -- If job did not need to be executed, skip to next job
429 if l_rowcount = 0 then
430 log(c_module, 'STATEMENT', l_job_hdr||'Skipping already executed row');
431 continue;
432 end if;
433
434 -- Execute Job
435 sys.dbms_sql.parse(l_cur, job.sql_lob, dbms_sql.native, job.edition_name, null, false);
436 -- Note: explicit execute required for non-DDL statements
437 -- TODO: gross
438 l_sql_substr := upper(dbms_lob.substr(job.sql_lob, 10, 1));
439 if (l_sql_substr is not null and not
440 (instr(l_sql_substr, 'CREATE ') > 0 OR
441 instr(l_sql_substr, 'DROP ') > 0 OR
442 instr(l_sql_substr, 'ALTER ') > 0 OR
443 instr(l_sql_substr, 'TRUNCATE ') > 0 OR
444 instr(l_sql_substr, 'RENAME ') > 0 )) then
445 l_ret := sys.dbms_sql.execute(l_cur);
446 end if;
447
448 -- record success
449 update ad_zd_ddl_handler
450 set executed = c_job_status_succeeded
451 , status = c_job_status_success_desc
452 , execution_time = current_timestamp
453 , error = NULL
454 where rowid = job.row_id;
455 commit;
456
457 exception
458 when others then
459 l_error := sqlerrm;
460 l_long_error := dbms_lob.substr(l_error, 200, 1)
461 ||' SQL: '||dbms_lob.substr(job.sql_lob, 200, 1);
462
463 <<error_handler>>
464 case
465 -- If FATAL error, exit worker
466 when (get_error_level(sqlcode)=c_job_status_fatal_desc) then
467 updatejob_status(job.ddl_id, c_job_status_failed, c_job_status_fatal_desc, l_error);
468 error(c_module, l_job_hdr||'Fatal Error, exiting. '||l_long_error);
469
470 -- If AD_ZD packages are invalid, exit worker
471 when (sqlcode= -04061 and
472 (instr(l_error, 'AD_ZD_TABLE' ) > 0 OR
473 instr(l_error, 'AD_ZD_MVIEW' ) > 0 OR
474 instr(l_error, 'AD_ZD_PREP' ) > 0 OR
475 instr(l_error, 'AD_ZD_SEED' ) > 0 OR
476 instr(l_error, 'AD_ZD_LOG' ) > 0 )) then
477 updatejob_status(job.ddl_id, c_job_status_failed, c_job_status_fatal_desc, l_error);
478 error(c_module, l_job_hdr||'AD_ZD Packages Invalid, exiting. '||l_long_error);
479
480 -- mark Job as errored and continue
481 else
482 l_error_level := get_error_level(sqlcode, l_error);
483 if (l_error_level = c_job_status_warning_desc) then
484 -- treat warning as success, but record warning message
485 log(c_module, 'STATEMENT', l_job_hdr||'Ignored: '||l_long_error);
486 updatejob_status(job.ddl_id, c_job_status_succeeded, c_job_status_success_desc, l_error);
487 else
488 -- record error status and message
489 log(c_module, 'ERROR', l_job_hdr||l_long_error);
490 updatejob_status(job.ddl_id, c_job_status_failed, l_error_level, l_error);
491 end if;
492
493 end case error_handler;
494 commit;
495 end;
496 end if; /* Job needs to be executed */
497 end loop; /* Job Loop */
498
499 commit;
500 if dbms_sql.is_open(l_cur) then
501 dbms_sql.close_cursor(l_cur);
502 end if;
503 log(c_module, 'PROCEDURE', l_worker_hdr||'end' );
504
505 exception
506 when others then
507 if dbms_sql.is_open(l_cur) then
508 dbms_sql.close_cursor(l_cur);
509 end if;
510 raise;
511 end EXECUTE;
512
513 end AD_ZD_PARALLEL_EXEC;
[Home] [Help]