1 PACKAGE BODY BIS_BIA_RSG_CUSTOM_API_MGMNT AS
2 /*$Header: BISCAPIB.pls 120.1 2005/07/12 16:37:48 tiwang noship $*/
3
4 PROCEDURE DEBUG(
5 P_TEXT VARCHAR2
6 , P_IDENT NUMBER DEFAULT 3)
7 IS
8 BEGIN
9 BIS_COLLECTION_UTILITIES.debug(P_TEXT, P_IDENT);
10 --DBMS_OUTPUT.PUT_LINE(P_TEXT);
11 END;
12
13 PROCEDURE LOG(
14 P_TEXT VARCHAR2 )
15 IS
16 BEGIN
17 BIS_COLLECTION_UTILITIES.put_line(' ' || P_TEXT);
18 --DBMS_OUTPUT.PUT_LINE(P_TEXT);
19 END;
20
21
22 PROCEDURE OUTPUT_PARAM (
23 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL
24 ) IS
25 l_name varchar2(32767);
26 l_value varchar2(32767);
27 BEGIN
28 FOR i IN 1..p_parameter_tbl.count LOOP
29 l_name := p_parameter_tbl(i).parameter_name;
30 l_value := p_parameter_tbl(i).parameter_value;
31 DEBUG( '( name, value) = (' || l_name ||', ' || l_value ||')' );
32 END LOOP;
33 END;
34
35
36 PROCEDURE ADD_PARAM (
37 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL,
38 p_param_name IN VARCHAR2,
39 p_param_value IN VARCHAR2
40 ) IS
41 l_parameter_rec BIS_BIA_RSG_PARAMETER_REC := BIS_BIA_RSG_PARAMETER_REC(TO_CHAR(null),TO_CHAR(null));
42 BEGIN
43 l_parameter_rec.parameter_name := p_param_name;
44 l_parameter_rec.parameter_value := p_param_value;
45 p_parameter_tbl.extend;
46 p_parameter_tbl(p_parameter_tbl.LAST) := l_parameter_rec;
47
48 DEBUG('Added Parameter ('|| p_param_name ||', ' || p_param_value || ')' );
49
50 END;
51
52 FUNCTION GET_PARAM (
53 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL,
54 p_param_name IN VARCHAR2
55 ) RETURN VARCHAR2
56 IS
57 l_parameter_rec BIS_BIA_RSG_PARAMETER_REC := BIS_BIA_RSG_PARAMETER_REC(TO_CHAR(null),TO_CHAR(null));
58 l_name varchar2(32767);
59 l_value varchar2(32767) := null;
60 BEGIN
61 FOR i IN 1..p_parameter_tbl.count LOOP
62 if p_param_name = p_parameter_tbl(i).parameter_name then
63 l_value := p_parameter_tbl(i).parameter_value;
64 LOG('Retrieved Parameter ('|| p_param_name ||', ' || l_value || ')' );
65 EXIT;
66 end if;
67 END LOOP;
68 return l_value;
69 END;
70
71
72 PROCEDURE SET_PARAM (
73 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL,
74 p_param_name IN VARCHAR2,
75 p_param_value IN VARCHAR2
76 ) IS
77 l_parameter_rec BIS_BIA_RSG_PARAMETER_REC := BIS_BIA_RSG_PARAMETER_REC(TO_CHAR(null),TO_CHAR(null));
78 l_name varchar2(32767);
79 l_value varchar2(32767);
80 BEGIN
81 FOR i IN 1..p_parameter_tbl.count LOOP
82 l_name := p_parameter_tbl(i).parameter_name;
83 l_value := p_parameter_tbl(i).parameter_value;
84
85 if l_name = p_param_name then
86 p_parameter_tbl(i).parameter_value := p_param_value;
87 LOG('Value of ' || l_name || ' was changed from ' || NVL(l_value, 'NULL') || ' to ' || p_parameter_tbl(i).parameter_value);
88 end if;
89
90 END LOOP;
91 END;
92
93
94 PROCEDURE INIT_PARAMS (
95 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL,
96 P_API_TYPE IN VARCHAR2,
97 P_OBJ_NAME IN VARCHAR2,
98 P_OBJ_TYPE IN VARCHAR2,
99 P_MODE IN VARCHAR2,
100 P_MV_REF_METHOD IN VARCHAR2
101 ) IS
102 l_parameter_rec BIS_BIA_RSG_PARAMETER_REC := BIS_BIA_RSG_PARAMETER_REC(TO_CHAR(null),TO_CHAR(null));
103 BEGIN
104 /*
105 'MV_LOG_MGT'-----Drop and create MV log
106 'MV_INDEX_MGT'---Drop and create MV index
107 'MV_THRESHOLD'---Using threshold at runtime to decide the MV refresh method
108 This parameter value will be passed in the custom API
109 */
110 ADD_PARAM(p_parameter_tbl, PARA_API_TYPE, P_API_TYPE );
111 /*
112 'BEFORE'-----Calling the API before the MV or table refresh
113 'AFTER'---Calling the API after the MV or table refresh
114 This parameter value will be passed in the custom API
115 */
116 ADD_PARAM(p_parameter_tbl, PARA_MODE, P_MODE );
117 /*
118 The table or MV name
119 This parameter value will be passed in the custom API
120 */
121 ADD_PARAM(p_parameter_tbl, PARA_OBJECT_NAME, P_OBJ_NAME ) ; --'BIS_TEST_TABLE' );
122 /*
123 'TABLE' or 'MV'
124 This parameter value will be passed in the custom API
125 */
126 ADD_PARAM(p_parameter_tbl, PARA_OBJECT_TYPE, P_OBJ_TYPE) ; --'TABLE' );
127 /*
128 This is an in/out parameter. ---modified for enhancement 4423644
129 The custom API can code logic based on this value OR set value for it.
130 After checking the threshold, if the MV should be complete refreshed,
131 then the parameter value is 'COMPLETE', otherwise the value is 'FAST'
132 */
133 ADD_PARAM(p_parameter_tbl, PARA_MV_REFRESH_METHOD, P_MV_REF_METHOD);
134 /*
135 This is an out parameter.
136 The custom API will set value for it based on if the process is successful or not
137 It has value
138 0---success
139 1-failure
140 null---the logic has not been implemented
141 */
142 ADD_PARAM(p_parameter_tbl, PARA_COMPLETE_STATUS, TO_CHAR(null) );
143 /*
144 This is an out parameter.
145 The custom API will set value for it if the above status has value 1---failure
146 */
147 ADD_PARAM(p_parameter_tbl, PARA_MESSAGE, TO_CHAR(null) );
148
149 END;
150
151 PROCEDURE INVOKE_API_DYNAMICALLY (
152 p_parameter_tbl IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL,
153 P_API IN VARCHAR2
154 ) IS
155 l_dynamic_sql varchar2(32767);
156 BEGIN
157 l_dynamic_sql := 'BEGIN '|| P_API ||' (:1); END;';
158 LOG('Executing ' || l_dynamic_sql);
159 LOG('************entering '||P_API||'*********************************');
160 execute immediate l_dynamic_sql using IN OUT p_parameter_tbl ;
161 LOG('************end of '||P_API||'*********************************');
162
163 EXCEPTION WHEN OTHERS THEN
164 LOG('CUSTOM API Error!!!!!');
165 LOG(sqlerrm);
166 RAISE;
167 END;
168
169 PROCEDURE INVOKE_CUSTOM_API (
170 RTNBUF IN OUT NOCOPY VARCHAR2,
171 RETCODE OUT NOCOPY VARCHAR2,
172 P_API IN VARCHAR2,
173 P_API_TYPE IN VARCHAR2,
174 P_OBJ_NAME IN VARCHAR2,
175 P_OBJ_TYPE IN VARCHAR2,
176 P_MODE IN VARCHAR2
177 ) IS
178 l_parameter_tbl BIS_BIA_RSG_PARAMETER_TBL := BIS_BIA_RSG_PARAMETER_TBL();
179 l_complete_status VARCHAR2(32767);
180 l_mv_refresh_method VARCHAR2(32767);
181 l_message VARCHAR2(32767);
182 l_api VARCHAR2(32767);
183 BEGIN
184 l_api := P_API;
185
186 if (l_api is null) then
187 select custom_api into l_api
188 from bis_obj_properties
189 where object_name = P_OBJ_NAME
190 and object_type = P_OBJ_TYPE;
191 end if;
192
193 IF (BIS_COLLECTION_UTILITIES.g_object_name is NULL) THEN
194 IF (Not BIS_COLLECTION_UTILITIES.setup(l_API)) THEN
195 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
196 return;
197 END IF;
198 END IF;
199
200 INIT_PARAMS(l_parameter_tbl, P_API_TYPE, P_OBJ_NAME, P_OBJ_TYPE, P_MODE,RTNBUF);
201 if (l_api is null) then
202 LOG('no custime API defined in RSG for ' || P_OBJ_NAME );
203 return;
204 end if;
205
206 ---LOG('Before calling ' || l_api );
207 INVOKE_API_DYNAMICALLY(l_parameter_tbl, l_api);
208 --- LOG('After calling ' || l_api );
209 OUTPUT_PARAM(l_parameter_tbl);
210
211 l_complete_status := GET_PARAM(l_parameter_tbl, PARA_COMPLETE_STATUS);
212 l_message := GET_PARAM(l_parameter_tbl, PARA_MESSAGE);
213 l_mv_refresh_method := GET_PARAM(l_parameter_tbl, PARA_MV_REFRESH_METHOD);
214
215 RTNBUF := l_mv_refresh_method;
216 RETCODE := l_complete_status;
217
218 IF ( l_complete_status is NULL ) THEN
219 LOG( l_api || ' did not implement ' || P_API_TYPE || ', ' || P_MODE );
220 ELSIF ( l_complete_status = STATUS_SUCCESS ) THEN
221 LOG( l_api || ' succeeded running, ' || P_API_TYPE || ', ' || P_MODE );
222 ELSIF ( l_complete_status = STATUS_FAILURE ) THEN
223 LOG( l_api || ' failed running, ' || P_API_TYPE || ', ' || P_MODE );
224 RTNBUF := l_message;
225 RAISE_APPLICATION_ERROR(-20000, l_message);
226 END IF;
227
228 --- LOG('Completed INVOKE_CUSTOME_API ' || P_API );
229 EXCEPTION WHEN OTHERS THEN
230 RTNBUF := sqlerrm;
231 RETCODE := sqlcode;
232 RAISE;
233 END INVOKE_CUSTOM_API;
234
235
236
237 END BIS_BIA_RSG_CUSTOM_API_MGMNT; -- Package Body BIS_BIA_RSG_CUSTOM_API_MGMNT
[Home] [Help]