1 PACKAGE BODY cct_upgrade_pkg AS
2 /* $Header: cctuupgb.pls 115.22 2004/03/26 00:27:13 sradhakr ship $ */
3 PROCEDURE add_column (schema_name IN VARCHAR2,
4 table_name IN VARCHAR2, col_name IN VARCHAR2) IS
5
6 table_not_found exception;
7 pragma exception_init(table_not_found, -942);
8
9 duplicate_column exception;
10 pragma exception_init(duplicate_column, -1430);
11 BEGIN
12 SAVEPOINT CCTUPG;
13 EXECUTE IMMEDIATE 'LOCK TABLE ' || schema_name || '.' || table_name
14 || ' IN EXCLUSIVE MODE';
15 EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_name || '.' || table_name
16 || ' ADD ( ' || col_name || ' NUMBER (15,0) )';
17 COMMIT ; -- used to release the table lock.
18 EXCEPTION
19 WHEN table_not_found THEN
20 COMMIT ; -- used to release the table lock.
21 raise_application_error(-20000, 'Table '||table_name||' not found') ;
22
23 WHEN duplicate_column THEN
24 COMMIT ; -- used to release the table lock.
25 -- not a problem allows the script to be rerun.
26 null;
27
28 WHEN OTHERS THEN
29 COMMIT ; -- used to release the table lock.
30 raise_application_error(-20000, sqlerrm || '. Could not add column') ;
31 END ADD_COLUMN;
32
33 -----------------------------------------------------------------------
34
35 PROCEDURE add_varchar_column (schema_name IN VARCHAR2,
36 table_name IN VARCHAR2, col_name IN VARCHAR2) IS
37
38 table_not_found exception;
39 pragma exception_init(table_not_found, -942);
40
41 duplicate_column exception;
42 pragma exception_init(duplicate_column, -1430);
43 BEGIN
44 SAVEPOINT CCTUPG_VAR_COL;
45 EXECUTE IMMEDIATE 'LOCK TABLE ' || schema_name || '.' || table_name
46 || ' IN EXCLUSIVE MODE';
47 EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_name || '.' || table_name
48 || ' ADD ( ' || col_name || ' VARCHAR2 (64) )';
49 COMMIT ; -- used to release the table lock.
50 EXCEPTION
51 WHEN table_not_found THEN
52 COMMIT ; -- used to release the table lock.
53 raise_application_error(-20000, 'Table '||table_name||' not found') ;
54
55 WHEN duplicate_column THEN
56 COMMIT ; -- used to release the table lock.
57 -- not a problem allows the script to be rerun.
58 null;
59
60 WHEN OTHERS THEN
61 COMMIT ; -- used to release the table lock.
62 raise_application_error(-20000, sqlerrm || '. Could not add column') ;
63 END ADD_VARCHAR_COLUMN;
64
65
66 -----------------------------------------------------------------------
67 PROCEDURE update_column
68 (
69 table_name IN VARCHAR2
70 , new_col_name IN VARCHAR2
71 , old_col_name IN VARCHAR2
72 , new_value IN NUMBER
73 , old_value IN NUMBER) IS
74
75 table_not_found exception;
76 pragma exception_init(table_not_found, -942);
77
78
79 BEGIN
80 SAVEPOINT CCTUPG;
81 EXECUTE IMMEDIATE 'SELECT ' ||old_col_name ||' from '|| table_name
82 || ' WHERE ' || old_col_name || ' = :y '
83 || ' FOR UPDATE ' USING old_value ;
84
85 EXECUTE IMMEDIATE 'UPDATE ' || table_name
86 || ' SET ' || new_col_name || ' = :x '
87 || ' WHERE ' || old_col_name || ' = :y ' USING
88 new_value, old_value;
89 EXCEPTION
90 WHEN table_not_found THEN
91 COMMIT ; -- used to release the table lock.
92 raise_application_error(-20000, 'Table '||table_name||' not found') ;
93
94 WHEN OTHERS THEN
95 COMMIT ; -- used to release the table lock.
96 raise_application_error (-20000, sqlerrm || '. Could not update column.') ;
97 END UPDATE_COLUMN;
98
99 -----------------------------------------------------------------------
100 PROCEDURE modify_column_to_nullable (
101 schema_name IN VARCHAR2
102 , table_name IN VARCHAR2
103 , col_name IN VARCHAR2) IS
104
105 table_not_found exception;
106 pragma exception_init(table_not_found, -942);
107
108 column_not_found exception;
109 pragma exception_init(column_not_found, -904);
110
111 null_column exception;
112 pragma exception_init(column_not_found, -1451);
113
114
115 BEGIN
116 SAVEPOINT CCTUPG;
117 EXECUTE IMMEDIATE 'LOCK TABLE ' || schema_name || '.' || table_name
118 || ' IN EXCLUSIVE MODE';
119 EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_name || '.' || table_name
120 || ' MODIFY ' || col_name || ' NUMBER NULL ' ;
121
122 COMMIT ; -- used to release the table lock.
123
124 EXCEPTION
125 WHEN table_not_found THEN
126 COMMIT ; -- used to release the table lock.
127 raise_application_error(-20000, 'Table '||table_name||' not found') ;
128
129 WHEN column_not_found THEN
130 COMMIT ; -- used to release the table lock.
131 -- not a problem allows the script to be rerun.
132 null;
133
134 WHEN null_column THEN
135 COMMIT ; -- used to release the table lock.
136 -- not a problem allows the script to be rerun.
137 null;
138
139 WHEN OTHERS THEN
140 COMMIT ; -- used to release the table lock.
141 raise_application_error(-20000, sqlerrm || '. Could not modify column') ;
142 END MODIFY_COLUMN_TO_NULLABLE;
143
144
145 PROCEDURE modify_new_sequence_from_old (
146 schema_name IN VARCHAR2
147 ,sequence_name IN VARCHAR2
148 ,old_sequence_name IN VARCHAR2
149 ) IS
150
151 sequence_not_found exception;
152 pragma exception_init(sequence_not_found, -942);
153 l_old_seq_val VARCHAR2(10);
154 l_new_seq_stmt VARCHAR2(2000);
155 l_missing_sequence VARCHAR2(2000);
156 l_drop_Seq_stmt VARCHAR2(2000);
157 v_cursorID INTEGER;
158 v_dummy INTEGER;
159 v_old_seq_Stmt VARCHAR2(2000);
160 BEGIN
161 SAVEPOINT CCTUPGSEQ;
162 BEGIN
163 v_cursorID:=DBMS_SQL.OPEN_CURSOR;
164 v_old_seq_stmt:='SELECT '||schema_name||'.'||old_sequence_name||'.NEXTVAL from dual';
165 --dbms_output.put_line(v_old_seq_stmt);
166 DBMS_SQL.PARSE(v_cursorID,v_old_seq_stmt,DBMS_SQL.V7);
167 --dbms_output.put_line('after parsing');
168 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,l_old_seq_val,10);
169 --dbms_output.put_line('after define column');
170 v_dummy:=DBMS_SQL.EXECUTE(v_cursorID);
171 --dbms_output.put_line('after execute');
172 Loop
173 if DBMS_SQL.FETCH_ROWS(v_cursorID)=0 THEN
174 --dbms_output.put_line('exiting');
175 exit;
176 end if;
177 --dbms_output.put_line('before col valu');
178 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_old_seq_val);
179 --dbms_output.put_line(l_old_Seq_val);
180 END LOOP;
181 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
182 Exception
183 When others then
184 l_missing_sequence:=old_sequence_name;
185 raise sequence_not_found;
186 end;
187
188 -- dropping sequence
189 Begin
190 l_drop_seq_stmt:='DROP SEQUENCE '||schema_name||'.'||sequence_name;
191 --dbms_output.put_line(l_drop_seq_stmt);
192 EXECUTE IMMEDIATE l_drop_seq_stmt;
193 Exception
194 When others then
195 l_missing_sequence:=sequence_name;
196 raise sequence_not_found;
197 end;
198
199 --create new sequence
200 l_new_seq_stmt:='CREATE SEQUENCE '||schema_name||'.'||sequence_name||' NOCYCLE NOORDER START WITH '||l_old_seq_val;
201 --dbms_output.put_line(l_new_seq_stmt);
202 EXECUTE IMMEDIATE l_new_seq_stmt;
203
204 EXCEPTION
205 WHEN sequence_not_found THEN
206 raise_application_error(-20000, 'Sequence '||l_missing_sequence||' not found') ;
207
208 WHEN OTHERS THEN
209 raise_application_error(-20000, sqlerrm || '. Could not modify sequence') ;
210 END MODIFY_new_SEQUENCE_from_old;
211
212 -- Added Oct 05 2001 rajayara
213 -- Verify if new sequence is less than old sequence, if so drop sequence name and re create it
214 -- with old_seq_val, else do nothing
215 PROCEDURE modify_sequence_with_verify (
216 schema_name IN VARCHAR2
217 ,sequence_name IN VARCHAR2
218 ,old_sequence_name IN VARCHAR2
219 ) IS
220
221 sequence_not_found exception;
222 pragma exception_init(sequence_not_found, -942);
223 l_old_seq_val VARCHAR2(10);
224 l_new_seq_val VARCHAR2(10);
225 l_new_seq_stmt VARCHAR2(2000);
226 l_missing_sequence VARCHAR2(2000);
227 l_drop_Seq_stmt VARCHAR2(2000);
228 v_cursorID INTEGER;
229 v_cursorID1 INTEGER;
230 v_dummy INTEGER;
231 v_dummy1 INTEGER;
232 v_old_seq_Stmt VARCHAR2(2000);
233 v_new_seq_Stmt VARCHAR2(2000);
234 BEGIN
235 SAVEPOINT CCTUPGSEQ;
236
237 -- Getting the Old Sequence value
238
239 BEGIN
240 v_cursorID:=DBMS_SQL.OPEN_CURSOR;
241 v_old_seq_stmt:='SELECT '||schema_name||'.'||old_sequence_name||'.NEXTVAL from dual';
242 --dbms_output.put_line(v_old_seq_stmt);
243 DBMS_SQL.PARSE(v_cursorID,v_old_seq_stmt,DBMS_SQL.V7);
244 --dbms_output.put_line('after parsing');
245 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,l_old_seq_val,10);
246 --dbms_output.put_line('after define column');
247 v_dummy:=DBMS_SQL.EXECUTE(v_cursorID);
248 --dbms_output.put_line('after execute');
249 Loop
250 if DBMS_SQL.FETCH_ROWS(v_cursorID)=0 THEN
251 --dbms_output.put_line('exiting');
252 exit;
253 end if;
254 --dbms_output.put_line('before col valu');
255 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_old_seq_val);
256 --dbms_output.put_line(l_old_seq_val);
257 END LOOP;
258 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
259 Exception
260 When others then
261 l_missing_sequence:=old_sequence_name;
262 raise sequence_not_found;
263 end;
264
265 -- Getting the New Sequence value
266
267 BEGIN
268 v_cursorID1:=DBMS_SQL.OPEN_CURSOR;
269 v_new_seq_stmt:='SELECT '||schema_name||'.'||sequence_name||'.NEXTVAL from dual';
270 --dbms_output.put_line(v_new_seq_stmt);
271 DBMS_SQL.PARSE(v_cursorID1,v_new_seq_stmt,DBMS_SQL.V7);
272 --dbms_output.put_line('after parsing');
273 DBMS_SQL.DEFINE_COLUMN(v_cursorID1,1,l_new_seq_val,10);
274 --dbms_output.put_line('after define column');
275 v_dummy1:=DBMS_SQL.EXECUTE(v_cursorID1);
276 --dbms_output.put_line('after execute');
277 Loop
278 if DBMS_SQL.FETCH_ROWS(v_cursorID1)=0 THEN
279 --dbms_output.put_line('exiting');
280 exit;
281 end if;
282 --dbms_output.put_line('before col valu');
283 DBMS_SQL.COLUMN_VALUE(v_cursorID1,1,l_new_seq_val);
284 --dbms_output.put_line(l_new_seq_val);
285 END LOOP;
286 DBMS_SQL.CLOSE_CURSOR(v_cursorID1);
287 Exception
288 When others then
289 l_missing_sequence:=sequence_name;
290 l_missing_sequence:=l_missing_sequence||' SQLERRM::'||sqlerrm;
291 raise sequence_not_found;
292 end;
293
294 -- if new sequence is less than old sequence then bump up new sequence to old seq, else do nothing
295 --dbms_output.put_line('New Sequence='||l_new_Seq_val||', l_old_Seq_val='||l_old_Seq_val);
296 if (to_number(l_new_Seq_val) < to_number(l_old_Seq_val) )
297 then
298 -- dropping sequence
299 Begin
300 l_drop_seq_stmt:='DROP SEQUENCE '||schema_name||'.'||sequence_name;
301 --dbms_output.put_line(l_drop_seq_stmt);
302 EXECUTE IMMEDIATE l_drop_seq_stmt;
303 Exception
304 When others then
305 l_missing_sequence:=sequence_name;
306 raise sequence_not_found;
307 end;
308
309 --create new sequence
310 l_new_seq_stmt:='CREATE SEQUENCE '||schema_name||'.'||sequence_name||' NOCYCLE NOORDER START WITH '||l_old_seq_val;
311 --dbms_output.put_line(l_new_seq_stmt);
312 EXECUTE IMMEDIATE l_new_seq_stmt;
313 END IF;
314
315 EXCEPTION
316 WHEN sequence_not_found THEN
317 raise_application_error(-20000, 'Sequence '||l_missing_sequence||' not found') ;
318
319 WHEN OTHERS THEN
320 raise_application_error(-20000, sqlerrm || '. Could not modify sequence') ;
321 END modify_sequence_with_verify;
322
323 -----------------------------------------------------------------------
324 PROCEDURE drop_column (schema_name IN VARCHAR2,
325 table_name IN VARCHAR2, col_name IN VARCHAR2) IS
326
327 table_not_found exception;
328 pragma exception_init(table_not_found, -942);
329
330 column_not_found exception;
331 pragma exception_init(column_not_found, -904);
332 BEGIN
333 SAVEPOINT CCTUPG;
334 EXECUTE IMMEDIATE 'LOCK TABLE ' || schema_name || '.' || table_name
335 || ' IN EXCLUSIVE MODE';
336 EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_name || '.' || table_name
337 || ' DROP COLUMN ' || col_name ;
338 COMMIT ; -- used to release the table lock.
339 EXCEPTION
340 WHEN table_not_found THEN
341 COMMIT ; -- used to release the table lock.
342 raise_application_error(-20000, 'Table '||table_name||' not found') ;
343
344 WHEN column_not_found THEN
345 COMMIT ; -- used to release the table lock.
346 -- not a problem allows the script to be rerun.
347 null;
348
349 WHEN OTHERS THEN
350 COMMIT ; -- used to release the table lock.
351 raise_application_error(-20000, 'Could not drop column') ;
352 END DROP_COLUMN;
353
354 -----------------------------------------------------------------------
355 PROCEDURE copy_all_rows (old_table_name IN VARCHAR2
356 , new_table_name IN VARCHAR2)
357 IS
358
359 CURSOR c1 IS
360 SELECT column_name
361 FROM all_tab_columns
362 WHERE table_name = old_table_name
363 and owner=(Select user from dual);
364
365 l_string VARCHAR2 (8000) ;
366
367 l_table_name VARCHAR2 (40);
368 l_column_name VARCHAR2 (40);
369
370 l_delete_string VARCHAR2 (200);
371
372 table_not_found exception;
373 pragma exception_init(table_not_found, -942);
374
375 column_not_found exception;
376 pragma exception_init(column_not_found, -904);
377
378 BEGIN
379 BEGIN
380 -- Get all the column names in the old table
381 OPEN c1;
382 FETCH c1 INTO l_column_name;
383 l_string := l_column_name;
384 LOOP
385 FETCH c1 INTO l_column_name;
386 EXIT WHEN c1%NOTFOUND;
387 l_string := l_string || ', ' || l_column_name;
388 END LOOP;
389 CLOSE c1;
390 EXCEPTION
391 WHEN table_not_found THEN
392 l_table_name := old_table_name;
393 raise ;
397
394 END;
395
396 l_table_name := new_table_name;
398 SAVEPOINT CCTUPG;
399 EXECUTE IMMEDIATE 'LOCK TABLE ' || new_table_name
400 || ',' || old_table_name || ' IN EXCLUSIVE MODE';
401
402 -- delete all rows from the new table before inserting
403 l_delete_string := 'DELETE FROM ' || new_table_name;
404 EXECUTE IMMEDIATE l_delete_string;
405
406 -- Insert into the new table
407 l_string := ' INSERT INTO ' || new_table_name
408 || ' SELECT ' || l_string || ' FROM ' || old_table_name ;
409
410 EXECUTE IMMEDIATE l_string;
411 COMMIT ; -- required to release the table locks
412 EXCEPTION
413 WHEN table_not_found THEN
414 COMMIT ; -- used to release the table lock.
415 raise_application_error(-20000, 'Table '||l_table_name||' not found') ;
416
417 WHEN OTHERS THEN
418 COMMIT ; -- used to release the table lock.
419 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
420
421
422 END COPY_ALL_ROWS;
423
424 PROCEDURE delete_fnd_lookups( lookupType IN VARCHAR2
425 ,lookupCode IN VARCHAR2
426 )
427 IS
428 CURSOR csr_installed_langs IS
429 SELECT distinct language FROM fnd_lookup_values
430 WHERE lookup_type=lookupType;
431
432 lang fnd_languages.language_code%TYPE;
433 l_string VARCHAR2(20) := 'DELETE_FND_LOOKUPS';
434
435 BEGIN
436
437 OPEN csr_installed_langs;
438
439 LOOP
440 FETCH csr_installed_langs into lang;
441 EXIT WHEN csr_installed_langs%NOTFOUND;
442
443 delete_fnd_lookups( lookupType,lookupCode, lang);
444
445 END LOOP;
446
447 CLOSE csr_installed_langs;
448
449 EXCEPTION
450 WHEN others THEN
451 CLOSE csr_installed_langs;
452 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
453 END;
454
455 PROCEDURE delete_fnd_lookups( lookupType IN VARCHAR2
456 , lookupCode IN VARCHAR2
457 , lang IN VARCHAR2)
458 IS
459 l_string VARCHAR2(25) := 'DELETE_FND_LOOKUPS-LANG';
460 BEGIN
461
462 if lookupCode is null then
463
464 DELETE fnd_lookup_values
465 WHERE lookup_type=lookupType
466 AND language = lang;
467
468 -- Also deleting the lookup type as we have deleted all the values
469 DELETE FND_LOOKUPS
470 WHERE lookup_type=lookupType;
471
472 else
473
474 DELETE fnd_lookup_values
475 WHERE lookup_type=lookupType
476 AND lookup_code = lookupCode
477 AND language = lang;
478
479 -- Also deleting the lookup type as we have deleted all the values
480 DELETE FND_LOOKUPS
481 WHERE lookup_type=lookupType
482 AND lookup_code = lookupCode;
483
484 end if;
485
486
487 EXCEPTION
488 WHEN others THEN
489 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
490 END;
491
492 Procedure change_params_in_mvalues
493 IS
494 l_string VARCHAR2(64) := 'change_mware_paramsinvalues';
495 Begin
496 Begin
497 update cct_middleware_values
498 set name='MW_SERVER_IPADDRESS'
499 where name='OT_SERVER_IPADDRESS'
500 or name='OPENTEL_SERVER_NAME';
501 Exception
502 When others then
503 null;
504 End;
505
506 Begin
507 update cct_middleware_values
508 set name='MW_SERVER_INFO_1'
509 where name='OT_SERVER_INFO1'
510 or name='OT_SERVER_INFO_1';
511
512 Exception
513 When others then
514 null;
515 End;
516
517
518 Begin
519 update cct_middleware_values
520 set name='MW_SERVER_INFO_2'
521 where name='OT_SERVER_INFO2'
522 or name='OT_SERVER_INFO_2';
523
524 Exception
525 When others then
526 null;
527 End;
528
529 Begin
530 update cct_middleware_values
531 set name='MW_SERVER_INFO_3'
532 where name='OT_SERVER_INFO3'
533 or name='OT_SERVER_INFO_3';
534
535 Exception
536 When others then
537 null;
538 End;
539
540 Begin
541 update cct_middleware_values
542 set name='MW_SERVER_INFO_4'
543 where name='OT_SERVER_INFO4'
544 or name='OT_SERVER_INFO_4';
545
546 Exception
547 When others then
548 null;
549 End;
550
551 Begin
552 update cct_middleware_values
553 set name='MW_SERVER_INFO_5'
554 where name='OT_SERVER_INFO5'
555 or name='OT_SERVER_INFO_5';
556
557 Exception
558 When others then
559 null;
560 End;
561
562 Begin
563 delete cct_middleware_values
564 where name='CTI_ENABLER_NAME'
565 or name='LOCAL_CALL_DATA_FILE';
566 Exception
567 When others then
571
568 null;
569 End;
570
572 Exception
573 WHEN others THEN
574 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
575 END;
576
577 Procedure delete_prospect_aspect
578 IS
579 Cursor c_mw
580 is
581 Select a.middleware_id
582 from cct_middlewares a,cct_middleware_types b
583 where a.middleware_type_id=b.middleware_type_id
584 and b.middleware_type='PROSPECT_ASPECT';
585
586 Begin
587
588 For v_mw in c_mw
589 Loop
590 Begin
591 delete cct_middleware_values
592 where middleware_id=v_mw.middleware_id;
593 Exception
594 When others then
595 --dbms_output.put_line('error in deleting middleware values for Prospect Aspect');
596 null;
597 End;
598 Begin
599 delete jtf_rs_ResourcE_values
600 where value_type=v_mw.middleware_id;
601 Exception
602 When others then
603 --dbms_output.put_line('error in deleting resourcE_middleware values for Prospect Aspect');
604 null;
605 End;
606 Begin
607 delete cct_middlewares
608 where middleware_id=v_mw.middleware_id;
609 Exception
610 When others then
611 --dbms_output.put_line('error in deleting middleware for type Prospect Aspect');
612 null;
613 End;
614 End Loop;
615 Begin
616 delete cct_middleware_params
617 where middleware_type_id=(Select middleware_Type_id
618 from cct_middleware_types
619 where middleware_type='PROSPECT_ASPECT');
620 Exception
621 When others then
622 --dbms_output.put_line('error in deleting middleware_param for Prospect Aspect');
623 null;
624 End;
625 Begin
626 delete jtf_rs_resource_params
627 where param_type=(Select middleware_Type_id
628 from cct_middleware_types
629 where middleware_type='PROSPECT_ASPECT');
630 Exception
631 When others then
632 --dbms_output.put_line('error in deleting middleware_param for Prospect Aspect');
633 null;
634 End;
635 Begin
636 delete cct_middleware_types
637 where middleware_type='PROSPECT_ASPECT';
638 Exception
639 When others then
640 --dbms_output.put_line('error in deleting middleware_type=Prospect Aspect');
641 null;
642 End;
643 Exception
644 When others then
645 null;
646 End;
647
648
649 Procedure upgrade_mware_values
650 IS
651 l_mvid Number;
652 l_mpname Varchar2(64);
653 l_mpid Number;
654 l_string VARCHAR2(64) := 'upgrade_mware_values';
655 Cursor c_mvp
656 is
657 Select a.middleware_value_id,a.name,b.middleware_param_id
658 from cct_middleware_values a, cct_middleware_params b,
659 cct_middlewares c
660 where a.name=b.name
661 and a.middleware_param_id is null
662 and a.middleware_id=c.middleware_id
663 and c.middleware_type_id=b.middleware_type_id;
664 Begin
665 for v_mvp in c_mvp LOOP
666 --dbms_output.put_line('in loop');
667 update cct_middleware_values
668 set middleware_param_id=v_mvp.middleware_param_id
669 where middleware_value_id=v_mvp.middleware_value_id;
670 end loop;
671 Exception
672 when others then
673 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
674 END;
675
676 Procedure update_agparam_in_agvalues(p_old_type IN VARCHAR2,p_old_param_name IN VARCHAR2,p_new_type IN VARCHAR2,p_new_param_name IN VARCHAR2)
677 IS
678 Cursor c_value
679 is
680 Select resource_param_value_id
681 from jtf_rs_resource_values v,jtf_rs_resource_params p,cct_middleware_types t
682 where t.middleware_type=p_old_type
683 and p.param_type=t.middleware_type_id
684 and p.name=p_old_param_name
685 and v.resource_param_id=p.resource_param_id;
686
687 l_new_param_id Number;
688 l_string VARCHAR2(1000);
689 Begin
690 l_string:='Error updating JTF_RS_RESOURCE_VALUES for middleware_type='||p_old_type||' agent param name='||p_old_param_name;
691 Select resourcE_param_id
692 into l_new_param_id
693 from jtf_rs_resource_params p,cct_middleware_types t
694 where t.middleware_type=p_new_type
695 and p.param_type=t.middleware_type_id
696 and p.name=p_new_param_name;
697
698 for v_valueID in c_value LOOP
699 update jtf_rs_resource_values
700 set resourcE_param_id=l_new_param_id
701 where resource_param_value_id=v_valueID.resource_param_value_id;
702 end loop;
703 Exception
704 When others then
705 raise_application_error(-20000, sqlerrm || '.' || l_string ) ;
706 END;
707
708
709 Procedure update_mwparam_in_mwvalues(p_old_type IN VARCHAR2,p_old_param_name IN VARCHAR2,p_new_type IN VARCHAR2,p_new_param_name IN VARCHAR2)
710 IS
711 Cursor c_value
712 is
713 Select middleware_value_id
714 from cct_middleware_values v,cct_middleware_params p,cct_middleware_types t
715 where t.middleware_type=p_old_type
716 and p.middleware_type_id=t.middleware_type_id
720 l_new_param_id Number;
717 and p.name=p_old_param_name
718 and v.middleware_param_id=p.middleware_param_id;
719
721 l_string VARCHAR2(1000);
722 Begin
723 l_string:='Error updating CCT_Middleware_values for middleware_type='||p_old_type||' param name='||p_old_param_name;
724 Select middleware_param_id
725 into l_new_param_id
726 from cct_middleware_params p,cct_middleware_types t
727 where t.middleware_type=p_new_type
728 and p.middleware_type_id=t.middleware_type_id
729 and p.name=p_new_param_name;
730
731 for v_mvalueID in c_value LOOP
732 update cct_middleware_values
733 set middleware_param_id=l_new_param_id
734 where middleware_value_id=v_mvalueID.middleware_value_id;
735 end loop;
736 Exception
737 When no_data_found then
738 null;
739 When others then
740 raise_application_error(-20000, sqlerrm || '.' || l_string ) ;
741 END;
742
743 Procedure update_mtype_in_mware(p_middleware_id IN Number,p_old_type in VARCHAR2, p_new_type IN VARCHAR2)
744 IS
745 Cursor c_middleware
746 is
747 Select m.middleware_id
748 from cct_middlewares m,cct_middleware_types t
749 where t.middleware_type=p_old_type
750 and t.middleware_type_id=m.middleware_type_id
751 and m.middleware_id=p_middleware_id;
752
753 l_string VARCHAR2(1000);
754 l_new_type_id Number;
755 Begin
756 l_string :='Error updating CCT_MIDDLEWARE_VALUES table middleware_id='||to_char(p_middleware_id)||' p_old_type='||p_old_type;
757 Select middleware_type_id
758 into l_new_type_id
759 from cct_middleware_types
760 where middleware_type=p_new_type;
761
762 for v_middleware in c_middleware Loop
763 update cct_middlewares
764 set middleware_Type_id=l_new_type_id
765 where middleware_id=p_middleware_id;
766 end loop;
767
768 Exception
769 When no_data_found then
770 null;
771 When others then
772 raise_application_error(-20000, sqlerrm || '.' || l_string ) ;
773 END;
774
775 Procedure delete_middleware_value(p_middleware_type in VARCHAR2,p_param_name in VARCHAR2)
776 is
777 Cursor c_mvalue
778 is
779 Select v.middleware_value_id
780 from cct_middleware_values v,cct_middleware_params p,cct_middleware_types t
781 where t.middleware_type=p_middleware_type
782 and t.middleware_type_id=p.middleware_type_id
783 and p.name=p_param_name
784 and v.middleware_param_id=p.middleware_param_id;
785 l_string VARCHAR2(1000);
786 Begin
787 l_string:='ERROR deleting obsolete middleware values for type='||p_middleware_type||' and param='||p_param_name;
788 For v_value in c_mvalue LOOP
789 Begin
790 update cct_middleware_values
791 set f_deletedflag='D'
792 where middleware_value_id=v_value.middleware_value_id;
793 Exception
794 When no_data_found then
795 null;
796 end;
797
798 end loop;
799 Exception
800 When no_data_found then
801 null;
802 When others then
803 raise_application_error(-20000, sqlerrm || ':'||l_string ) ;
804 END;
805
806 Procedure delete_middleware_type(p_middleware_type_id in Number)
807 IS
808 Cursor c_mw
809 is
810 Select a.middleware_id
811 from cct_middlewares a,cct_middleware_types b
812 where a.middleware_type_id=b.middleware_type_id
813 and b.middleware_type_id=p_middleware_type_id;
814
815 Begin
816
817 For v_mw in c_mw
818 Loop
819 Begin
820 delete cct_middleware_values
821 where middleware_id=v_mw.middleware_id;
822 Exception
823 When others then
824 --dbms_output.put_line('error in deleting middleware values for Prospect Aspect');
825 null;
826 End;
827 Begin
828 delete jtf_rs_ResourcE_values
829 where value_type=v_mw.middleware_id;
830 Exception
831 When others then
832 --dbms_output.put_line('error in deleting resourcE_middleware values for Prospect Aspect');
833 null;
834 End;
835 Begin
836 delete cct_middlewares
837 where middleware_id=v_mw.middleware_id;
838 Exception
839 When others then
843 End Loop;
840 --dbms_output.put_line('error in deleting middleware for type Prospect Aspect');
841 null;
842 End;
844 Begin
845 delete cct_middleware_params
846 where middleware_type_id=(Select middleware_Type_id
847 from cct_middleware_types
848 where middleware_type_id=p_middleware_type_id);
849 Exception
850 When others then
851 --dbms_output.put_line('error in deleting middleware_param for Prospect Aspect');
852 null;
853 End;
854 Begin
855 delete jtf_rs_resource_params
856 where param_type=(Select middleware_Type_id
857 from cct_middleware_types
858 where middleware_type_id=p_middleware_type_id);
859 Exception
860 When others then
861 --dbms_output.put_line('error in deleting middleware_param for Prospect Aspect');
862 null;
863 End;
864 Begin
865 delete cct_middleware_types
866 where middleware_type_id=p_middleware_type_id;
867 Exception
868 When others then
869 --dbms_output.put_line('error in deleting middleware_type=Prospect Aspect');
870 null;
871 End;
872 Exception
873 When others then
874 null;
875 End;
876
877 -- Need to Update Middleware Values because of AOM bug, implimenting workaround as described
878 -- in bug 2276001
879 Procedure upgrade_ao_flg_mw_values
880 IS
881 l_mvid Number;
882 l_mval CCT_MIDDLEWARE_VALUES.VALUE%TYPE;
883 l_mpid Number;
884 l_string VARCHAR2(64) := 'upg_ao_flg_mware_values';
885 Cursor c_mvp
886 is
887 SELECT b.middleware_value_id, b.VALUE
888 FROM cct_middleware_params a, cct_middleware_values b
889 WHERE a.middleware_param_id = b.middleware_param_id
890 AND a.domain_lookup_type = 'CCT_AO_FLAG'
891 AND (b.f_deletedflag <> 'D' OR b.f_deletedflag IS NULL);
892 Begin
893 for v_mvp in c_mvp LOOP
894 --dbms_output.put_line('in loop');
895 update cct_middleware_values
896 set value= decode(v_mvp.value,'Y', 'YES', 'N','NO','' )
897 where middleware_value_id=v_mvp.middleware_value_id;
898 end loop;
899 Exception
900 when others then
901 raise_application_error(-20000, sqlerrm || '.' || l_string) ;
902 END;
903
904 PROCEDURE MODIFY_SEQUENCE_WITH_VALUE (
905 schema_name IN VARCHAR2
906 ,sequence_name IN VARCHAR2
907 ,replacement_value IN VARCHAR2
908 ) IS
909
910 sequence_not_found exception;
911 pragma exception_init(sequence_not_found, -942);
912 l_old_seq_val VARCHAR2(10);
913 l_new_seq_val VARCHAR2(10);
914 l_new_seq_stmt VARCHAR2(2000);
915 l_missing_sequence VARCHAR2(2000);
916 l_drop_Seq_stmt VARCHAR2(2000);
917 v_cursorID INTEGER;
918 v_dummy INTEGER;
919 v_old_seq_Stmt VARCHAR2(2000);
920 l_seq_val varchar2(20);
921 BEGIN
922 SAVEPOINT CCTUPGSEQ;
923
924
925 -- Getting the Current Sequence value
926 BEGIN
927 v_cursorID:=DBMS_SQL.OPEN_CURSOR;
928 v_old_seq_stmt:='SELECT '||schema_name||'.'||sequence_name||'.NEXTVAL from dual';
929 --dbms_output.put_line(v_old_seq_stmt);
930 DBMS_SQL.PARSE(v_cursorID,v_old_seq_stmt,DBMS_SQL.V7);
931 --dbms_output.put_line('after parsing');
932 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,l_seq_val,10);
933 --dbms_output.put_line('after define column');
934 v_dummy:=DBMS_SQL.EXECUTE(v_cursorID);
935 --dbms_output.put_line('after execute');
936 Loop
937 if DBMS_SQL.FETCH_ROWS(v_cursorID)=0 THEN
938 --dbms_output.put_line('exiting');
939 exit;
940 end if;
941 --dbms_output.put_line('before col valu');
942 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_seq_val);
943 --dbms_output.put_line(l_seq_val);
944 END LOOP;
945 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
946 Exception
947 When others then
948 l_missing_sequence:=sequence_name;
949 raise sequence_not_found;
950 end;
951
952 IF ( (to_number(replacement_value)) > (to_number(l_seq_val)) )THEN
953 l_seq_val := replacement_value;
954
955
956 -- dropping sequence
957 Begin
958 l_drop_seq_stmt:='DROP SEQUENCE '||schema_name||'.'||sequence_name;
959 --dbms_output.put_line(l_drop_seq_stmt);
960 EXECUTE IMMEDIATE l_drop_seq_stmt;
961 Exception
962 When others then
963 l_missing_sequence:=sequence_name;
964 raise sequence_not_found;
965 end;
966
967 --create new sequence
968 l_new_seq_stmt:='CREATE SEQUENCE '||schema_name||'.'||sequence_name||' NOCYCLE NOORDER START WITH '||l_seq_val;
969 --dbms_output.put_line(l_new_seq_stmt);
970 EXECUTE IMMEDIATE l_new_seq_stmt;
971 END IF;
972 EXCEPTION
973 WHEN sequence_not_found THEN
974 raise_application_error(-20000, 'Sequence '||l_missing_sequence||' not found') ;
975
976 WHEN OTHERS THEN
977 raise_application_error(-20000, sqlerrm || '. Could not modify sequence') ;
978 END MODIFY_SEQUENCE_WITH_VALUE;
979
983 is
980 Procedure update_agent_values(p_agent_param IN VARCHAR2,p_middleware_type_id IN VARCHAR2,p_new_agent_param_id In NUMBER)
981 IS
982 Cursor c_values(p_param_id Number)
984 Select resource_param_value_id
985 from jtf_rs_resource_values
986 where resource_param_id=p_param_id;
987 l_old_param_id Number;
988 BEGIN
989 -- check if the resource_param_id is incorrect
990 Select resource_param_id
991 into l_old_param_id
992 from jtf_rs_resource_params
993 where name=p_agent_param
994 and param_type=p_middleware_type_id
995 and resource_param_id<>p_new_agent_param_id;
996
997 --dbms_output.put_line('l_old_param_id='||to_char(l_old_param_id));
998
999 -- if there is an incorrect param then get all the resource values for that param and update it with the correct param id (from JTFRSPAR.ldt file)
1003 set resource_param_id=p_new_agent_param_id
1000 For v_values in c_values(l_old_param_id) LOOP
1001 --dbms_output.put_line('updating Resource Value id='||to_char(v_values.resource_param_value_id));
1002 Update jtf_rs_ResourcE_values
1004 where resource_param_value_id=v_values.resource_param_value_id;
1005 End Loop;
1006
1007 -- Delete the incorrect resource param
1008 delete jtf_rs_resource_params
1009 where resource_param_id=l_old_param_id;
1010 --dbms_output.put_line('deleted l_old_param_id='||to_char(l_old_param_id));
1011 Exception
1012 When no_data_found then
1013 null;
1014 When others then
1015 raise_application_error(-20000, sqlerrm || '. Could not update agent param values') ;
1016 End;
1017 Procedure increment_ikey_sequence
1018 is
1019 current_val number;
1020 target_val number:=2000;
1021 Begin
1022 Select cct_interaction_keys_s.nextval
1023 into current_val
1024 from dual;
1025
1026 while (current_val<target_val) loop
1027 Select cct_interaction_keys_s.nextval
1028 into current_val
1029 from dual;
1030 end loop;
1031 end;
1032 END cct_upgrade_pkg;
[Home] [Help]