1 PACKAGE BODY BIM_DBI_SCM_RSG_API_PVT AS
2 /*$Header: bimmvimb.pls 115.3 2004/07/29 06:44:08 arvikuma noship $*/
3
4 PROCEDURE Manage_bim_Index(p_mode VARCHAR2, p_obj_name VARCHAR2) IS
5
6 CURSOR c_obj_indexes (p_obj_name VARCHAR2,p_ind_name VARCHAR2)IS
7 SELECT index_name
8 FROM user_indexes
9 WHERE table_name = p_obj_name
10 AND index_name = nvl(p_ind_name,index_name) ;
11
12 CURSOR c_val_indexes (p_obj_name VARCHAR2,p_ind_name VARCHAR2)IS
13 SELECT index_name , column_name
14 FROM bim_all_indexes
15 WHERE table_name = p_obj_name
16 AND index_name = nvl(p_ind_name,index_name) ;
17
18 l_ind_val VARCHAR2(30);
19
20 BEGIN -- Manage_bim_Index
21
22 IF (p_mode = 'BEFORE') THEN
23
24 -- Delete the entry from bim_all_indexes if the earlier entry for index was not deleted.
25 FOR i in c_obj_indexes (p_obj_name,null) LOOP
26 FOR j in c_val_indexes (p_obj_name,i.index_name) LOOP
27 -- Delete the entry from bim_all_indexes table for the index in context
28 execute immediate ('DELETE FROM bim_all_indexes where table_name = '||''''||p_obj_name||''''||' and index_name = '||''''||j.index_name||'''');
29 END LOOP;
30 END LOOP;
31
32
33 FOR i in c_obj_indexes(p_obj_name,null) LOOP
34 INSERT INTO bim_all_indexes (INDEX_NAME,TABLE_NAME,COLUMN_NAME)
35 VALUES (i.index_name,p_obj_name,REPLACE(DBMS_METADATA.GET_DDL('INDEX',i.index_name),'COMPUTE STATISTICS',NULL)||' NOLOGGING PARALLEL'); -- Log the index ddl syntax
36 execute immediate ('drop index '||i.index_name); -- drop the index
37 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('MetaData Information Recorded and Index Dropped for Index :'||i.index_name);
38 END LOOP;
39
40 ELSIF (p_mode = 'AFTER') THEN
41 BEGIN -- Re-create indexes
42 FOR i in c_val_indexes (p_obj_name,null) LOOP
43
44 -- Check for existance of the index
45 OPEN c_obj_indexes(p_obj_name,i.index_name);
46 IF c_obj_indexes%FOUND THEN
47 FETCH c_obj_indexes INTO l_ind_val;
48 ELSE
49 l_ind_val := NULL;
50 END IF;
51 CLOSE c_obj_indexes;
52
53 -- Create index if is not existing in the DB
54 IF l_ind_val IS NULL THEN
55 execute immediate (i.column_name);
56 execute immediate ('ALTER INDEX '||i.index_name||' LOGGING NOPARALLEL');
57 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Index '||i.index_name||' created on '||p_obj_name);
58 END IF;
59
60 -- Delete the entry from bim_all_indexes table for the index in context
61 execute immediate ('DELETE FROM bim_all_indexes where table_name = '||''''||p_obj_name||''''||' and index_name = '||''''||i.index_name||'''');
62 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('MetaData Information Deleted for Index -'||i.index_name);
63
64 END LOOP;
65
66 END; -- Re-create indexes
67
68 END IF;
69
70 COMMIT;
71
72 EXCEPTION
73 WHEN OTHERS
74 THEN
75 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Error for Object'|| p_obj_name ||' - '||sqlcode||'-'||sqlerrm);
76 RAISE;
77 END Manage_bim_Index;
78
79 PROCEDURE bim_Custom_Api(p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL) IS
80
81 l_api_type VARCHAR2(300);
82 l_mode VARCHAR2(300);
83 l_obj_name VARCHAR2(300);
84
85 CURSOR c_chk_index
86 IS
87 SELECT substr(version,1,1) ver
88 FROM v$instance;
89
90 BEGIN
91
92 -- BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Inside BIM Custom API ');
93 /* Checking Version of the database */
94 For l_rec in c_chk_index
95 LOOP
96 /* If Version is 9i then do then index management else do nothing */
97 IF l_rec.ver = 9 THEN
98 /* IF mode is null it means that this is the incremental mode so do no do anything and exit else do the Manage.*/
99 /* Conform to Standard 2. Retrieving Parameters API_TYPE, MODE, OBJECT_NAM, OBJECT_TYPE */
100 l_mode := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode);
101 l_api_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type);
102 l_obj_name := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name);
103
104 /* IF mode is null it means that this is the incremental mode so do no do anything and exit else do the Manage.*/
105 IF l_mode is null THEN
106 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Materialized View '|| l_obj_name || ' is running in Incremental Mode so no index management operations conducted. ');
107 ELSE
108 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Value for Parameter '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type|| ': '|| l_api_type);
109 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Value for Parameter '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode|| ': '|| l_mode);
110 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Value for Parameter '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name|| ': '|| l_obj_name);
111
112 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Start Of Process');
113
114 /** Performing Custom Actions based on the API type and calling mode**/
115 IF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Log_Mgt) THEN
116 NULL; --MANAGE_LOG(l_mode, l_obj_name);
117 ELSIF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Index_Mgt) THEN
118 Manage_bim_Index(l_mode, l_obj_name);
119 ELSIF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Threshold) THEN
120 NULL; --MANAGE_MV_THRESHOLD(L_MODE, L_OBJ_NAME);
121 END IF;
122 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('End of Process');
123 END IF;
124 COMMIT;
125 ELSE
126 NULL;
127 END IF;
128 END LOOP;
129
130 /* Conform to Standard 3. Setting Complete Status and Message */
131 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status, BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Success);
132 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, 'Succeeded');
133
134
135
136 EXCEPTION WHEN OTHERS THEN
137 /* Conform to Standard 6. Error Handling */
138 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status,BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Failure);
139 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, sqlerrm);
140
141 END bim_Custom_Api;
142
143 END;
[Home] [Help]