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;