[Home] [Help]
PACKAGE BODY: APPS.FND_EXEC_MIG_CMDS
Source
1 PACKAGE BODY FND_EXEC_MIG_CMDS AS
2 /* $Header: fndpemcb.pls 120.1 2005/07/02 03:34:07 appldev noship $ */
3
4 PROCEDURE execute_cmd (p_lineno IN NUMBER,
5 p_mig_cmd IN VARCHAR2,
6 x_err_code OUT NOCOPY NUMBER,
7 x_status OUT NOCOPY VARCHAR2)
8 IS
9 l_err VARCHAR2(4000);
10 l_err_code NUMBER;
11 BEGIN
12 x_status := 'SUCCESS';
13 x_err_code := 0;
14
15 BEGIN
16 UPDATE fnd_ts_mig_cmds
17 SET start_date = sysdate,
18 last_update_date = sysdate,
19 end_date = null
20 WHERE lineno = p_lineno;
21
22 EXECUTE IMMEDIATE p_mig_cmd;
23
24 UPDATE fnd_ts_mig_cmds
25 SET migration_status = 'SUCCESS',
26 end_date = sysdate,
27 last_update_date = sysdate,
28 error_text = NULL
29 WHERE lineno = p_lineno;
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 l_err_code := sqlcode;
34 x_err_code := sqlcode;
35 l_err := sqlerrm(sqlcode);
36 x_status := 'ERROR';
37 UPDATE fnd_ts_mig_cmds
38 SET migration_status = 'ERROR',
39 end_date = sysdate,
40 error_text = l_err,
41 last_update_date = sysdate
42 WHERE lineno = p_lineno;
43 if l_err_code IN (-1658, -1659) then
44 raise_application_error(-20101, l_err);
45 end if;
46 END;
47 COMMIT;
48 END execute_cmd;
49
50
51 PROCEDURE process_line_child_cmds (p_lineno IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2)
53 IS
54 l_status VARCHAR2(30) := 'SUCCESS';
55 l_err_code NUMBER := 0;
56 l_message_data SYSTEM.tbl_mig_type;
57 l_enqopt DBMS_AQ.enqueue_options_t;
58 l_msgprop DBMS_AQ.message_properties_t;
59 l_deqopt DBMS_AQ.dequeue_options_t;
60 l_enq_msgid RAW(16);
61 l_deq_msgid RAW(16);
62 l_queue_name VARCHAR2(100);
63
64 CURSOR child_lineno_cur(l_lineno NUMBER) IS
65 SELECT lineno,
66 owner,
67 object_name,
68 migration_cmd,
69 partitioned,
70 parent_owner,
71 parent_object_name,
72 execution_mode
73 FROM fnd_ts_mig_cmds
74 WHERE parent_lineno = l_lineno;
75 BEGIN
76 l_enqopt.visibility := DBMS_AQ.IMMEDIATE;
77
78 FOR child_lineno_rec IN child_lineno_cur(p_lineno)
79 LOOP
80 if child_lineno_rec.execution_mode = 'P' then
81 l_queue_name := 'tblmig_messageque';
82 elsif child_lineno_rec.execution_mode = 'S' then
83 l_queue_name := 'tblmig_seq_messageque';
84 end if;
85
86 l_message_data := SYSTEM.tbl_mig_type
87 (child_lineno_rec.migration_cmd,
88 child_lineno_rec.lineno,
89 child_lineno_rec.owner,
90 child_lineno_rec.object_name,
91 child_lineno_rec.partitioned,
92 child_lineno_rec.parent_owner,
93 child_lineno_rec.parent_object_name);
94 DBMS_AQ.enqueue (l_queue_name, l_enqopt, l_msgprop,
95 l_message_data, l_enq_msgid);
96 END LOOP;
97 END process_line_child_cmds;
98
99
100 PROCEDURE migrate_objects (
101 p_owner IN VARCHAR2,
102 p_aqStat IN VARCHAR2,
103 p_exec_mode IN VARCHAR2,
104 x_return_status OUT NOCOPY VARCHAR2
105 ) IS
106
107 l_status VARCHAR2(30) := 'SUCCESS';
108 l_err_code NUMBER := 0;
109 l_retVal NUMBER;
110 l_lineno NUMBER;
111 l_aqretVal NUMBER;
112 l_mig_cmd VARCHAR2(4000);
113 l_err VARCHAR2(4000);
114 l_message_data SYSTEM.tbl_mig_type;
115 l_outmessage_data SYSTEM.tbl_mig_type;
116 l_enqopt DBMS_AQ.enqueue_options_t;
117 l_msgprop DBMS_AQ.message_properties_t;
118 l_deqopt DBMS_AQ.dequeue_options_t;
119 l_enq_msgid RAW(16);
120 l_deq_msgid RAW(16);
121
122 l_parent_owner FND_TS_MIG_CMDS.PARENT_OWNER%TYPE;
123 l_parent_object_name FND_TS_MIG_CMDS.PARENT_OBJECT_NAME%TYPE;
124 l_owner FND_TS_MIG_CMDS.OWNER%TYPE;
125 l_object_name FND_TS_MIG_CMDS.OBJECT_NAME%TYPE;
126 l_mig_status FND_TS_MIG_CMDS.MIGRATION_STATUS%TYPE := 'SUCCESS';
127 l_partitioned FND_TS_MIG_CMDS.PARTITIONED%TYPE;
128 l_queue_name VARCHAR2(100);
129 l_cnt NUMBER;
130 l_string VARCHAR2(4000);
131 TYPE mig_cmd_cur_type IS REF CURSOR;
132 mig_cmd_cur mig_cmd_cur_type;
133
134 TYPE NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
135 TYPE CharTabType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
136 TYPE BigCharTabType IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
137
138 TYPE RecTabType IS RECORD
139 (lineno NumTabType,
140 owner CharTabType,
141 object_name CharTabType,
142 migration_cmd BigCharTabType,
143 partitioned CharTabType,
144 parent_owner CharTabType,
145 parent_object_name CharTabType);
146 cmdtab RecTabType;
147
148 cursor all_cmd_cur(l_exec_mode VARCHAR2) is
149 select lineno,
150 owner,
151 object_name,
152 migration_cmd,
153 partitioned,
154 parent_owner,
155 parent_object_name
156 from fnd_ts_mig_cmds
157 --where object_type IN ('TABLE', 'INDEX', 'MVIEW', 'MV_LOG', 'LONG_INDEX','LONG_MVLOG')
158 where object_type IN ('TABLE', 'INDEX', 'MVIEW', 'MV_LOG')
159 and migration_status in ('ERROR', 'GENERATED')
160 and object_name not like 'FND_TS_MIG_CMDS%'
161 and object_name not like 'FND_TS_SIZING%'
162 and object_name not like 'FND_TS_PROD_INSTS%'
163 AND execution_mode = l_exec_mode
164 AND ( (migration_status = 'ERROR' -- for restart
165 OR
166 (migration_status = 'GENERATED'
167 AND start_date IS NOT NULL)) -- for restart of Abort cases
168 OR
169 (parent_lineno IS NULL) -- for first time enqueueing
170 OR
171 (migration_status = 'GENERATED' and parent_lineno in (select lineno from fnd_ts_mig_cmds where migration_status='SUCCESS')) -- for bug 4332349
172 )
173 order by total_blocks desc;
174
175 l_schema_list VARCHAR2(4000);
176 l_old_index NUMBER := 0;
177 l_new_index NUMBER := 0;
178 l_enqueue VARCHAR2(1) := NVL(p_aqStat, 'N');
179
180 BEGIN
181 update fnd_ts_mig_status set num_threads=num_threads+1 where item='EXE_MIG_CMDS';
182 commit;
183
184 if p_owner <> '%' then
185 LOOP
186 l_new_index := INSTR(p_owner, ',', l_old_index+1);
187 if l_schema_list IS NULL AND l_new_index = 0 then
188 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1)||''')';
189 elsif l_schema_list IS NULL AND l_new_index <> 0 then
190 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
191 elsif l_new_index = 0 then
192 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1)||''')';
193 else
194 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
195 end if;
196 EXIT WHEN l_new_index = 0;
197 l_old_index := l_new_index;
198 END LOOP;
199 end if;
200 --dbms_output.put_line('schema list = '||l_schema_list);
201
202 l_string := 'select lineno,
203 owner,
204 object_name,
205 migration_cmd,
206 partitioned,
207 parent_owner,
208 parent_object_name
209 from fnd_ts_mig_cmds
210 where owner IN '||l_schema_list||'
211 and object_type IN (''TABLE'', ''INDEX'', ''MVIEW'', ''MV_LOG'', ''LONG_INDEX'',''LONG_MVLOG'')
212 and migration_status in (''ERROR'', ''GENERATED'')
213 and object_name not like ''FND_TS_MIG_CMDS%''
214 and object_name not like ''FND_TS_SIZING%''
215 and object_name not like ''FND_TS_PROD_INSTS%''
216 AND execution_mode = '''||p_exec_mode||'''
217 AND ( (migration_status = ''ERROR''
218 OR
219 (migration_status = ''GENERATED''
220 AND start_date IS NOT NULL))
221 OR
222 (parent_lineno IS NULL))
223 order by total_blocks desc';
224 --dbms_output.put_line(substr(l_string,1,250));
225 --dbms_output.put_line(substr(l_string,251,250));
226 --dbms_output.put_line(substr(l_string,501,250));
227 --dbms_output.put_line(substr(l_string,751,250));
228
229 if p_exec_mode = 'P' then
230 -- Set the module name for parallel process
231 DBMS_APPLICATION_INFO.SET_MODULE('TS_MIGRATE_PARALLEL_OBJECTS', NULL);
232 l_queue_name := 'tblmig_messageque';
233 elsif p_exec_mode = 'S' then
234 /*
235 -- Initial Enqueue only if no Sequential process is running.
236 SELECT COUNT(1)
237 INTO l_cnt
238 FROM v$session
239 WHERE module = 'TS_MIGRATE_SEQUENTIAL_OBJECTS'
240 AND status <> 'KILLED';
241 if l_cnt > 0 then
242 l_enqueue := 'N';
243 end if;
244 */
245 -- Set the module name for sequential process
246 DBMS_APPLICATION_INFO.SET_MODULE('TS_MIGRATE_SEQUENTIAL_OBJECTS', NULL);
247 l_queue_name := 'tblmig_seq_messageque';
248 end if;
249
250 if l_enqueue = 'Y' then
251 l_enqopt.visibility := DBMS_AQ.IMMEDIATE;
252 if p_owner = '%' then
253 OPEN all_cmd_cur(p_exec_mode);
254 LOOP
255 cmdtab.lineno.DELETE;
256 FETCH all_cmd_cur BULK COLLECT INTO
257 cmdtab.lineno, cmdtab.owner, cmdtab.object_name,
258 cmdtab.migration_cmd, cmdtab.partitioned,
259 cmdtab.parent_owner, cmdtab.parent_object_name LIMIT 1000;
260 EXIT WHEN cmdtab.lineno.COUNT = 0;
261 FOR i IN cmdtab.lineno.FIRST..cmdtab.lineno.LAST
262 LOOP
263 l_message_data := SYSTEM.tbl_mig_type
264 (cmdtab.migration_cmd(i),
265 cmdtab.lineno(i),
266 cmdtab.owner(i),
267 cmdtab.object_name(i),
268 cmdtab.partitioned(i),
269 cmdtab.parent_owner(i),
270 cmdtab.parent_object_name(i));
271 DBMS_AQ.enqueue (l_queue_name, l_enqopt, l_msgprop,
272 l_message_data, l_enq_msgid);
273 --DBMS_OUTPUT.PUT_LINE('Parent Message Enqueued, lineno = '||to_char(cmdtab.lineno(i)));
274 END LOOP;
275 END LOOP;
276 CLOSE all_cmd_cur;
277 else -- for a list of schemas
278 OPEN mig_cmd_cur FOR l_string;
279 LOOP
280 cmdtab.lineno.DELETE;
281 FETCH mig_cmd_cur BULK COLLECT INTO
282 cmdtab.lineno, cmdtab.owner, cmdtab.object_name,
283 cmdtab.migration_cmd, cmdtab.partitioned,
284 cmdtab.parent_owner, cmdtab.parent_object_name LIMIT 1000;
285 EXIT WHEN cmdtab.lineno.COUNT = 0;
286 FOR i IN cmdtab.lineno.FIRST..cmdtab.lineno.LAST
287 LOOP
288 l_message_data := SYSTEM.tbl_mig_type
289 (cmdtab.migration_cmd(i),
290 cmdtab.lineno(i),
291 cmdtab.owner(i),
292 cmdtab.object_name(i),
293 cmdtab.partitioned(i),
294 cmdtab.parent_owner(i),
295 cmdtab.parent_object_name(i));
296 DBMS_AQ.enqueue (l_queue_name, l_enqopt, l_msgprop,
297 l_message_data, l_enq_msgid);
298 --DBMS_OUTPUT.PUT_LINE('Parent Message Enqueued, lineno = '||to_char(cmdtab.lineno(i)));
299 END LOOP;
300 END LOOP;
301 CLOSE mig_cmd_cur;
302 end if;
303 end if; -- for p_aqStat = 'Y'
307 l_deqopt.visibility := DBMS_AQ.IMMEDIATE;
304
305 BEGIN
306 l_deqopt.navigation := DBMS_AQ.FIRST_MESSAGE;
308 l_deqopt.wait := 1;
309 END;
310
311 l_retVal := 0;
312 while ( l_retVal = 0 )
313 LOOP
314 BEGIN
315 DBMS_AQ.dequeue (
316 queue_name => l_queue_name,
317 dequeue_options => l_deqopt,
318 message_properties => l_msgprop,
319 payload => l_outmessage_data,
320 msgid => l_deq_msgid
321 );
322 l_lineno := l_outmessage_data.lineno;
323 l_owner := l_outmessage_data.owner;
324 l_object_name := l_outmessage_data.object_name;
325 l_parent_owner := l_outmessage_data.parent_owner;
326 l_parent_object_name := l_outmessage_data.parent_object_name;
327 l_partitioned := l_outmessage_data.partitioned;
328 l_mig_cmd := l_outmessage_data.query;
329
330 execute_cmd (l_lineno,
331 l_mig_cmd,
332 l_err_code,
333 l_status);
334 if l_err_code = -54 then
335 -- Re-try
336 execute_cmd (l_lineno,
337 l_mig_cmd,
338 l_err_code,
339 l_status);
340 end if;
341
342 if l_status = 'SUCCESS' then
343 -- Enqueue the childs i.e. all the objects with
344 -- parent_lineno = lineno of the moved object
345 process_line_child_cmds(l_lineno,
346 l_status);
347 end if;
348 if l_status = 'ERROR' then
349 x_return_status := 'ERROR';
350 end if;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 if p_exec_mode = 'P' then
355 SELECT COUNT(1)
356 INTO l_cnt
357 FROM v$session
358 WHERE module = 'TS_MIGRATE_SEQUENTIAL_OBJECTS'
359 AND status <> 'KILLED';
360 if l_cnt = 0 then
361 if sqlcode = -25228 then
362 update fnd_ts_mig_status set num_threads=num_threads-1, status=decode(num_threads,1,'SUCCESS',status),detail_message=decode(num_threads,1,'',detail_message) where item='EXE_MIG_CMDS';
363 commit;
364 end if;
365 l_retVal := 1;
366 EXIT;
367 end if;
368 DBMS_LOCK.SLEEP(300);
369 else
370 if sqlcode = -25228 then
371 update fnd_ts_mig_status set num_threads=num_threads-1, status=decode(num_threads,1,'SUCCESS',status),detail_message=decode(num_threads,1,'',detail_message) where item='EXE_MIG_CMDS';
372 commit;
373 end if;
374 l_retVal := 1;
375 EXIT;
376 end if;
377 END;
378 END LOOP; -- end of while loop
379
380 END migrate_objects;
381
382
383 PROCEDURE disable_cons (
384 p_owner IN VARCHAR2,
385 x_return_status OUT NOCOPY VARCHAR2)
386 IS
387 cursor disable_cur is
388 select lineno,
389 migration_cmd
390 from fnd_ts_mig_cmds
391 where owner = p_owner
392 and object_type = 'DISABLE_CONSTRAINT'
393 order by lineno asc;
394 query VARCHAR2(4000);
395 l_err VARCHAR2(4000);
396 BEGIN
397 x_return_status := 'SUCCESS';
398
399 FOR disable_rec IN disable_cur
400 LOOP
401 query := disable_rec.migration_cmd;
402 BEGIN
403 UPDATE fnd_ts_mig_cmds
404 SET start_date = sysdate,
405 end_date = null
406 WHERE lineno = disable_rec.lineno;
407
408 EXECUTE IMMEDIATE query;
409
410 UPDATE fnd_ts_mig_cmds
411 SET migration_status = 'SUCCESS',
412 end_date = sysdate,
413 last_update_date = sysdate,
414 error_text = NULL
415 WHERE lineno = disable_rec.lineno;
416 EXCEPTION
417 WHEN OTHERS THEN
418 l_err := sqlerrm(sqlcode);
419 x_return_status := 'ERROR';
420 UPDATE fnd_ts_mig_cmds
421 SET migration_status = 'ERROR',
422 end_date = sysdate,
423 error_text = l_err,
424 last_update_date = sysdate
425 WHERE lineno = disable_rec.lineno;
426 END;
427 END LOOP;
428 END disable_cons;
429
430 PROCEDURE disable_trigger (
431 p_owner IN VARCHAR2,
432 x_return_status OUT NOCOPY VARCHAR2)
433 IS
434 cursor disable_cur is
435 select lineno,
436 migration_cmd
437 from fnd_ts_mig_cmds
438 where owner = p_owner
439 and object_type = 'DISABLE_TRIGGER'
440 order by lineno asc;
441 query VARCHAR2(4000);
442 l_err VARCHAR2(4000);
443 BEGIN
444 x_return_status := 'SUCCESS';
445
446 FOR disable_rec IN disable_cur
447 LOOP
448 query := disable_rec.migration_cmd;
449 BEGIN
450 UPDATE fnd_ts_mig_cmds
451 SET start_date = sysdate,
452 end_date = null
453 WHERE lineno = disable_rec.lineno;
454
455 EXECUTE IMMEDIATE query;
456
457 UPDATE fnd_ts_mig_cmds
458 SET migration_status = 'SUCCESS',
462 WHERE lineno = disable_rec.lineno;
459 end_date = sysdate,
460 last_update_date = sysdate,
461 error_text = NULL
463 EXCEPTION
464 WHEN OTHERS THEN
465 l_err := sqlerrm(sqlcode);
466 x_return_status := 'ERROR';
467 UPDATE fnd_ts_mig_cmds
468 SET migration_status = 'ERROR',
469 end_date = sysdate,
470 error_text = l_err,
471 last_update_date = sysdate
472 WHERE lineno = disable_rec.lineno;
473 END;
474 END LOOP;
475 END disable_trigger;
476
477 PROCEDURE stop_queues (
478 p_owner IN VARCHAR2,
479 x_return_status OUT NOCOPY VARCHAR2)
480 IS
481 cursor disable_cur is
482 select lineno,
483 migration_cmd
484 from fnd_ts_mig_cmds
485 where owner = p_owner
486 and object_type = 'STOP_QUEUE'
487 order by lineno asc;
488 query VARCHAR2(4000);
489 l_err VARCHAR2(4000);
490 BEGIN
491 x_return_status := 'SUCCESS';
492
493 FOR disable_rec IN disable_cur
494 LOOP
495 query := disable_rec.migration_cmd;
496 BEGIN
497 UPDATE fnd_ts_mig_cmds
498 SET start_date = sysdate,
499 end_date = null
500 WHERE lineno = disable_rec.lineno;
501
502 EXECUTE IMMEDIATE query;
503
504 UPDATE fnd_ts_mig_cmds
505 SET migration_status = 'SUCCESS',
506 end_date = sysdate,
507 last_update_date = sysdate,
508 error_text = NULL
509 WHERE lineno = disable_rec.lineno;
510 EXCEPTION
511 WHEN OTHERS THEN
512 l_err := sqlerrm(sqlcode);
513 x_return_status := 'ERROR';
514 UPDATE fnd_ts_mig_cmds
515 SET migration_status = 'ERROR',
516 end_date = sysdate,
517 error_text = l_err,
518 last_update_date = sysdate
519 WHERE lineno = disable_rec.lineno;
520 END;
521 END LOOP;
522 END stop_queues;
523
524 PROCEDURE disable_policies (
525 p_owner IN VARCHAR2,
526 x_return_status OUT NOCOPY VARCHAR2)
527 IS
528 cursor disable_cur is
529 select lineno,
530 migration_cmd
531 from fnd_ts_mig_cmds
532 where owner = p_owner
533 and object_type = 'DISABLE_POLICY'
534 order by lineno asc;
535 query VARCHAR2(4000);
536 l_err VARCHAR2(4000);
537 BEGIN
538 x_return_status := 'SUCCESS';
539
540 FOR disable_rec IN disable_cur
541 LOOP
542 query := disable_rec.migration_cmd;
543 BEGIN
544 UPDATE fnd_ts_mig_cmds
545 SET start_date = sysdate,
546 end_date = null
547 WHERE lineno = disable_rec.lineno;
548
549 EXECUTE IMMEDIATE query;
550
551 UPDATE fnd_ts_mig_cmds
552 SET migration_status = 'SUCCESS',
553 end_date = sysdate,
554 last_update_date = sysdate,
555 error_text = NULL
556 WHERE lineno = disable_rec.lineno;
557 EXCEPTION
558 WHEN OTHERS THEN
559 l_err := sqlerrm(sqlcode);
560 x_return_status := 'ERROR';
561 UPDATE fnd_ts_mig_cmds
562 SET migration_status = 'ERROR',
563 end_date = sysdate,
564 error_text = l_err,
565 last_update_date = sysdate
566 WHERE lineno = disable_rec.lineno;
567 END;
568 END LOOP;
569 END disable_policies;
570
571 PROCEDURE disable (
572 p_owner IN VARCHAR2,
573 x_return_status OUT NOCOPY VARCHAR2)
574 IS
575 cursor disable_all_cur is
576 select lineno,
577 migration_cmd
578 from fnd_ts_mig_cmds
579 where object_type IN ('DISABLE_TRIGGER', 'DISABLE_CONSTRAINT', 'STOP_QUEUE', 'DISABLE_POLICY');
580
581 TYPE disable_cur_type IS REF CURSOR;
582 disable_cur disable_cur_type;
583 l_string VARCHAR2(4000);
584 l_list VARCHAR2(4000);
585 l_err VARCHAR2(4000);
586 l_lineno FND_TS_MIG_CMDS.LINENO%TYPE;
587 l_migration_cmd FND_TS_MIG_CMDS.MIGRATION_CMD%TYPE;
588 query VARCHAR2(4000);
589
590 l_schema_list VARCHAR2(4000);
591 l_old_index NUMBER := 0;
592 l_new_index NUMBER := 0;
593
594 BEGIN
595 x_return_status := 'SUCCESS';
596
597 if p_owner <> '%' then
598 LOOP
599 l_new_index := INSTR(p_owner, ',', l_old_index+1);
600 if l_schema_list IS NULL AND l_new_index = 0 then
601 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1)||''')';
602 elsif l_schema_list IS NULL AND l_new_index <> 0 then
603 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
604 elsif l_new_index = 0 then
605 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1)||''')';
606 else
607 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
608 end if;
609 EXIT WHEN l_new_index = 0;
610 l_old_index := l_new_index;
611 END LOOP;
612 end if;
613 --dbms_output.put_line('schema list = '||l_schema_list);
614
618 query := disable_rec.migration_cmd;
615 if p_owner = '%' then
616 FOR disable_rec IN disable_all_cur
617 LOOP
619 BEGIN
620 UPDATE fnd_ts_mig_cmds
621 SET start_date = sysdate,
622 end_date = null
623 WHERE lineno = disable_rec.lineno;
624
625 EXECUTE IMMEDIATE query;
626
627 UPDATE fnd_ts_mig_cmds
628 SET migration_status = 'SUCCESS',
629 end_date = sysdate,
630 last_update_date = sysdate,
631 error_text = NULL
632 WHERE lineno = disable_rec.lineno;
633 EXCEPTION
634 WHEN OTHERS THEN
635 l_err := sqlerrm(sqlcode);
636 x_return_status := 'ERROR';
637 UPDATE fnd_ts_mig_cmds
638 SET migration_status = 'ERROR',
639 end_date = sysdate,
640 error_text = l_err,
641 last_update_date = sysdate
642 WHERE lineno = disable_rec.lineno;
643 END;
644 END LOOP;
645 else -- for a list of schemas
646 l_string := 'select lineno, migration_cmd from fnd_ts_mig_cmds
647 where owner IN '||l_schema_list||'
648 and object_type IN (''DISABLE_TRIGGER'', ''DISABLE_CONSTRAINT'', ''STOP_QUEUE'', ''DISABLE_POLICY'')';
649 --dbms_output.put_line(l_string);
650 OPEN disable_cur FOR l_string;
651 LOOP
652 FETCH disable_cur INTO l_lineno, l_migration_cmd;
653 EXIT WHEN disable_cur%NOTFOUND;
654 query := l_migration_cmd;
655 BEGIN
656 UPDATE fnd_ts_mig_cmds
657 SET start_date = sysdate,
658 end_date = null
659 WHERE lineno = l_lineno;
660
661 EXECUTE IMMEDIATE query;
662
663 UPDATE fnd_ts_mig_cmds
664 SET migration_status = 'SUCCESS',
665 end_date = sysdate,
666 last_update_date = sysdate,
667 error_text = NULL
668 WHERE lineno = l_lineno;
669 EXCEPTION
670 WHEN OTHERS THEN
671 l_err := sqlerrm(sqlcode);
672 x_return_status := 'ERROR';
673 UPDATE fnd_ts_mig_cmds
674 SET migration_status = 'ERROR',
675 end_date = sysdate,
676 error_text = l_err,
677 last_update_date = sysdate
678 WHERE lineno = l_lineno;
679 END;
680 END LOOP;
681 CLOSE disable_cur;
682 end if;
683 END disable;
684
685 PROCEDURE enable_cons (
686 p_owner IN VARCHAR2,
687 x_return_status OUT NOCOPY VARCHAR2)
688 IS
689 cursor enable_cur is
690 select lineno,
691 migration_cmd
692 from fnd_ts_mig_cmds
693 where owner = p_owner
694 and object_type = 'ENABLE_CONSTRAINT'
695 order by lineno asc;
696 query VARCHAR2(4000);
697 l_err VARCHAR2(4000);
698 BEGIN
699 x_return_status := 'SUCCESS';
700
701 FOR enable_rec IN enable_cur
702 LOOP
703 query := enable_rec.migration_cmd;
704 BEGIN
705 UPDATE fnd_ts_mig_cmds
706 SET start_date = sysdate,
707 end_date = null
708 WHERE lineno = enable_rec.lineno;
709
710 EXECUTE IMMEDIATE query;
711
712 UPDATE fnd_ts_mig_cmds
713 SET migration_status = 'SUCCESS',
714 end_date = sysdate,
715 last_update_date = sysdate,
716 error_text = NULL
717 WHERE lineno = enable_rec.lineno;
718 EXCEPTION
719 WHEN OTHERS THEN
720 l_err := sqlerrm(sqlcode);
721 x_return_status := 'ERROR';
722 UPDATE fnd_ts_mig_cmds
723 SET migration_status = 'ERROR',
724 end_date = sysdate,
725 error_text = l_err,
726 last_update_date = sysdate
727 WHERE lineno = enable_rec.lineno;
728 END;
729 END LOOP;
730 END enable_cons;
731
732 PROCEDURE enable_trigger (
733 p_owner IN VARCHAR2,
734 x_return_status OUT NOCOPY VARCHAR2)
735 IS
736 cursor enable_cur is
737 select lineno,
738 migration_cmd
739 from fnd_ts_mig_cmds
740 where owner = p_owner
741 and object_type = 'ENABLE_TRIGGER'
742 order by lineno asc;
743 query VARCHAR2(4000);
744 l_err VARCHAR2(4000);
745 BEGIN
746 x_return_status := 'SUCCESS';
747
748 FOR enable_rec IN enable_cur
749 LOOP
750 query := enable_rec.migration_cmd;
751 BEGIN
752 UPDATE fnd_ts_mig_cmds
753 SET start_date = sysdate,
754 end_date = null
755 WHERE lineno = enable_rec.lineno;
756
757 EXECUTE IMMEDIATE query;
758
759 UPDATE fnd_ts_mig_cmds
760 SET migration_status = 'SUCCESS',
761 end_date = sysdate,
762 last_update_date = sysdate,
763 error_text = NULL
764 WHERE lineno = enable_rec.lineno;
765 EXCEPTION
766 WHEN OTHERS THEN
767 l_err := sqlerrm(sqlcode);
768 x_return_status := 'ERROR';
769 UPDATE fnd_ts_mig_cmds
770 SET migration_status = 'ERROR',
771 end_date = sysdate,
772 error_text = l_err,
773 last_update_date = sysdate
774 WHERE lineno = enable_rec.lineno;
778
775 END;
776 END LOOP;
777 END enable_trigger;
779 PROCEDURE start_queues (
780 p_owner IN VARCHAR2,
781 x_return_status OUT NOCOPY VARCHAR2)
782 IS
783 cursor enable_cur is
784 select lineno,
785 migration_cmd
786 from fnd_ts_mig_cmds
787 where owner = p_owner
788 and object_type = 'START_QUEUE'
789 order by lineno asc;
790 query VARCHAR2(4000);
791 l_err VARCHAR2(4000);
792 BEGIN
793 x_return_status := 'SUCCESS';
794
795 FOR enable_rec IN enable_cur
796 LOOP
797 query := enable_rec.migration_cmd;
798 BEGIN
799 UPDATE fnd_ts_mig_cmds
800 SET start_date = sysdate,
801 end_date = null
802 WHERE lineno = enable_rec.lineno;
803
804 EXECUTE IMMEDIATE query;
805
806 UPDATE fnd_ts_mig_cmds
807 SET migration_status = 'SUCCESS',
808 end_date = sysdate,
809 last_update_date = sysdate,
810 error_text = NULL
811 WHERE lineno = enable_rec.lineno;
812 EXCEPTION
813 WHEN OTHERS THEN
814 l_err := sqlerrm(sqlcode);
815 x_return_status := 'ERROR';
816 UPDATE fnd_ts_mig_cmds
817 SET migration_status = 'ERROR',
818 end_date = sysdate,
819 error_text = l_err,
820 last_update_date = sysdate
821 WHERE lineno = enable_rec.lineno;
822 END;
823 END LOOP;
824 END start_queues;
825
826 PROCEDURE enable_policies (
827 p_owner IN VARCHAR2,
828 x_return_status OUT NOCOPY VARCHAR2)
829 IS
830 cursor enable_cur is
831 select lineno,
832 migration_cmd
833 from fnd_ts_mig_cmds
834 where owner = p_owner
835 and object_type = 'ENABLE_POLICY'
836 order by lineno asc;
837 query VARCHAR2(4000);
838 l_err VARCHAR2(4000);
839 BEGIN
840 x_return_status := 'SUCCESS';
841
842 FOR enable_rec IN enable_cur
843 LOOP
844 query := enable_rec.migration_cmd;
845 BEGIN
846 UPDATE fnd_ts_mig_cmds
847 SET start_date = sysdate,
848 end_date = null
849 WHERE lineno = enable_rec.lineno;
850
851 EXECUTE IMMEDIATE query;
852
853 UPDATE fnd_ts_mig_cmds
854 SET migration_status = 'SUCCESS',
855 end_date = sysdate,
856 last_update_date = sysdate,
857 error_text = NULL
858 WHERE lineno = enable_rec.lineno;
859 EXCEPTION
860 WHEN OTHERS THEN
861 l_err := sqlerrm(sqlcode);
862 x_return_status := 'ERROR';
863 UPDATE fnd_ts_mig_cmds
864 SET migration_status = 'ERROR',
865 end_date = sysdate,
866 error_text = l_err,
867 last_update_date = sysdate
868 WHERE lineno = enable_rec.lineno;
869 END;
870 END LOOP;
871 END enable_policies;
872
873 PROCEDURE enable (
874 p_owner IN VARCHAR2,
875 x_return_status OUT NOCOPY VARCHAR2)
876 IS
877 cursor enable_all_cur is
878 select lineno,
879 migration_cmd
880 from fnd_ts_mig_cmds
881 where object_type IN ('ENABLE_TRIGGER', 'ENABLE_CONSTRAINT', 'START_QUEUE', 'ENABLE_POLICY');
882
883 cursor proc_csr IS
884 select 1
885 from v$session
886 /* where module in ('TS_MIGRATE_SEQUENTIAL_OBJECTS', 'TS_MIGRATE_PARALLEL_OBJECTS') */
887 where module in
888 ('TS_MIGRATE_SEQUENTIAL_OBJECTS',
889 'TS_MIGRATE_PARALLEL_OBJECTS',
890 'TS_SET_DEFAULTS',
891 'TS_DISABLE_CMDS',
892 'TS_GENERATE_STATEMENTS',
893 'TS_POSTMIGRATION_STEPS')
894 and status <> 'KILLED';
895
896 l_dummy INTEGER;
897
898 cursor postmig_csr is
899 select lineno,
900 migration_cmd
901 from fnd_ts_mig_cmds
902 where object_type = 'POSTMIG';
903
904 TYPE enable_cur_type IS REF CURSOR;
905 enable_cur enable_cur_type;
906 l_string VARCHAR2(4000);
907 l_list VARCHAR2(4000);
908 l_err VARCHAR2(4000);
909 l_lineno FND_TS_MIG_CMDS.LINENO%TYPE;
910 l_migration_cmd FND_TS_MIG_CMDS.MIGRATION_CMD%TYPE;
911 query VARCHAR2(4000);
912 l_schema_list VARCHAR2(4000);
913 l_old_index NUMBER := 0;
914 l_new_index NUMBER := 0;
915
916 BEGIN
917 DBMS_APPLICATION_INFO.SET_MODULE('TS_POSTMIGRATION_STEPS', NULL);
918
919 x_return_status := 'SUCCESS';
920
921 if p_owner <> '%' then
922 LOOP
923 l_new_index := INSTR(p_owner, ',', l_old_index+1);
924 if l_schema_list IS NULL AND l_new_index = 0 then
925 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1)||''')';
926 elsif l_schema_list IS NULL AND l_new_index <> 0 then
927 l_schema_list := '('''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
928 elsif l_new_index = 0 then
929 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1)||''')';
930 else
931 l_schema_list := l_schema_list||','''||SUBSTR(p_owner, l_old_index+1, l_new_index-l_old_index-1)||'''';
932 end if;
936 end if;
933 EXIT WHEN l_new_index = 0;
934 l_old_index := l_new_index;
935 END LOOP;
937 --dbms_output.put_line('schema list = '||l_schema_list);
938
939 if p_owner = '%' then
940 FOR enable_rec IN enable_all_cur
941 LOOP
942 query := enable_rec.migration_cmd;
943 BEGIN
944 UPDATE fnd_ts_mig_cmds
945 SET start_date = sysdate,
946 end_date = null
947 WHERE lineno = enable_rec.lineno;
948
949 EXECUTE IMMEDIATE query;
950
951 UPDATE fnd_ts_mig_cmds
952 SET migration_status = 'SUCCESS',
953 end_date = sysdate,
954 last_update_date = sysdate,
955 error_text = NULL
956 WHERE lineno = enable_rec.lineno;
957 EXCEPTION
958 WHEN OTHERS THEN
959 l_err := sqlerrm(sqlcode);
960 x_return_status := 'ERROR';
961 UPDATE fnd_ts_mig_cmds
962 SET migration_status = 'ERROR',
963 end_date = sysdate,
964 error_text = l_err,
965 last_update_date = sysdate
966 WHERE lineno = enable_rec.lineno;
967 END;
968 END LOOP;
969 else -- for a list of schemas
970 l_string := 'select lineno, migration_cmd from fnd_ts_mig_cmds
971 where owner IN '||l_schema_list||'
972 and object_type IN (''ENABLE_TRIGGER'', ''ENABLE_CONSTRAINT'', ''START_QUEUE'', ''ENABLE_POLICY'', ''POSTMIG'')';
973 OPEN enable_cur FOR l_string;
974 LOOP
975 FETCH enable_cur INTO l_lineno, l_migration_cmd;
976 EXIT WHEN enable_cur%NOTFOUND;
977 query := l_migration_cmd;
978 BEGIN
979 UPDATE fnd_ts_mig_cmds
980 SET start_date = sysdate,
981 end_date = null
982 WHERE lineno = l_lineno;
983
984 EXECUTE IMMEDIATE query;
985
986 UPDATE fnd_ts_mig_cmds
987 SET migration_status = 'SUCCESS',
988 end_date = sysdate,
989 last_update_date = sysdate,
990 error_text = NULL
991 WHERE lineno = l_lineno;
992 EXCEPTION
993 WHEN OTHERS THEN
994 l_err := sqlerrm(sqlcode);
995 x_return_status := 'ERROR';
996 UPDATE fnd_ts_mig_cmds
997 SET migration_status = 'ERROR',
998 end_date = sysdate,
999 error_text = l_err,
1000 last_update_date = sysdate
1001 WHERE lineno = l_lineno;
1002 END;
1003 END LOOP;
1004 CLOSE enable_cur;
1005 end if;
1006
1007 OPEN proc_csr;
1008 FETCH proc_csr INTO l_dummy;
1009 if proc_csr%NOTFOUND then
1010 FOR postmig_rec IN postmig_csr
1011 LOOP
1012 BEGIN
1013 UPDATE fnd_ts_mig_cmds
1014 SET start_date = sysdate,
1015 end_date = null
1016 WHERE lineno = postmig_rec.lineno;
1017
1018 EXECUTE IMMEDIATE postmig_rec.migration_cmd;
1019
1020 UPDATE fnd_ts_mig_cmds
1021 SET migration_status = 'SUCCESS',
1022 end_date = sysdate,
1023 last_update_date = sysdate,
1024 error_text = NULL
1025 WHERE lineno = postmig_rec.lineno;
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 l_err := sqlerrm(sqlcode);
1029 x_return_status := 'ERROR';
1030 UPDATE fnd_ts_mig_cmds
1031 SET migration_status = 'ERROR',
1032 end_date = sysdate,
1033 error_text = l_err,
1034 last_update_date = sysdate
1035 WHERE lineno = postmig_rec.lineno;
1036 END;
1037 END LOOP;
1038 end if;
1039 CLOSE proc_csr;
1040 END enable;
1041
1042 PROCEDURE migtsobj IS
1043 CURSOR c1 IS
1044 select *
1045 from fnd_ts_mig_cmds
1046 where migration_status <> 'SUCCESS'
1047 and (object_name like 'FND_TS_MIG_CMDS%'
1048 or object_name like 'FND_TS_PROD_INST%'
1049 or object_name like 'FND_TS_SIZING%')
1050 order by lineno;
1051 TYPE cmd_tab_type IS TABLE OF FND_TS_MIG_CMDS%ROWTYPE;
1055 OPEN c1;
1052 cmd_tab cmd_tab_type;
1053 i INTEGER := 0;
1054 BEGIN
1056 LOOP
1057 FETCH c1 INTO cmd_tab(i);
1058 EXIT WHEN c1%NOTFOUND;
1059 i := i + 1;
1060 END LOOP;
1061 CLOSE c1;
1062
1063 FOR j IN cmd_tab.FIRST..cmd_tab.LAST
1064 LOOP
1065 BEGIN
1066 cmd_tab(j).start_date := sysdate;
1067 EXECUTE IMMEDIATE cmd_tab(j).migration_cmd;
1068 cmd_tab(j).migration_status := 'SUCCESS';
1069 cmd_tab(j).end_date := sysdate;
1070 cmd_tab(j).last_update_date := sysdate;
1071 cmd_tab(j).error_text := null;
1072 EXCEPTION WHEN OTHERS THEN
1073 cmd_tab(j).migration_status := 'ERROR';
1074 cmd_tab(j).end_date := sysdate;
1075 cmd_tab(j).last_update_date := sysdate;
1076 cmd_tab(j).error_text := sqlerrm(sqlcode);
1077 END;
1078 END LOOP;
1079
1080 FOR j IN cmd_tab.FIRST..cmd_tab.LAST
1081 LOOP
1082 UPDATE fnd_ts_mig_cmds
1083 SET migration_status = cmd_tab(j).migration_status,
1084 start_date = cmd_tab(j).start_date,
1085 end_date = cmd_tab(j).end_date,
1086 last_update_date = cmd_tab(j).last_update_date,
1087 error_text = cmd_tab(j).error_text
1088 WHERE lineno = cmd_tab(i).lineno;
1089 END LOOP;
1090 END migtsobj;
1091
1092 END FND_EXEC_MIG_CMDS;