[Home] [Help]
PACKAGE BODY: APPS.CCT_ALTER_WEB_SCHEMA_PKG
Source
1 PACKAGE BODY cct_alter_web_schema_pkg AS
2 /* $Header: cctupawb.pls 120.0 2005/06/02 09:38:20 appldev noship $ */
3 PROCEDURE modify_column_delault (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 || ' MODIFY ( ' || col_name || ' DEFAULT 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 modify_column_delault;
32
33 PROCEDURE update_null_object_versions IS
34 BEGIN
35
36 SAVEPOINT CCTUPGCOL;
37
38 UPDATE cct_middlewares
39 SET object_version_number = 0
40 where object_version_number IS NULL;
41
42 UPDATE CCT_CLASSIFICATIONS
43 SET object_version_number = 0
44 where object_version_number IS NULL;
45
46 UPDATE CCT_CLASSIFICATION_RULES
47 SET object_version_number = 0
48 where object_version_number IS NULL;
49
50 UPDATE CCT_IVR_MAPS
51 SET object_version_number = 0
52 where object_version_number IS NULL;
53
54 UPDATE CCT_LINES
55 SET object_version_number = 0
56 where object_version_number IS NULL;
57
58 UPDATE CCT_MIDDLEWARE_PARAMS
59 SET object_version_number = 0
60 where object_version_number IS NULL;
61
62 UPDATE CCT_MIDDLEWARE_VALUES
63 SET object_version_number = 0
64 where object_version_number IS NULL;
65
66 UPDATE CCT_TELESET_TYPES
67 SET object_version_number = 0
68 where object_version_number IS NULL;
69
70 UPDATE CCT_STATIC_ROUTES
71 SET object_version_number = 0
72 where object_version_number IS NULL;
73
74 UPDATE CCT_ROUTE_PARAMS
75 SET object_version_number = 0
76 where object_version_number IS NULL;
77
78 UPDATE CCT_ROUTES
79 SET object_version_number = 0
80 where object_version_number IS NULL;
81
82 UPDATE CCT_TELESETS
83 SET object_version_number = 0
84 where object_version_number IS NULL;
85 EXCEPTION
86
87 WHEN OTHERS THEN
88 rollback;
89 raise_application_error(-20000, sqlerrm || '.UPDATE COL VALUE, Could not add column') ;
90
91
92 END;
93
94 PROCEDURE update_route_param_operator(schema_name IN VARCHAR2) is
95 l_str varchar2(200):= 'ALTER TABLE ' || schema_name || '.' ||'CCT_ROUTE_PARAMS MODIFY ( OPERATION DEFAULT '''||'='||''' )';
96 BEGIN
97
98 SAVEPOINT CCTUPG1;
99
100 EXECUTE IMMEDIATE 'LOCK TABLE ' || schema_name || '.' ||'CCT_ROUTE_PARAMS IN EXCLUSIVE MODE';
101 EXECUTE IMMEDIATE l_str;
102 COMMIT ; -- used to release the table lock.
103
104 UPDATE CCT_ROUTE_PARAMS
105 SET operation = '='
106 where operation IS NULL;
107
108 COMMIT ; -- used to release the table lock.
109
110 EXCEPTION
111
112 WHEN OTHERS THEN
113 rollback;
114 raise_application_error(-20000, sqlerrm || '.UPDATE_ROUTE_PARAM_OPERATOR COL VALUE, Could not modify') ;
115
116 END update_route_param_operator;
117
118 END cct_alter_web_schema_pkg;