[Home] [Help]
PACKAGE BODY: APPS.HR_LONG2LOB
Source
1 PACKAGE BODY HR_LONG2LOB AS
2 /*$Header: hrl2lmig.pkb 120.4.12020000.4 2012/11/28 10:49:30 srannama ship $ */
3
4 FUNCTION getTblOwner(p_appl_short_name VARCHAR2) RETURN VARCHAR2 IS
5 l_status VARCHAR2(100) := NULL;
6 l_industry VARCHAR2(100) := NULL;
7 l_result BOOLEAN;
8 l_schema_owner VARCHAR2(10) := NULL;
9
10 BEGIN
11 l_result := FND_INSTALLATION.GET_APP_INFO(
12 p_appl_short_name,
13 l_status,
14 l_industry,
15 l_schema_owner);
16 RETURN l_schema_owner;
17 END getTblOwner;
18
19 FUNCTION columnExists(
20 p_appl_short_name VARCHAR2,
21 p_table_name VARCHAR2,
22 p_col_name VARCHAR2,
23 p_col_data_type VARCHAR2) RETURN BOOLEAN AS
24
25 dummy VARCHAR2(1);
26 retValue BOOLEAN:=FALSE;
27 l_schema_owner VARCHAR2(10):= NULL;
28
29 BEGIN
30
31 l_schema_owner := getTblOwner(p_appl_short_name);
32 -- Bug 15914681
33 SELECT 'X'
34 INTO dummy
35 FROM all_tab_columns atc, user_synonyms syn
36 WHERE atc.owner = l_schema_owner
37 AND syn.synonym_name = p_table_name
38 AND syn.table_name = atc.table_name
39 AND syn.table_owner = atc.owner
40 AND atc.column_name = p_col_name
41 AND atc.data_type = p_col_data_type;
42
43 IF (SQL%FOUND) THEN
44 retValue:=TRUE;
45 RETURN retValue;
46 END IF;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN retValue;
51 END columnExists;
52
53
54 --wrapper to all actions in the process of migration.
55 PROCEDURE main(
56 p_appl_short_name IN VARCHAR2,
57 p_table_name IN VARCHAR2,
58 p_old_column_name IN VARCHAR2,
59 p_new_column_data_type IN VARCHAR2,
60 p_mode IN VARCHAR2
61 )AS
62
63 TYPE cur_type IS REF CURSOR;
64
65 l_cur cur_type;
66 l_str VARCHAR2 (500);
67 l_schema VARCHAR2 (30);
68 l_table_name VARCHAR2 (30);
69 l_old_column_name VARCHAR2 (30);
70 l_old_data_type VARCHAR2 (30);
71 l_new_column_name VARCHAR2 (30);
72 l_new_data_type VARCHAR2 (30);
73 l_curr_status VARCHAR2 (20);
74 l_action VARCHAR2 (30);
75 l_rowid VARCHAR2 (30);
76 l_init_process BOOLEAN := FALSE;
77 l_add_columns BOOLEAN := FALSE;
78 l_add_triggers BOOLEAN := FALSE;
79 l_conv_data BOOLEAN := FALSE;
80 l_drop_triggers BOOLEAN := FALSE;
81 l_drop_columns BOOLEAN := FALSE;
82 l_rename_columns BOOLEAN := FALSE;
83 l_mark_columns_as_unused BOOLEAN := FALSE;
84 lp_table_name VARCHAR2(30);
85 lp_old_column_name VARCHAR2(30);
86 lp_new_column_data_type VARCHAR2(30);
87 lp_mode VARCHAR2(30);
88
89
90 BEGIN
91
92 lp_table_name:=Upper(p_table_name);
93 lp_old_column_name:=Upper(p_old_column_name);
94 lp_new_column_data_type:=Upper(p_new_column_data_type);
95 lp_mode:=Upper(p_mode);
96
97 IF p_mode IN ( 'ALL_DROP','ALL_UNUSED','INIT') AND columnExists(p_appl_short_name,
98 lp_table_name, lp_old_column_name, lp_new_column_data_type) = TRUE THEN
99 RETURN;
100 END IF;
101
102 IF (lp_mode = 'ALL_DROP')
103 THEN
104 l_init_process := TRUE;
105 l_add_columns := TRUE;
106 l_conv_data := TRUE;
107 l_rename_columns := TRUE;
108 l_drop_columns := TRUE;
109 END IF;
110
111 IF (lp_mode = 'ALL_UNUSED')
112 THEN
113 l_init_process := TRUE;
114 l_add_columns := TRUE;
115 l_conv_data := TRUE;
116 l_rename_columns := TRUE;
117 l_mark_columns_as_unused := TRUE;
118 END IF;
119
120 IF (lp_mode = 'INIT')
121 THEN
122 l_init_process := TRUE;
123 l_add_columns := TRUE;
124 l_add_triggers := TRUE;
125 END IF;
126
127 IF (lp_mode = 'MIGRATE')
128 THEN
129 l_conv_data := TRUE;
130 l_rename_columns := TRUE;
131 l_drop_triggers := TRUE;
132 END IF;
133
134 IF (lp_mode = 'DROP')
135 THEN
136 l_drop_columns := TRUE;
137 END IF;
138
139 IF (lp_mode = 'UNUSED')
140 THEN
141 l_mark_columns_as_unused := TRUE;
142 END IF;
143
144 -- Steps-1 Intiliazation of AD tables.
145 IF (l_init_process = TRUE) THEN
146 BEGIN
147 ad_longtolob_pkg.initialize_process
148 (p_specific_table => lp_table_name);
149 END;
150 END IF;
151
152 -- Step-2 Addding of New Columns
153 IF (l_add_columns = TRUE)
154 THEN
155 BEGIN
156 l_str :=
157 ' SELECT schema_name, table_name, old_column_name,'
158 || ' new_column_name, new_data_type, status, '
159 || ' action, ROWID '
160 || ' FROM ad_long_column_conversions '
161 || ' WHERE status = '''
162 || ad_longtolob_pkg.g_initialized_status
163 || ''''
164 || ' AND upper(table_name) = upper('''
165 || lp_table_name
166 || ''')'
167 || ' AND upper(old_column_name) = upper('''
168 || lp_old_column_name
169 || ''')';
170
171 OPEN l_cur
172 FOR l_str;
173
174 LOOP
175 FETCH l_cur
176 INTO l_schema, l_table_name, l_old_column_name,
177 l_new_column_name, l_new_data_type, l_curr_status,
178 l_action, l_rowid;
179
180 EXIT WHEN l_cur%NOTFOUND;
181
182 BEGIN
183 IF (l_curr_status = ad_longtolob_pkg.g_initialized_status)
184 THEN
185 -- overrding the new column datatype
186 IF ( lp_new_column_data_type IS NOT NULL
187 AND lp_new_column_data_type IN
188 ('VARCHAR2', 'CLOB', 'BLOB')
189 )
190 THEN
191 l_new_data_type := lp_new_column_data_type;
192 END IF;
193
194 ad_longtolob_pkg.add_new_column (l_schema,
195 l_table_name,
196 l_old_column_name,
197 l_new_column_name,
198 l_new_data_type,
199 l_curr_status,
200 l_action
201 );
202 END IF;
203 EXCEPTION
204 WHEN OTHERS
205 THEN
206 RAISE_APPLICATION_ERROR (-20001,'Exception in adding the new column.');
207 END;
208 END LOOP;
209
210 IF l_cur%ISOPEN
211 THEN
212 CLOSE l_cur;
213 END IF;
214 END;
215 END IF;
216
217 -- Step-3 Addding of Triggers Columns
218 IF (l_add_triggers = TRUE)
219 THEN
220 BEGIN
221 l_str :=
222 ' SELECT schema_name, table_name, old_column_name,'
223 || ' new_column_name, new_data_type, status, '
224 || ' action, ROWID '
225 || ' FROM ad_long_column_conversions '
226 || ' WHERE status = '''
227 || ad_longtolob_pkg.g_add_new_column_status
228 || ''''
229 || ' and upper(table_name) = upper('''
230 || lp_table_name
231 || ''')'
232 || ' AND upper(old_column_name) = upper('''
233 || lp_old_column_name
234 || ''')';
235
236 OPEN l_cur
237 FOR l_str;
238
239 LOOP
240 FETCH l_cur
241 INTO l_schema, l_table_name, l_old_column_name,
242 l_new_column_name, l_new_data_type, l_curr_status,
243 l_action, l_rowid;
244
245 EXIT WHEN l_cur%NOTFOUND;
246
247 BEGIN
248 IF (l_curr_status = ad_longtolob_pkg.g_add_new_column_status
249 )
250 THEN
251 --overrding the new column datatype
252 IF ( lp_new_column_data_type IS NOT NULL
253 AND lp_new_column_data_type IN
254 ('VARCHAR2', 'CLOB', 'BLOB')
255 )
256 THEN
257 l_new_data_type := lp_new_column_data_type;
258 END IF;
259
260 ad_longtolob_pkg.create_transform_triggers
261 (l_schema,
262 l_table_name,
263 l_old_column_name,
264 l_new_column_name,
265 l_new_data_type
266 );
267 END IF;
268 EXCEPTION
269 WHEN OTHERS
270 THEN
271 RAISE_APPLICATION_ERROR (-20001,'Exception in adding the triggers.');
272 END;
273 END LOOP;
274
275 IF l_cur%ISOPEN
276 THEN
277 CLOSE l_cur;
278 END IF;
279 END;
280 END IF;
281
282 --Step-4 Data Migration.
283 IF (l_conv_data = TRUE)
284 THEN
285 BEGIN
286 l_str :=
287 ' SELECT schema_name, table_name, old_column_name,'
288 || ' old_data_type, new_column_name, new_data_type, status, '
289 || ' action, ROWID '
290 || ' FROM ad_long_column_conversions '
291 || ' WHERE status IN ('''
292 || ad_longtolob_pkg.g_add_trigger_status
293 || ''','''|| ad_longtolob_pkg.g_add_new_column_status || ''''
294 || ') AND upper(table_name) = upper('''
295 || lp_table_name
296 || ''')'
297 || ' AND upper(old_column_name) = upper('''
298 || lp_old_column_name
299 || ''')';
300 OPEN l_cur
301 FOR l_str;
302
303 LOOP
304 FETCH l_cur
305 INTO l_schema, l_table_name, l_old_column_name, l_old_data_type,
306 l_new_column_name, l_new_data_type, l_curr_status,
307 l_action, l_rowid;
308
309 EXIT WHEN l_cur%NOTFOUND;
310
311 BEGIN
312 IF (l_curr_status IN (ad_longtolob_pkg.g_add_trigger_status,ad_longtolob_pkg.g_add_new_column_status))
313 THEN
314 ad_longtolob_pkg.update_new_data (l_schema,
315 l_table_name,
316 l_old_column_name,
317 l_old_data_type,
318 l_new_column_name
319 );
320 END IF;
321 EXCEPTION
322 WHEN OTHERS
323 THEN
324 RAISE_APPLICATION_ERROR (-20001,'Exception in migration of long data to clob data.');
325 END;
326 END LOOP;
327
328 IF l_cur%ISOPEN
329 THEN
330 CLOSE l_cur;
331 END IF;
332 END;
333 END IF;
334
335 -- Step-5 Renaming of Columns
336 IF (l_rename_columns = TRUE)
337 THEN
338 DECLARE
339 CURSOR c1 (cur_p_table_name VARCHAR2, cur_old_column_name VARCHAR2)
340 IS
341 SELECT schema_name, table_name, old_column_name, new_column_name
342 FROM ad_long_column_conversions
343 WHERE status IN
344 (ad_longtolob_pkg.g_update_rows_status,
345 ad_longtolob_pkg.g_drop_old_column_status
346 )
347 AND table_name = cur_p_table_name
348 AND old_column_name = cur_old_column_name;
349
350 l_column_name VARCHAR2 (30);
351 l_command VARCHAR2 (300);
352 l_flag NUMBER;
353 BEGIN
354 l_flag := 0;
355
356 FOR rec IN c1 (lp_table_name, lp_old_column_name)
357 LOOP
358 l_command :=
359 'alter table '
360 || rec.schema_name
361 || '.'
362 || rec.table_name
363 || ' rename column '
364 || rec.old_column_name
365 || ' to '
366 || SUBSTR (rec.old_column_name, 1, 26)
367 || '_old';
368
369 BEGIN
370 EXECUTE IMMEDIATE l_command;
371 EXCEPTION
372 WHEN OTHERS
373 THEN
374 RAISE_APPLICATION_ERROR (-20001,'Exception in renaming the old column.');
375 END;
376
377 IF rec.new_column_name LIKE 'R118_%'
378 THEN
379 SELECT LTRIM (rec.new_column_name, 'R118_')
380 INTO l_column_name
381 FROM DUAL;
382
383 l_command :=
384 'alter table '
385 || rec.schema_name
386 || '.'
387 || rec.table_name
388 || ' rename column '
389 || rec.new_column_name
390 || ' to '
391 || l_column_name;
392
393 BEGIN
394 EXECUTE IMMEDIATE l_command;
395 EXCEPTION
396 WHEN OTHERS
397 THEN
398 RAISE_APPLICATION_ERROR (-20001,'Exception in renaming the new column.');
399 END;
400 END IF;
401
402 UPDATE ad_long_column_conversions
403 SET status = ad_longtolob_pkg.g_col_renamed_status
404 WHERE schema_name = rec.schema_name
405 AND table_name = rec.table_name
406 AND old_column_name = rec.old_column_name;
407 COMMIT;
408 END LOOP;
409 END;
410 END IF;
411
412 --Step-6 Dropping Triggers
413 IF (l_drop_triggers = TRUE)
414 THEN
415 DECLARE
416 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
417 IS
418 SELECT schema_name, table_name, old_column_name
419 FROM ad_long_column_conversions
420 WHERE status IN (ad_longtolob_pkg.g_col_renamed_status)
421 AND table_name = cur_table_name
422 AND old_column_name = cur_old_column_name;
423
424 l_command VARCHAR2 (300);
425 BEGIN
426 FOR rec IN c1 (lp_table_name, lp_old_column_name)
427 LOOP
428 l_command :=
429 'drop trigger ' || SUBSTR (rec.table_name, 1, 24)
430 || '_$R2U1';
431
432 BEGIN
433 EXECUTE IMMEDIATE l_command;
434 EXCEPTION
435 WHEN OTHERS
436 THEN
437 RAISE_APPLICATION_ERROR (-20001,'Exception in dropping the 1st trigger.');
438 END;
439
440 l_command :=
441 'drop trigger ' || SUBSTR (rec.table_name, 1, 24)
442 || '_$R2U2';
443
444 BEGIN
445 EXECUTE IMMEDIATE l_command;
446 EXCEPTION
447 WHEN OTHERS
448 THEN
449 RAISE_APPLICATION_ERROR (-20001,'Exception in dropping the 2nd trigger.');
450 END;
451
452 UPDATE ad_long_column_conversions
453 SET status = ad_longtolob_pkg.g_drop_trigger_status
454 WHERE schema_name = rec.schema_name
455 AND table_name = rec.table_name
456 AND old_column_name = rec.old_column_name;
457 COMMIT;
458 END LOOP;
459 END;
460 END IF;
461
462 --Step-7 Marking Columns as Unused
463 IF (l_mark_columns_as_unused = TRUE)
464 THEN
465 DECLARE
466 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
467 IS
468 SELECT schema_name, table_name, old_column_name
469 FROM ad_long_column_conversions
470 WHERE status IN (ad_longtolob_pkg.g_drop_trigger_status,ad_longtolob_pkg.g_col_renamed_status)
471 AND table_name = cur_table_name
472 AND old_column_name = cur_old_column_name;
473
474 l_column_name VARCHAR2 (30);
475 l_command VARCHAR2 (300);
476 BEGIN
477 FOR rec IN c1 (lp_table_name, lp_old_column_name)
478 LOOP
479 l_command :=
480 'alter table '
481 || rec.schema_name
482 || '.'
483 || rec.table_name
484 || ' set unused column '
485 || SUBSTR (rec.old_column_name, 1, 26)
486 || '_old';
487
488 BEGIN
489 EXECUTE IMMEDIATE l_command;
490 EXCEPTION
491 WHEN OTHERS
492 THEN
493 RAISE_APPLICATION_ERROR (-20001,'Exception in marking the columns as unused.');
494 END;
495
496 UPDATE ad_long_column_conversions
497 SET status = ad_longtolob_pkg.g_complete_status
498 WHERE schema_name = rec.schema_name
499 AND table_name = rec.table_name
500 AND old_column_name = rec.old_column_name;
501 COMMIT;
502 END LOOP;
503 END;
504 END IF;
505
506 --Step-8 Dropping of Columns
507 IF (l_drop_columns = TRUE)
508 THEN
509 DECLARE
510 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
511 IS
512 SELECT schema_name, table_name, old_column_name
513 FROM ad_long_column_conversions
514 WHERE status IN (
515 ad_longtolob_pkg.g_drop_trigger_status,ad_longtolob_pkg.g_col_renamed_status)
516 AND table_name = cur_table_name
517 AND old_column_name = cur_old_column_name;
518
519 l_column_name VARCHAR2 (30);
520 l_command VARCHAR2 (300);
521 BEGIN
522 FOR rec IN c1 (lp_table_name, lp_old_column_name)
523 LOOP
524 l_command :=
525 'alter table '
526 || rec.schema_name
527 || '.'
528 || rec.table_name
529 || ' drop column '
530 || SUBSTR (rec.old_column_name, 1, 26)
531 || '_old';
532
533 BEGIN
534 EXECUTE IMMEDIATE l_command;
535 EXCEPTION
536 WHEN OTHERS
537 THEN
538 RAISE_APPLICATION_ERROR (-20001,'Exception in dropping the old column.');
539 END;
540
541 UPDATE ad_long_column_conversions
542 SET status = ad_longtolob_pkg.g_complete_status
543 WHERE schema_name = rec.schema_name
544 AND table_name = rec.table_name
545 AND old_column_name = rec.old_column_name;
546 COMMIT;
547 END LOOP;
548 END;
549 END IF;
550 END MAIN;
551
552 -- init procedure will do the following steps.
553 -- 1.initialize the AD tables with required data.
554 -- 2.Adding the columns
555 -- 3.Adding the Triggers
556
557 PROCEDURE DO_INIT(
558 p_appl_short_name IN VARCHAR2,
559 p_table_name IN VARCHAR2,
560 p_old_column_name IN VARCHAR2,
561 p_new_column_data_type IN VARCHAR2) AS
562
563 BEGIN
564
565 main(p_appl_short_name,p_table_name,p_old_column_name,p_new_column_data_type,'INIT');
566
567 END DO_INIT;
568
569 -- migrate procedure will do the following steps.
570 -- 1.migration of data.
571 -- 2.Renaming the columns
572 -- 3.Dropping the triggers.
573
574 PROCEDURE DO_MIGRATE(
575 p_appl_short_name IN VARCHAR2,
576 p_table_name IN VARCHAR2,
577 p_old_column_name IN VARCHAR2) AS
578
579 BEGIN
580
581 main(p_appl_short_name,p_table_name,p_old_column_name,Null,'MIGRATE');
582
583 END DO_MIGRATE;
584
585 -- drop procedure will do the following step.
586 -- 1.dropping the column
587
588 PROCEDURE DO_DROP(
589 p_appl_short_name IN VARCHAR2,
590 p_table_name IN VARCHAR2,
591 p_old_column_name IN VARCHAR2) AS
592
593 BEGIN
594
595 main(p_appl_short_name,p_table_name,p_old_column_name,NULL,'DROP');
596
597 END DO_DROP;
598
599 -- unused procedure will do the following step.
600 -- 1.marks the column as unused.
601
602 PROCEDURE DO_UNUSED(
603 p_appl_short_name IN VARCHAR2,
604 p_table_name IN VARCHAR2,
605 p_old_column_name IN VARCHAR2) AS
606
607 BEGIN
608
609 main(p_appl_short_name,p_table_name,p_old_column_name,NULL,'UNUSED');
610
611 END DO_UNUSED;
612
613 -- ALL_DROP procedure will do the following steps.
614 -- 1.initialize the AD tables with required data.
615 -- 2.Adding the column
616 -- 3.migration of data.
617 -- 4.Renaming the column
618 -- 5.Dropping the column
619
620 PROCEDURE DO_ALL_DROP(
621 p_appl_short_name IN VARCHAR2,
622 p_table_name IN VARCHAR2,
623 p_old_column_name IN VARCHAR2,
624 p_new_column_data_type IN VARCHAR2) AS
625
626 BEGIN
627
628 main(p_appl_short_name,p_table_name,p_old_column_name,p_new_column_data_type,'ALL_DROP');
629
630 END DO_ALL_DROP;
631
632 -- ALL_DROP procedure will do the following steps.
633 -- 1.initialize the AD tables with required data.
634 -- 2.Adding the column
635 -- 3.migration of data.
636 -- 4.Renaming the column
637 -- 5.Marking the column as unused.
638
639 PROCEDURE DO_ALL_UNUSED(
640 p_appl_short_name IN VARCHAR2,
641 p_table_name IN VARCHAR2,
642 p_old_column_name IN VARCHAR2,
643 p_new_column_data_type IN VARCHAR2) AS
644
645 BEGIN
646
647 main(p_appl_short_name, p_table_name,p_old_column_name,p_new_column_data_type,'ALL_UNUSED');
648
649 END DO_ALL_UNUSED;
650
651
652 END HR_LONG2LOB;