[Home] [Help]
PACKAGE BODY: APPS.IEO_SVR_UTIL_PVT
Source
1 PACKAGE BODY IEO_SVR_UTIL_PVT AS
2 /* $Header: IEOSVUVB.pls 115.25 2004/04/27 00:56:03 edwang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEO_SVR_UTIL_PVT';
5
6 -- Sub-Program Units
7 -- Entry point routine for apps to retrieve The Load Specs for a specific type Of Server:
8 PROCEDURE GET_SVR_TYPE_LOAD_INFO
9 (P_SERVER_TYPE_UUID IN VARCHAR2
10 )
11 AS
12 ty_major_load_max IEO_SVR_TYPES_B.MAX_MAJOR_LOAD_FACTOR%TYPE;
13 ty_minor_load_max IEO_SVR_TYPES_B.MAX_MINOR_LOAD_FACTOR%TYPE;
14 l_refresh_rate_secs NUMBER(5);
15
16 BEGIN
17
18 IF ((P_SERVER_TYPE_UUID IS NULL)) THEN
19 raise_application_error
20 (-20000
21 ,'P_SERVER_TYPE_UUID cannot be NULL.'
22 ,TRUE );
23 END IF;
24
25 SELECT
26 -- DISTINCT
27 type_table.MAX_MAJOR_LOAD_FACTOR,
28 type_table.MAX_MINOR_LOAD_FACTOR,
29 (type_table.RT_REFRESH_RATE * 60)
30 INTO
31 ty_major_load_max,
32 ty_minor_load_max,
33 l_refresh_rate_secs
34 FROM
35 IEO_SVR_TYPES_B type_table
36 WHERE
37 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
38 (ROWNUM <= 1);
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 RAISE;
43
44 END GET_SVR_TYPE_LOAD_INFO;
45
46
47 -- Recursive routine to get all the nested groups within a given group
48 PROCEDURE GET_ALL_SUBGROUPS
49 (P_GROUP_ID IN NUMBER
50 ,X_GROUP_ID_LIST OUT NOCOPY SYSTEM.IEO_SVR_ID_ARRAY
51 )
52 AS
53 CURSOR c1 is
54 SELECT
55 SERVER_GROUP_ID
56 FROM
57 IEO_SVR_GROUPS
58 WHERE
59 GROUP_GROUP_ID = P_GROUP_ID;
60
61 counter NUMBER := 1;
62 sub_counter NUMBER := 1;
63 v_all_group_ids SYSTEM.IEO_SVR_ID_ARRAY:= SYSTEM.IEO_SVR_ID_ARRAY(
64 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
65 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
66 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
67 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
68 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1);
69 v_tmp_group_ids SYSTEM.IEO_SVR_ID_ARRAY:= SYSTEM.IEO_SVR_ID_ARRAY(
70 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
71 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
72 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
73 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
74 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1);
75
76 BEGIN
77
78 FOR c1_rec IN c1
79 LOOP
80 v_all_group_ids(counter) := c1_rec.server_group_id;
81
82 IF (c1%NOTFOUND) THEN
83 EXIT;
84 END IF;
85
86 GET_ALL_SUBGROUPS(v_all_group_ids(c1%ROWCOUNT), v_tmp_group_ids);
87 counter := counter + 1;
88
89 WHILE (v_tmp_group_ids IS NOT NULL AND v_tmp_group_ids(sub_counter) >= 0)
90 LOOP
91 v_all_group_ids(counter) := v_tmp_group_ids(sub_counter);
92 counter := counter + 1;
93 sub_counter := sub_counter + 1;
94 END LOOP; -- Inner WHILE
95 END LOOP; -- Outer FOR
96
97 X_GROUP_ID_LIST := v_all_group_ids;
98
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 X_GROUP_ID_LIST := NULL;
102 WHEN OTHERS THEN
103 RAISE;
104
105 END GET_ALL_SUBGROUPS;
106
107
108 -- Internal utility function to retrieve all the Group IDs A Given server is eligible
109 -- to use. NOTE: Groups may be subsets of other groups therefore a
110 -- server may connect to any server within his group AND ALL subsets.
111 PROCEDURE LOCATE_ALL_GROUPS
112 (P_SERVER_ID_LOOKING IN NUMBER
113 ,X_GROUP_ID_LIST OUT NOCOPY SYSTEM.IEO_SVR_ID_ARRAY
114 )
115 AS
116 counter NUMBER := 1;
117 sub_counter NUMBER := 1;
118 v_all_group_ids SYSTEM.IEO_SVR_ID_ARRAY:= SYSTEM.IEO_SVR_ID_ARRAY(
119 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
120 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
121 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
122 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
123 -1, -1, -1, -1, -1, -1, -1, -1, -1, -1);
124
125 v_tmp_group_ids SYSTEM.IEO_SVR_ID_ARRAY;
126
127 BEGIN
128
129 -- First get the group our server is using:
130 SELECT
131 DISTINCT
132 NVL( svr_table.USING_SVR_GROUP_ID, svr_table.MEMBER_SVR_GROUP_ID )
133 INTO
134 v_all_group_ids(counter)
135 FROM
136 IEO_SVR_SERVERS svr_table
137 WHERE
138 (svr_table.SERVER_ID = P_SERVER_ID_LOOKING) AND
139 (ROWNUM <= 1);
140
141 -- Now get all sub groups our server may access:
142 GET_ALL_SUBGROUPS(v_all_group_ids(counter), v_tmp_group_ids);
143
144 counter := counter + 1;
145
146 WHILE (v_tmp_group_ids IS NOT NULL AND v_tmp_group_ids(sub_counter) >= 0)
147 LOOP
148 v_all_group_ids(counter) := v_tmp_group_ids(sub_counter);
149 counter := counter + 1;
150 sub_counter := sub_counter + 1;
151 END LOOP;
152
153 X_GROUP_ID_LIST := v_all_group_ids;
154
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 X_GROUP_ID_LIST := NULL;
158
159 WHEN OTHERS THEN
160 RAISE;
161
162 END LOCATE_ALL_GROUPS;
163
164 -- Internal utility function to get connection information for ALL servers
165 -- (of a specified type) within A single given group.
166 PROCEDURE GET_ALL_SERVERS_IN_GROUP
167 (P_GROUP_ID IN NUMBER
168 ,P_SERVER_TYPE_UUID IN VARCHAR2
169 ,P_WIRE_PROTOCOL IN VARCHAR2
170 ,P_COMP_DEF_NAME IN VARCHAR2
171 ,P_COMP_DEF_VERSION IN NUMBER
172 ,P_COMP_DEF_IMPL IN VARCHAR2
173 ,P_COMP_NAME IN VARCHAR2
174 ,X_SVR_INFO_LIST OUT NOCOPY SYSTEM.IEO_SVR_INFO_ARRAY
175 )
176 AS
177 CURSOR c1 IS
178 SELECT
179 svr_table.SERVER_ID,
180 svr_table.SERVER_NAME,
181 svr_table.USER_ADDRESS,
182 svr_table.DNS_NAME,
183 svr_table.IP_ADDRESS,
184 prot_table.PORT,
185 comp_table.COMP_NAME,
186 rt_table.STATUS,
187 rt_table.MAJOR_LOAD_FACTOR,
188 rt_table.MINOR_LOAD_FACTOR,
189 rt_table.LAST_UPDATE_DATE
190 FROM
191 IEO_SVR_TYPES_B type_table,
192 IEO_SVR_SERVERS svr_table,
193 IEO_SVR_COMP_DEFS cdef_table,
194 IEO_SVR_COMPS comp_table,
195 IEO_SVR_PROTOCOL_MAP prot_table,
196 IEO_SVR_RT_INFO rt_table
197 WHERE
198 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
199 (type_table.TYPE_ID = svr_table.TYPE_ID) AND
200 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
201 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
202 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
203 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
204 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
205 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
206 (prot_table.COMP_ID = comp_table.COMP_ID) AND
207 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
208 -- NOTE: Outer on the RT INFO so servers which haven't updated
209 -- this are NOT excluded
210 (svr_table.SERVER_ID = rt_table.SERVER_ID (+)
211 ) ;
212
213 v_svr_info_list SYSTEM.IEO_SVR_INFO_ARRAY := SYSTEM.IEO_SVR_INFO_ARRAY
214 (
215 SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
216 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
217 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
218 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
219 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
220 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
221 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
222 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
223 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
224 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
225 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
226 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
227 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
228 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
229 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
230 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
231 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
232 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
233 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
234 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
235 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
236 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
237 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
238 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
239 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
240 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
241 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
242 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
243 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
244 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
245 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
246 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
247 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
248 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
249 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
250 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
251 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
252 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
253 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
254 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
255 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
256 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
257 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
258 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
259 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
260 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
261 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
262 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
263 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
264 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
265 );
266 b_check_name BOOLEAN := TRUE;
267
268 BEGIN
269
270 IF (P_COMP_NAME IS NULL)
271 THEN
272 b_check_name := FALSE;
273 END IF;
274
275 FOR c1_rec IN c1
276 LOOP
277 <<begin_loop>>
278
279 IF (c1%NOTFOUND) THEN
280 EXIT;
281 END IF;
282
283 IF (b_check_name)
284 THEN
285 IF (c1_rec.COMP_NAME <> P_COMP_NAME)
286 THEN
287 GOTO begin_loop;
288 END IF;
289 END IF;
290
291 v_svr_info_list(c1%ROWCOUNT).SERVER_NAME := c1_rec.SERVER_NAME;
292 v_svr_info_list(c1%ROWCOUNT).SERVER_ID := c1_rec.SERVER_ID;
293 v_svr_info_list(c1%ROWCOUNT).USER_ADDR := c1_rec.USER_ADDRESS;
294 v_svr_info_list(c1%ROWCOUNT).DNS_NAME := c1_rec.DNS_NAME;
295 v_svr_info_list(c1%ROWCOUNT).IP_ADDR := c1_rec.IP_ADDRESS;
296 v_svr_info_list(c1%ROWCOUNT).PORT := c1_rec.PORT;
297 v_svr_info_list(c1%ROWCOUNT).COMP_NAME := c1_rec.COMP_NAME;
298 v_svr_info_list(c1%ROWCOUNT).STATUS := c1_rec.STATUS;
299 v_svr_info_list(c1%ROWCOUNT).MAJOR_LOAD := c1_rec.MAJOR_LOAD_FACTOR;
300 v_svr_info_list(c1%ROWCOUNT).MINOR_LOAD := c1_rec.MINOR_LOAD_FACTOR;
301 v_svr_info_list(c1%ROWCOUNT).LAST_UPDATE := c1_rec.LAST_UPDATE_DATE;
302 END LOOP;
303
304 X_SVR_INFO_LIST := v_svr_info_list;
305
306 EXCEPTION
307 WHEN NO_DATA_FOUND THEN
308 X_SVR_INFO_LIST := NULL;
309
310 WHEN OTHERS THEN
311 RAISE;
312
313 END GET_ALL_SERVERS_IN_GROUP;
314
315 -- Internal utility function to get connection information for ALL servers
316 -- (of a specified type) within A single given group.
317 PROCEDURE GET_ALL_SVRS_IN_GROUP_NST
318 (P_GROUP_ID IN NUMBER
319 ,P_SERVER_TYPE_UUID IN VARCHAR2
320 ,P_WIRE_PROTOCOL IN VARCHAR2
321 ,P_COMP_DEF_NAME IN VARCHAR2
322 ,P_COMP_DEF_VERSION IN NUMBER
323 ,P_COMP_DEF_IMPL IN VARCHAR2
324 ,P_COMP_NAME IN VARCHAR2
325 ,X_SVR_INFO_LIST OUT NOCOPY SYSTEM.IEO_SVR_INFO_NST
326 )
327 AS
328 CURSOR c1 IS
329 SELECT
330 svr_table.SERVER_ID,
331 svr_table.SERVER_NAME,
332 svr_table.USER_ADDRESS,
333 svr_table.DNS_NAME,
334 svr_table.IP_ADDRESS,
335 prot_table.PORT,
336 comp_table.COMP_NAME,
337 rt_table.STATUS,
338 rt_table.MAJOR_LOAD_FACTOR,
339 rt_table.MINOR_LOAD_FACTOR,
340 rt_table.LAST_UPDATE_DATE
341 FROM
342 IEO_SVR_TYPES_B type_table,
343 IEO_SVR_SERVERS svr_table,
344 IEO_SVR_COMP_DEFS cdef_table,
345 IEO_SVR_COMPS comp_table,
346 IEO_SVR_PROTOCOL_MAP prot_table,
347 IEO_SVR_RT_INFO rt_table
348 WHERE
349 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
350 (type_table.TYPE_ID = svr_table.TYPE_ID) AND
351 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
352 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
356 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
353 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
354 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
355 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
357 (prot_table.COMP_ID = comp_table.COMP_ID) AND
358 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
359 -- NOTE: Outer on the RT INFO so servers which haven't updated
360 -- this are NOT excluded
361 (svr_table.SERVER_ID = rt_table.SERVER_ID (+)
362 ) ;
363
364 v_svr_info_list SYSTEM.IEO_SVR_INFO_ARRAY := SYSTEM.IEO_SVR_INFO_ARRAY
365 (
366 SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
367 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
368 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
369 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
370 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
371 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
372 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
373 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
374 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
375 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
376 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
377 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
378 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
379 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
380 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
381 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
382 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
383 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
384 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
385 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
386 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
387 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
388 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
389 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
390 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
391 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
392 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
393 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
394 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
395 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
396 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
397 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
398 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
399 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
400 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
401 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
402 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
403 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
404 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
405 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
406 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
407 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
408 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
409 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
410 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
411 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
412 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
413 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
414 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
418 b_check_name BOOLEAN := TRUE;
415 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
416 );
417
419 b_server_found BOOLEAN := FALSE;
420
421 BEGIN
422
423 X_SVR_INFO_LIST := SYSTEM.IEO_SVR_INFO_NST();
424
425 IF (P_COMP_NAME IS NULL)
426 THEN
427 b_check_name := FALSE;
428 END IF;
429
430 FOR c1_rec IN c1
431 LOOP
432 <<begin_loop>>
433
434 IF (c1%NOTFOUND) THEN
435 EXIT;
436 END IF;
437
438 IF (b_check_name)
439 THEN
440 IF (c1_rec.COMP_NAME <> P_COMP_NAME)
441 THEN
442 GOTO begin_loop;
443 END IF;
444 END IF;
445
446 --dude!!! I am using a combination of VARRAY and NST.. because
447 --VARRAYS allows assigning to individual object attributes i use it..
448
449 --dbms_output.put_line( 'server found :' || c1_rec.SERVER_NAME );
450 v_svr_info_list(c1%ROWCOUNT).SERVER_NAME := c1_rec.SERVER_NAME;
451 v_svr_info_list(c1%ROWCOUNT).SERVER_ID := c1_rec.SERVER_ID;
452 v_svr_info_list(c1%ROWCOUNT).USER_ADDR := c1_rec.USER_ADDRESS;
453 v_svr_info_list(c1%ROWCOUNT).DNS_NAME := c1_rec.DNS_NAME;
454 v_svr_info_list(c1%ROWCOUNT).IP_ADDR := c1_rec.IP_ADDRESS;
455 v_svr_info_list(c1%ROWCOUNT).PORT := c1_rec.PORT;
456 v_svr_info_list(c1%ROWCOUNT).COMP_NAME := c1_rec.COMP_NAME;
457 v_svr_info_list(c1%ROWCOUNT).STATUS := c1_rec.STATUS;
458 v_svr_info_list(c1%ROWCOUNT).MAJOR_LOAD := c1_rec.MAJOR_LOAD_FACTOR;
459 v_svr_info_list(c1%ROWCOUNT).MINOR_LOAD := c1_rec.MINOR_LOAD_FACTOR;
460 v_svr_info_list(c1%ROWCOUNT).LAST_UPDATE := c1_rec.LAST_UPDATE_DATE;
461
462 X_SVR_INFO_LIST.EXTEND(1);
463 X_SVR_INFO_LIST( X_SVR_INFO_LIST.LAST ) := v_svr_info_list(c1%ROWCOUNT);
464 b_server_found := TRUE;
465
466 END LOOP;
467
468 IF ( b_server_found <> TRUE ) THEN
469 --dbms_output.put_line( 'server not found' );
470 X_SVR_INFO_LIST := NULL;
471 END IF;
472
473
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 X_SVR_INFO_LIST := NULL;
477
478 WHEN OTHERS THEN
479 RAISE;
480
481 END GET_ALL_SVRS_IN_GROUP_NST;
482
483 -- Main Entry Point for Apps. to get remote server connection info.
484 -- For all Servers of a specified type.
485 -- NOTE: THIS procedure ONLY supports up to a total of 49 nested groups and a TOTAL of 50
486 -- eligible servers within the nested group structure.
487 PROCEDURE GET_CONNECT_INFO_FOR_ALL_SVRS
488 (P_SERVER_ID_LOOKING IN NUMBER
489 ,P_SERVER_TYPE_UUID IN VARCHAR2
490 ,P_WIRE_PROTOCOL IN VARCHAR2
491 ,P_COMP_DEF_NAME IN VARCHAR2
492 ,P_COMP_DEF_VERSION IN NUMBER
493 ,P_COMP_DEF_IMPL IN VARCHAR2
494 ,P_COMP_NAME IN VARCHAR2
495 ,X_DB_TIME OUT NOCOPY DATE
496 ,X_SVR_COUNT OUT NOCOPY NUMBER
497 ,X_SVR_INFO_LIST OUT NOCOPY SYSTEM.IEO_SVR_INFO_ARRAY
498 )
499 AS
500 group_counter NUMBER := 1;
501 svr_counter NUMBER := 1;
502 tmp_counter NUMBER := 1;
503 v_all_svr_info_list SYSTEM.IEO_SVR_INFO_ARRAY := SYSTEM.IEO_SVR_INFO_ARRAY(
504 SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
505 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
506 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
507 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
508 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
509 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
510 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
511 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
512 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
513 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
514 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
515 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
516 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
517 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
518 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
519 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
520 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
521 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
522 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
523 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
524 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
528 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
525 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
526 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
527 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
529 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
530 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
531 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
532 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
533 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
534 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
535 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
536 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
537 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
538 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
539 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
540 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
541 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
542 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
543 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
544 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
545 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
546 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
547 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
548 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
549 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
550 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
551 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
552 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
553 ,SYSTEM.IEO_SVR_INFO_OBJ('', 0, '', '', '', 0, '', 0, NULL, 0, 0)
554 );
555 v_tmp_svr_info_list SYSTEM.IEO_SVR_INFO_ARRAY;
556 v_group_ids SYSTEM.IEO_SVR_ID_ARRAY;
557
558 BEGIN
559
560 -- First validate input:
561 IF ( (P_SERVER_ID_LOOKING IS NULL) OR
562 (P_SERVER_TYPE_UUID IS NULL) OR
563 (P_WIRE_PROTOCOL IS NULL) OR
564 (P_COMP_DEF_NAME IS NULL) OR
565 (P_COMP_DEF_VERSION IS NULL) OR
566 (P_COMP_DEF_IMPL IS NULL) )
567 THEN
568 raise_application_error
569 (-20000
570 ,'A required parameter is null' ||
571 '. (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID ||
572 ') (P_WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL ||
573 ') (P_COMP_DEF_NAME = ' || P_COMP_DEF_NAME ||
574 ') (P_COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION ||
575 ') (P_COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL ||
576 ')'
577 ,TRUE );
578 END IF;
579
580
581 -- Retrieve all the groups which are within the given server's set:
582 LOCATE_ALL_GROUPS(P_SERVER_ID_LOOKING, v_group_ids);
583
584 IF ( (v_group_ids IS NULL) OR (v_group_ids(1) <= 0) )
585 THEN
586 raise_application_error
587 (-20000
588 ,'Invalid Server ID specified: ' || P_SERVER_ID_LOOKING
589 ,TRUE );
590 END IF;
591
592 WHILE ( v_group_ids(group_counter) > 0 )
593 LOOP
594 GET_ALL_SERVERS_IN_GROUP( v_group_ids(group_counter)
595 ,P_SERVER_TYPE_UUID
596 ,P_WIRE_PROTOCOL
597 ,P_COMP_DEF_NAME
598 ,P_COMP_DEF_VERSION
599 ,P_COMP_DEF_IMPL
600 ,P_COMP_NAME
601 ,v_tmp_svr_info_list
602 );
603 group_counter := group_counter + 1;
604
605 tmp_counter := 1;
606
607 WHILE ( (v_tmp_svr_info_list IS NOT NULL) AND
608 v_tmp_svr_info_list(tmp_counter).is_valid() )
609 LOOP
610 v_all_svr_info_list(svr_counter) := v_tmp_svr_info_list(tmp_counter);
611 svr_counter := svr_counter + 1;
612 tmp_counter := tmp_counter + 1;
613 END LOOP;
614 END LOOP;
615
616 X_SVR_COUNT := svr_counter - 1;
617 IF (X_SVR_COUNT >= 0)
618 THEN
619 X_SVR_INFO_LIST := v_all_svr_info_list;
620
621 -- Get the current time:
622 SELECT sysdate
623 INTO X_DB_TIME
624 FROM dual;
625 END IF;
626
627
628 EXCEPTION
629 WHEN OTHERS THEN
630 RAISE;
631
632
633 END GET_CONNECT_INFO_FOR_ALL_SVRS;
634
635
636 -- Main Entry Point for Apps. to get remote server connection info.
640 PROCEDURE GET_CONN_INFO_FOR_ALL_SVRS_NST
637 -- For all Servers of a specified type.
638 -- NOTE: THIS procedure ONLY supports up to a total of 49 nested groups and a TOTAL of 50
639 -- eligible servers within the nested group structure.
641 (P_SERVER_ID_LOOKING IN NUMBER
642 ,P_SERVER_TYPE_UUID IN VARCHAR2
643 ,P_WIRE_PROTOCOL IN VARCHAR2
644 ,P_COMP_DEF_NAME IN VARCHAR2
645 ,P_COMP_DEF_VERSION IN NUMBER
646 ,P_COMP_DEF_IMPL IN VARCHAR2
647 ,P_COMP_NAME IN VARCHAR2
648 ,X_DB_TIME OUT NOCOPY DATE
649 ,X_SVR_COUNT OUT NOCOPY NUMBER
650 ,X_SVR_INFO_LIST OUT NOCOPY SYSTEM.IEO_SVR_INFO_NST
651 )
652 AS
653 group_counter NUMBER := 1;
654 svr_counter NUMBER := 1;
655 v_group_ids SYSTEM.IEO_SVR_ID_ARRAY;
656 v_tmp_svr_info_list SYSTEM.IEO_SVR_INFO_NST;
657
658 BEGIN
659
660 -- First validate input:
661 IF ( (P_SERVER_ID_LOOKING IS NULL) OR
662 (P_SERVER_TYPE_UUID IS NULL) OR
663 (P_WIRE_PROTOCOL IS NULL) OR
664 (P_COMP_DEF_NAME IS NULL) OR
665 (P_COMP_DEF_VERSION IS NULL) OR
666 (P_COMP_DEF_IMPL IS NULL) )
667 THEN
668 raise_application_error
669 (-20000
670 ,'A required parameter is null' ||
671 '. (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID ||
672 ') (P_WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL ||
673 ') (P_COMP_DEF_NAME = ' || P_COMP_DEF_NAME ||
674 ') (P_COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION ||
675 ') (P_COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL ||
676 ')'
677 ,TRUE );
678 END IF;
679
680
681 X_SVR_INFO_LIST := SYSTEM.IEO_SVR_INFO_NST();
682
683 -- Retrieve all the groups which are within the given server's set:
684 LOCATE_ALL_GROUPS(P_SERVER_ID_LOOKING, v_group_ids);
685
686 IF ( (v_group_ids IS NULL) OR (v_group_ids(1) <= 0) )
687 THEN
688 raise_application_error
689 (-20000
690 ,'Invalid Server ID specified: ' || P_SERVER_ID_LOOKING
691 ,TRUE );
692 END IF;
693
694
695 WHILE ( v_group_ids(group_counter) > 0 )
696 LOOP
697 --dbms_output.put_line( 'group name ' || v_group_ids(group_counter) );
698 GET_ALL_SVRS_IN_GROUP_NST( v_group_ids(group_counter)
699 ,P_SERVER_TYPE_UUID
700 ,P_WIRE_PROTOCOL
701 ,P_COMP_DEF_NAME
702 ,P_COMP_DEF_VERSION
703 ,P_COMP_DEF_IMPL
704 ,P_COMP_NAME
705 ,v_tmp_svr_info_list
706 );
707 group_counter := group_counter + 1;
708
709 if (v_tmp_svr_info_list is not null)
710 then
711 FOR i IN v_tmp_svr_info_list.FIRST..v_tmp_svr_info_list.LAST
712 LOOP
713 X_SVR_INFO_LIST.extend(1);
714 X_SVR_INFO_LIST( X_SVR_INFO_LIST.LAST ) := v_tmp_svr_info_list(i);
715 svr_counter := svr_counter + 1;
716 END LOOP;
717 END IF;
718 END LOOP;
719
720 --dbms_output.put_line( 'after main for loop' );
721 X_SVR_COUNT := svr_counter - 1;
722 if (X_SVR_COUNT = 0)
723 then
724 X_SVR_INFO_LIST := NULL;
725 end IF;
726
727 SELECT sysdate INTO X_DB_TIME FROM dual;
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 RAISE;
732
733
734 END GET_CONN_INFO_FOR_ALL_SVRS_NST;
735
736 /* Used to update real-time server information with node id. */
737 PROCEDURE UPDATE_RT_INFO_V2
738 (P_SERVER_ID IN NUMBER
739 ,P_STATUS IN NUMBER
740 ,P_NODE_ID IN NUMBER
741 ,P_MAJOR_LOAD_FACTOR IN NUMBER
742 ,P_MINOR_LOAD_FACTOR IN NUMBER
743 ,P_EXTRA IN VARCHAR2
744 )
745 AS
746 BEGIN
747
748 IF ((P_SERVER_ID IS NULL) OR (P_STATUS IS NULL)) THEN
749 raise_application_error
750 (-20000
751 ,'P_SERVER_ID and P_STATUS cannot be NULL. (P_SERVER_ID = ' ||
752 P_SERVER_ID || ') (P_STATUS = ' || P_STATUS || ')'
753 ,TRUE );
754 END IF;
755
756
757 SAVEPOINT start_update;
758
759
760 UPDATE IEO_SVR_RT_INFO
761 SET
762 STATUS = P_STATUS,
763 NODE_ID = P_NODE_ID,
764 MAJOR_LOAD_FACTOR = P_MAJOR_LOAD_FACTOR,
765 MINOR_LOAD_FACTOR = P_MINOR_LOAD_FACTOR,
766 EXTRA = P_EXTRA,
767 LAST_UPDATE_DATE = SYSDATE
768 WHERE
769 SERVER_ID = P_SERVER_ID;
770
771
772 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
773
774 INSERT INTO IEO_SVR_RT_INFO
775 ( SERVER_ID,
776 STATUS,
777 NODE_ID,
778 MAJOR_LOAD_FACTOR,
779 MINOR_LOAD_FACTOR,
780 EXTRA,
781 LAST_UPDATE_DATE )
782 VALUES (
783 P_SERVER_ID,
784 P_STATUS,
785 P_NODE_ID,
786 P_MAJOR_LOAD_FACTOR,
787 P_MINOR_LOAD_FACTOR,
788 P_EXTRA,
789 SYSDATE );
790
791 END IF;
792
793 COMMIT;
794
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 ROLLBACK TO start_update;
799 RAISE;
803
800
801 END UPDATE_RT_INFO_V2;
802
804
805
806
807 /* Used to update real-time server information when load information is not specified. */
808
809 PROCEDURE UPDATE_RT_INFO_NO_LOAD_V2
810 (P_SERVER_ID IN NUMBER
811 ,P_STATUS IN NUMBER
812 ,P_NODE_ID IN NUMBER
813 ,P_EXTRA IN VARCHAR2
814 )
815 AS
816 BEGIN
817
818 IF ((P_SERVER_ID IS NULL) OR (P_STATUS IS NULL)) THEN
819 raise_application_error
820 (-20000
821 ,'P_SERVER_ID and P_STATUS cannot be NULL. (P_SERVER_ID = ' ||
822 P_SERVER_ID || ') (P_STATUS = ' || P_STATUS || ')'
823 ,TRUE );
824 END IF;
825
826
827 SAVEPOINT start_update;
828
829
830 UPDATE IEO_SVR_RT_INFO
831 SET
832 STATUS = P_STATUS,
833 NODE_ID = P_NODE_ID,
834 EXTRA = P_EXTRA,
835 LAST_UPDATE_DATE = SYSDATE
836 WHERE
837 SERVER_ID = P_SERVER_ID;
838
839
840 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
841
842 INSERT INTO IEO_SVR_RT_INFO
843 ( SERVER_ID,
844 STATUS,
845 NODE_ID,
846 EXTRA,
847 LAST_UPDATE_DATE )
848 VALUES (
849 P_SERVER_ID,
850 P_STATUS,
851 P_NODE_ID,
852 P_EXTRA,
853 SYSDATE );
854
855 END IF;
856
857 COMMIT;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 ROLLBACK TO start_update;
862 RAISE;
863
864 END UPDATE_RT_INFO_NO_LOAD_V2;
865
866
867 /* Used to update real-time server information. */
868 PROCEDURE UPDATE_RT_INFO
869 (P_SERVER_ID IN NUMBER
870 ,P_STATUS IN NUMBER
871 ,P_MAJOR_LOAD_FACTOR IN NUMBER
872 ,P_MINOR_LOAD_FACTOR IN NUMBER
873 ,P_EXTRA IN VARCHAR2
874 )
875 AS
876 BEGIN
877
878 IF ((P_SERVER_ID IS NULL) OR (P_STATUS IS NULL)) THEN
879 raise_application_error
880 (-20000
881 ,'P_SERVER_ID and P_STATUS cannot be NULL. (P_SERVER_ID = ' ||
882 P_SERVER_ID || ') (P_STATUS = ' || P_STATUS || ')'
883 ,TRUE );
884 END IF;
885
886
887 SAVEPOINT start_update;
888
889
890 UPDATE IEO_SVR_RT_INFO
891 SET
892 STATUS = P_STATUS,
893 MAJOR_LOAD_FACTOR = P_MAJOR_LOAD_FACTOR,
894 MINOR_LOAD_FACTOR = P_MINOR_LOAD_FACTOR,
895 EXTRA = P_EXTRA,
896 LAST_UPDATE_DATE = SYSDATE
897 WHERE
898 SERVER_ID = P_SERVER_ID;
899
900
901 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
902
903 INSERT INTO IEO_SVR_RT_INFO
904 ( SERVER_ID,
905 STATUS,
906 MAJOR_LOAD_FACTOR,
907 MINOR_LOAD_FACTOR,
908 EXTRA,
909 LAST_UPDATE_DATE )
910 VALUES (
911 P_SERVER_ID,
912 P_STATUS,
913 P_MAJOR_LOAD_FACTOR,
914 P_MINOR_LOAD_FACTOR,
915 P_EXTRA,
916 SYSDATE );
917
918 END IF;
919
920 COMMIT;
921
922
923 EXCEPTION
924 WHEN OTHERS THEN
925 ROLLBACK TO start_update;
926 RAISE;
927
928 END UPDATE_RT_INFO;
929
930
931
932
933
934 /* Used to update real-time server information when load information is not specified. */
935
936 PROCEDURE UPDATE_RT_INFO_NO_LOAD
937 (P_SERVER_ID IN NUMBER
938 ,P_STATUS IN NUMBER
939 ,P_EXTRA IN VARCHAR2
940 )
941 AS
942 BEGIN
943
944 IF ((P_SERVER_ID IS NULL) OR (P_STATUS IS NULL)) THEN
945 raise_application_error
946 (-20000
947 ,'P_SERVER_ID and P_STATUS cannot be NULL. (P_SERVER_ID = ' ||
948 P_SERVER_ID || ') (P_STATUS = ' || P_STATUS || ')'
949 ,TRUE );
950 END IF;
951
952
953 SAVEPOINT start_update;
954
955
956 UPDATE IEO_SVR_RT_INFO
957 SET
958 STATUS = P_STATUS,
959 EXTRA = P_EXTRA,
960 LAST_UPDATE_DATE = SYSDATE
961 WHERE
962 SERVER_ID = P_SERVER_ID;
963
964
965 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
966
967 INSERT INTO IEO_SVR_RT_INFO
968 ( SERVER_ID,
969 STATUS,
970 EXTRA,
971 LAST_UPDATE_DATE )
972 VALUES (
973 P_SERVER_ID,
974 P_STATUS,
975 P_EXTRA,
976 SYSDATE );
977
978 END IF;
979
980 COMMIT ;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 ROLLBACK TO start_update;
985 RAISE;
986
987 END UPDATE_RT_INFO_NO_LOAD;
988
989
990
991
992 /* Locates a server of a particular type, given a group. */
993 PROCEDURE LOCATE_LEAST_LOADED_IN_GROUP
994 (P_GROUP_ID IN NUMBER
995 ,P_SERVER_TYPE_UUID IN VARCHAR2
996 ,P_EXCLUDE_SERVER_ID IN NUMBER
997 ,X_SERVER_ID OUT NOCOPY NUMBER
998 ,P_TIMEOUT_TOLERANCE IN NUMBER
999 )
1000 AS
1001
1002 rt_major_load_min IEO_SVR_RT_INFO.MAJOR_LOAD_FACTOR%TYPE;
1006 rt_minor_load_max IEO_SVR_RT_INFO.MINOR_LOAD_FACTOR%TYPE;
1003 rt_major_load_max IEO_SVR_RT_INFO.MAJOR_LOAD_FACTOR%TYPE;
1004
1005 rt_minor_load_min IEO_SVR_RT_INFO.MINOR_LOAD_FACTOR%TYPE;
1007
1008 ty_major_load_max IEO_SVR_TYPES_B.MAX_MAJOR_LOAD_FACTOR%TYPE;
1009 ty_minor_load_max IEO_SVR_TYPES_B.MAX_MINOR_LOAD_FACTOR%TYPE;
1010
1011 l_exclude_server_id IEO_SVR_SERVERS.SERVER_ID%TYPE;
1012
1013 l_curr_time_secs NUMBER(5);
1014 l_refresh_rate_secs NUMBER(5);
1015
1016 l_timeout_tolerance NUMBER := P_TIMEOUT_TOLERANCE;
1017
1018 BEGIN
1019
1020
1021 IF ((P_GROUP_ID IS NULL) OR (P_SERVER_TYPE_UUID IS NULL)) THEN
1022 raise_application_error
1023 (-20000
1024 ,'P_GROUP_ID and P_SERVER_TYPE_UUID cannot be NULL. (P_GROUP_ID = ' ||
1025 P_GROUP_ID || ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID || ')'
1026 ,TRUE );
1027 END IF;
1028
1029
1030 --
1031 -- First we try for the lowest MAJOR_LOAD, and if they are all equal, then
1032 -- we try for the lowest MINOR_LOAD. If they're all equal, then it's a wash
1033 -- and we just make sure something is selected.
1034 --
1035
1036
1037 --
1038 -- Collecting some information that we need in the next step(s).
1039 --
1040
1041
1042 IF (P_EXCLUDE_SERVER_ID IS NULL) THEN
1043
1044 -- zero is an invalid number because the sequence min = 10000
1045 -- this allows the select statements to be stuctured the same because
1046 -- there's a WHERE (server_id <> exlude_server_id) in all of them.
1047 l_exclude_server_id := 0;
1048
1049 ELSE
1050
1051 l_exclude_server_id := P_EXCLUDE_SERVER_ID;
1052
1053 END IF;
1054
1055
1056 --
1057 -- Parameter parsing, if the timeout is NULL, we'll use a default timeout.
1058 -- If it's negative, we'll turn off timeout checking entirely.
1059 --
1060 IF (l_timeout_tolerance IS NULL) THEN
1061 l_timeout_tolerance := 30;
1062 ELSIF (l_timeout_tolerance < 0) THEN
1063 l_timeout_tolerance := NULL;
1064 END IF;
1065
1066
1067 SELECT
1068 DISTINCT
1069 type_table.MAX_MAJOR_LOAD_FACTOR,
1070 type_table.MAX_MINOR_LOAD_FACTOR,
1071 (type_table.RT_REFRESH_RATE * 60)
1072 INTO
1073 ty_major_load_max,
1074 ty_minor_load_max,
1075 l_refresh_rate_secs
1076 FROM
1077 IEO_SVR_TYPES_B type_table
1078 WHERE
1079 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1080 (ROWNUM <= 1);
1081
1082
1083 l_curr_time_secs := to_number(to_char(SYSDATE,'SSSSS'));
1084
1085
1086 SELECT
1087 DISTINCT
1088 MIN(rt_table.MAJOR_LOAD_FACTOR),
1089 MAX(rt_table.MAJOR_LOAD_FACTOR),
1090 MIN(rt_table.MINOR_LOAD_FACTOR),
1091 MAX(rt_table.MINOR_LOAD_FACTOR)
1092 INTO
1093 rt_major_load_min,
1094 rt_major_load_max,
1095 rt_minor_load_min,
1096 rt_minor_load_max
1097 FROM
1098 IEO_SVR_SERVERS svr_table,
1099 IEO_SVR_TYPES_B type_table,
1100 IEO_SVR_RT_INFO rt_table
1101 WHERE
1102 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1103 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1104 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1105 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1106 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1107 (rt_table.STATUS > 0) AND
1108 (
1109 (l_timeout_tolerance IS NULL) OR
1110 (
1111 ABS( l_curr_time_secs -
1112 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1113 <= (l_refresh_rate_secs + l_timeout_tolerance)
1114 )
1115 );
1116
1117
1118 --
1119 -- I've used GOTOs (where I would normally have used 'return's) rather than
1120 -- having a bunch of nested IFs that would be very difficult to follow (if
1121 -- you understand the logic below, and try to figure out what the nested IFs
1122 -- would look like, you'll see what I mean.
1123 --
1124
1125
1126 --
1127 -- Major load takes priority, if we've exceded all possible major loads, then
1128 -- we cannot issue a server. Servers should make sure they publish a "proper"
1129 -- major load maximums to avoid this, if they don't want to use this feature.
1130 --
1131 IF (rt_major_load_min > ty_major_load_max) THEN
1132 X_SERVER_ID := NULL;
1133 GOTO done;
1134 END IF;
1135
1136
1137 --
1138 -- If the min = max it means we cannot use it to determine which server to
1139 -- select, and would have to use the Minor load to determine selection.
1140 --
1141 IF (rt_major_load_min <> rt_major_load_max) THEN
1142
1143 BEGIN
1144 SELECT
1145 DISTINCT
1146 svr_table.SERVER_ID
1147 INTO
1148 X_SERVER_ID
1149 FROM
1150 IEO_SVR_SERVERS svr_table,
1151 IEO_SVR_TYPES_B type_table,
1152 IEO_SVR_RT_INFO rt_table
1153 WHERE
1154 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1155 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1156 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1157 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1158 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1162 (
1159 (rt_table.STATUS > 0) AND
1160 (
1161 (l_timeout_tolerance IS NULL) OR
1163 ABS( l_curr_time_secs -
1164 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1165 <= (l_refresh_rate_secs + l_timeout_tolerance)
1166 )
1167 ) AND
1168 (rt_table.MAJOR_LOAD_FACTOR <= rt_major_load_min) AND
1169 (ROWNUM <= 1);
1170
1171 EXCEPTION
1172
1173 WHEN NO_DATA_FOUND THEN
1174 NULL;
1175
1176 WHEN OTHERS THEN
1177 RAISE;
1178
1179 END;
1180
1181 GOTO done;
1182
1183 END IF;
1184
1185
1186 --
1187 --
1188 -- If we get this far, we're supposed to determine load based on the Minor
1189 -- factor because we cannot determine it from the Major factor.
1190 --
1191 --
1192
1193
1194 --
1195 -- Minor load takes priority, if we've exceded all possible minor loads, then
1196 -- we cannot issue a server. Servers should make sure they publish a "proper"
1197 -- minor load maximums to avoid this, if they don't want to use this feature.
1198 --
1199 IF (rt_minor_load_min > ty_minor_load_max) THEN
1200 X_SERVER_ID := NULL;
1201 GOTO done;
1202 END IF;
1203
1204
1205 --
1206 -- If the min = max it means we cannot use it to determine which server to
1207 -- select, and would have to use some random method to determine selection.
1208 --
1209 IF (rt_minor_load_min <> rt_minor_load_max) THEN
1210
1211 BEGIN
1212 SELECT
1213 DISTINCT
1214 svr_table.SERVER_ID
1215 INTO
1216 X_SERVER_ID
1217 FROM
1218 IEO_SVR_SERVERS svr_table,
1219 IEO_SVR_TYPES_B type_table,
1220 IEO_SVR_RT_INFO rt_table
1221 WHERE
1222 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1223 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1224 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1225 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1226 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1227 (rt_table.STATUS > 0) AND
1228 (
1229 (l_timeout_tolerance IS NULL) OR
1230 (
1231 ABS( l_curr_time_secs -
1232 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1233 <= (l_refresh_rate_secs + l_timeout_tolerance)
1234 )
1235 ) AND
1236 (rt_table.MINOR_LOAD_FACTOR <= rt_minor_load_min) AND
1237 (ROWNUM <= 1);
1238
1239 EXCEPTION
1240
1241 WHEN NO_DATA_FOUND THEN
1242 NULL;
1243
1244 WHEN OTHERS THEN
1245 RAISE;
1246
1247 END;
1248
1249 GOTO done;
1250
1251 END IF;
1252
1253
1254 --
1255 --
1256 -- If we get to this point, neither the Major nor the Minor was able to
1257 -- determine a server, yet everything is under the Maximums, so we have to
1258 -- "randomly" assign a server.
1259 --
1260 --
1261
1262 --
1263 -- We'll just select the "First" server we can get.
1264 --
1265 BEGIN
1266
1267 SELECT
1268 DISTINCT
1269 svr_table.SERVER_ID
1270 INTO
1271 X_SERVER_ID
1272 FROM
1273 IEO_SVR_SERVERS svr_table,
1274 IEO_SVR_TYPES_B type_table,
1275 IEO_SVR_RT_INFO rt_table
1276 WHERE
1277 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1278 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1279 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1280 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1281 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1282 (rt_table.STATUS > 0) AND
1283 (
1284 (l_timeout_tolerance IS NULL) OR
1285 (
1286 ABS( l_curr_time_secs -
1287 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1288 <= (l_refresh_rate_secs + l_timeout_tolerance)
1289 )
1290 ) AND
1291 (ROWNUM <= 1);
1292
1293 EXCEPTION
1294
1295 WHEN NO_DATA_FOUND THEN
1296 NULL;
1297
1298 WHEN OTHERS THEN
1299 RAISE;
1300
1301 END;
1302
1303
1304 <<done>>
1305 NULL;
1306
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 RAISE;
1311
1312
1313 END LOCATE_LEAST_LOADED_IN_GROUP;
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334 /* Locates a server of a particular type, given a group. */
1335 PROCEDURE LOCATE_BY_MAJOR_LOAD
1336 (P_GROUP_ID IN NUMBER
1337 ,P_SERVER_TYPE_UUID IN VARCHAR2
1338 ,P_EXCLUDE_SERVER_ID IN NUMBER
1339 ,X_SERVER_ID OUT NOCOPY NUMBER
1340 ,P_TIMEOUT_TOLERANCE IN NUMBER
1341 )
1342 AS
1343
1344 rt_major_load_min IEO_SVR_RT_INFO.MAJOR_LOAD_FACTOR%TYPE;
1348 ty_major_load_max IEO_SVR_TYPES_B.MAX_MAJOR_LOAD_FACTOR%TYPE;
1345 rt_major_load_max IEO_SVR_RT_INFO.MAJOR_LOAD_FACTOR%TYPE;
1346
1347
1349
1350 l_exclude_server_id IEO_SVR_SERVERS.SERVER_ID%TYPE;
1351
1352 l_curr_time_secs NUMBER(5);
1353 l_refresh_rate_secs NUMBER(5);
1354
1355 l_timeout_tolerance NUMBER := P_TIMEOUT_TOLERANCE;
1356
1357 BEGIN
1358
1359
1360 IF ((P_GROUP_ID IS NULL) OR (P_SERVER_TYPE_UUID IS NULL)) THEN
1361 raise_application_error
1362 (-20000
1363 ,'P_GROUP_ID and P_SERVER_TYPE_UUID cannot be NULL. (P_GROUP_ID = ' ||
1364 P_GROUP_ID || ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID || ')'
1365 ,TRUE );
1366 END IF;
1367
1368
1369 --
1370 -- First we try for the lowest MAJOR_LOAD, and if they are all equal, then
1371 -- we try for the lowest MINOR_LOAD. If they're all equal, then it's a wash
1372 -- and we just make sure something is selected.
1373 --
1374
1375
1376 --
1377 -- Collecting some information that we need in the next step(s).
1378 --
1379
1380
1381 IF (P_EXCLUDE_SERVER_ID IS NULL) THEN
1382
1383 -- zero is an invalid number because the sequence min = 10000
1384 -- this allows the select statements to be stuctured the same because
1385 -- there's a WHERE (server_id <> exlude_server_id) in all of them.
1386 l_exclude_server_id := 0;
1387
1388 ELSE
1389
1390 l_exclude_server_id := P_EXCLUDE_SERVER_ID;
1391
1392 END IF;
1393
1394
1395 --
1396 -- Parameter parsing, if the timeout is NULL, we'll use a default timeout.
1397 -- If it's negative, we'll turn off timeout checking entirely.
1398 --
1399 IF (l_timeout_tolerance IS NULL) THEN
1400 l_timeout_tolerance := 30;
1401 ELSIF (l_timeout_tolerance < 0) THEN
1402 l_timeout_tolerance := NULL;
1403 END IF;
1404
1405
1406 SELECT
1407 DISTINCT
1408 type_table.MAX_MAJOR_LOAD_FACTOR,
1409 (type_table.RT_REFRESH_RATE * 60)
1410 INTO
1411 ty_major_load_max,
1412 l_refresh_rate_secs
1413 FROM
1414 IEO_SVR_TYPES_B type_table
1415 WHERE
1416 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1417 (ROWNUM <= 1);
1418
1419
1420 l_curr_time_secs := to_number(to_char(SYSDATE,'SSSSS'));
1421
1422
1423 SELECT
1424 DISTINCT
1425 MIN(rt_table.MAJOR_LOAD_FACTOR),
1426 MAX(rt_table.MAJOR_LOAD_FACTOR)
1427 INTO
1428 rt_major_load_min,
1429 rt_major_load_max
1430 FROM
1431 IEO_SVR_SERVERS svr_table,
1432 IEO_SVR_TYPES_B type_table,
1433 IEO_SVR_RT_INFO rt_table
1434 WHERE
1435 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1436 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1437 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1438 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1439 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1440 (rt_table.STATUS > 1) AND
1441 (
1442 (l_timeout_tolerance IS NULL) OR
1443 (
1444 ABS( l_curr_time_secs -
1445 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1446 <= (l_refresh_rate_secs + l_timeout_tolerance)
1447 )
1448 );
1449
1450
1451 --
1452 -- I've used GOTOs (where I would normally have used 'return's) rather than
1453 -- having a bunch of nested IFs that would be very difficult to follow (if
1454 -- you understand the logic below, and try to figure out what the nested IFs
1455 -- would look like, you'll see what I mean.
1456 --
1457
1458
1459 --
1460 -- Major load takes priority, if we've exceded all possible major loads, then
1461 -- we cannot issue a server. Servers should make sure they publish a "proper"
1462 -- major load maximums to avoid this, if they don't want to use this feature.
1463 --
1464 IF (rt_major_load_min > ty_major_load_max) THEN
1465 X_SERVER_ID := NULL;
1466 GOTO done;
1467 END IF;
1468
1469
1470 --
1471 -- If the min = max it means we cannot use it to determine which server to
1472 -- select, and would have to use the Minor load to determine selection.
1473 --
1474 IF (rt_major_load_min <> rt_major_load_max) THEN
1475
1476 BEGIN
1477 SELECT
1478 DISTINCT
1479 svr_table.SERVER_ID
1480 INTO
1481 X_SERVER_ID
1482 FROM
1483 IEO_SVR_SERVERS svr_table,
1484 IEO_SVR_TYPES_B type_table,
1485 IEO_SVR_RT_INFO rt_table
1486 WHERE
1487 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1488 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1489 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1490 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1491 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1492 (rt_table.STATUS > 1) AND
1493 (
1494 (l_timeout_tolerance IS NULL) OR
1495 (
1496 ABS( l_curr_time_secs -
1497 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1498 <= (l_refresh_rate_secs + l_timeout_tolerance)
1499 )
1500 ) AND
1501 (rt_table.MAJOR_LOAD_FACTOR <= rt_major_load_min) AND
1502 (ROWNUM <= 1);
1503
1504 EXCEPTION
1505
1506 WHEN NO_DATA_FOUND THEN
1507 NULL;
1508
1509 WHEN OTHERS THEN
1510 RAISE;
1514 GOTO done;
1511
1512 END;
1513
1515
1516 END IF;
1517
1518 -- If we get to this point, the Major was not able to
1519 -- determine a server, yet everything is under the Maximums, so we have to
1520 -- "randomly" assign a server.
1521 --
1522 --
1523
1524 --
1525 -- We'll just select the "First" server we can get.
1526 --
1527 BEGIN
1528
1529 SELECT
1530 DISTINCT
1531 svr_table.SERVER_ID
1532 INTO
1533 X_SERVER_ID
1534 FROM
1535 IEO_SVR_SERVERS svr_table,
1536 IEO_SVR_TYPES_B type_table,
1537 IEO_SVR_RT_INFO rt_table
1538 WHERE
1539 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1540 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1541 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1542 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1543 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1544 (rt_table.STATUS > 1) AND
1545 (
1546 (l_timeout_tolerance IS NULL) OR
1547 (
1548 ABS( l_curr_time_secs -
1549 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1550 <= (l_refresh_rate_secs + l_timeout_tolerance)
1551 )
1552 ) AND
1553 (ROWNUM <= 1);
1554
1555 EXCEPTION
1556
1557 WHEN NO_DATA_FOUND THEN
1558 NULL;
1559
1560 WHEN OTHERS THEN
1561 RAISE;
1562
1563 END;
1564
1565
1566 <<done>>
1567 NULL;
1568
1569
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 RAISE;
1573
1574
1575 END LOCATE_BY_MAJOR_LOAD;
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594 /* Locates a server of a particular type, given a group. */
1595 PROCEDURE LOCATE_BY_MINOR_LOAD
1596 (P_GROUP_ID IN NUMBER
1597 ,P_SERVER_TYPE_UUID IN VARCHAR2
1598 ,P_EXCLUDE_SERVER_ID IN NUMBER
1599 ,X_SERVER_ID OUT NOCOPY NUMBER
1600 ,P_TIMEOUT_TOLERANCE IN NUMBER
1601 )
1602 AS
1603 rt_minor_load_min IEO_SVR_RT_INFO.MINOR_LOAD_FACTOR%TYPE;
1604 rt_minor_load_max IEO_SVR_RT_INFO.MINOR_LOAD_FACTOR%TYPE;
1605 ty_minor_load_max IEO_SVR_TYPES_B.MAX_MINOR_LOAD_FACTOR%TYPE;
1606
1607 l_exclude_server_id IEO_SVR_SERVERS.SERVER_ID%TYPE;
1608
1609 l_curr_time_secs NUMBER(5);
1610 l_refresh_rate_secs NUMBER(5);
1611
1612 l_timeout_tolerance NUMBER := P_TIMEOUT_TOLERANCE;
1613
1614 BEGIN
1615
1616
1617 IF ((P_GROUP_ID IS NULL) OR (P_SERVER_TYPE_UUID IS NULL)) THEN
1618 raise_application_error
1619 (-20000
1620 ,'P_GROUP_ID and P_SERVER_TYPE_UUID cannot be NULL. (P_GROUP_ID = ' ||
1621 P_GROUP_ID || ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID || ')'
1622 ,TRUE );
1623 END IF;
1624
1625
1626 --
1627 -- First we try for the lowest MAJOR_LOAD, and if they are all equal, then
1628 -- we try for the lowest MINOR_LOAD. If they're all equal, then it's a wash
1629 -- and we just make sure something is selected.
1630 --
1631
1632
1633 --
1634 -- Collecting some information that we need in the next step(s).
1635 --
1636
1637
1638 IF (P_EXCLUDE_SERVER_ID IS NULL) THEN
1639
1640 -- zero is an invalid number because the sequence min = 10000
1641 -- this allows the select statements to be stuctured the same because
1642 -- there's a WHERE (server_id <> exlude_server_id) in all of them.
1643 l_exclude_server_id := 0;
1644
1645 ELSE
1646
1647 l_exclude_server_id := P_EXCLUDE_SERVER_ID;
1648
1649 END IF;
1650
1651
1652 --
1653 -- Parameter parsing, if the timeout is NULL, we'll use a default timeout.
1654 -- If it's negative, we'll turn off timeout checking entirely.
1655 --
1656 IF (l_timeout_tolerance IS NULL) THEN
1657 l_timeout_tolerance := 30;
1658 ELSIF (l_timeout_tolerance < 0) THEN
1659 l_timeout_tolerance := NULL;
1660 END IF;
1661
1662
1663 SELECT
1664 DISTINCT
1665 type_table.MAX_MINOR_LOAD_FACTOR,
1666 (type_table.RT_REFRESH_RATE * 60)
1667 INTO
1668 ty_minor_load_max,
1669 l_refresh_rate_secs
1670 FROM
1671 IEO_SVR_TYPES_B type_table
1672 WHERE
1673 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1674 (ROWNUM <= 1);
1675
1676
1677 l_curr_time_secs := to_number(to_char(SYSDATE,'SSSSS'));
1678
1679
1680 SELECT
1681 DISTINCT
1682 MIN(rt_table.MINOR_LOAD_FACTOR),
1683 MAX(rt_table.MINOR_LOAD_FACTOR)
1684 INTO
1685 rt_minor_load_min,
1686 rt_minor_load_max
1687 FROM
1688 IEO_SVR_SERVERS svr_table,
1689 IEO_SVR_TYPES_B type_table,
1690 IEO_SVR_RT_INFO rt_table
1691 WHERE
1692 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1693 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1694 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1695 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1696 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1697 (rt_table.STATUS > 1) AND
1698 (
1699 (l_timeout_tolerance IS NULL) OR
1700 (
1701 ABS( l_curr_time_secs -
1702 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1703 <= (l_refresh_rate_secs + l_timeout_tolerance)
1704 )
1708 --
1705 );
1706
1707 --
1709 -- If we get this far, we're supposed to determine load based on the Minor
1710 -- factor because we cannot determine it from the Major factor.
1711 --
1712 --
1713
1714
1715 --
1716 -- Minor load takes priority, if we've exceded all possible minor loads, then
1717 -- we cannot issue a server. Servers should make sure they publish a "proper"
1718 -- minor load maximums to avoid this, if they don't want to use this feature.
1719 --
1720 IF (rt_minor_load_min > ty_minor_load_max) THEN
1721 X_SERVER_ID := NULL;
1722 GOTO done;
1723 END IF;
1724
1725
1726 --
1727 -- If the min = max it means we cannot use it to determine which server to
1728 -- select, and would have to use some random method to determine selection.
1729 --
1730 IF (rt_minor_load_min <> rt_minor_load_max) THEN
1731
1732 BEGIN
1733 SELECT
1734 DISTINCT
1735 svr_table.SERVER_ID
1736 INTO
1737 X_SERVER_ID
1738 FROM
1739 IEO_SVR_SERVERS svr_table,
1740 IEO_SVR_TYPES_B type_table,
1741 IEO_SVR_RT_INFO rt_table
1742 WHERE
1743 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1744 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1745 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1746 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1747 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1748 (rt_table.STATUS > 1) AND
1749 (
1750 (l_timeout_tolerance IS NULL) OR
1751 (
1752 ABS( l_curr_time_secs -
1753 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1754 <= (l_refresh_rate_secs + l_timeout_tolerance)
1755 )
1756 ) AND
1757 (rt_table.MINOR_LOAD_FACTOR <= rt_minor_load_min) AND
1758 (ROWNUM <= 1);
1759
1760 EXCEPTION
1761
1762 WHEN NO_DATA_FOUND THEN
1763 NULL;
1764
1765 WHEN OTHERS THEN
1766 RAISE;
1767
1768 END;
1769
1770 GOTO done;
1771
1772 END IF;
1773
1774
1775 --
1776 --
1777 -- If we get to this point, neither the Major nor the Minor was able to
1778 -- determine a server, yet everything is under the Maximums, so we have to
1779 -- "randomly" assign a server.
1780 --
1781 --
1782
1783 --
1784 -- We'll just select the "First" server we can get.
1785 --
1786 BEGIN
1787
1788 SELECT
1789 DISTINCT
1790 svr_table.SERVER_ID
1791 INTO
1792 X_SERVER_ID
1793 FROM
1794 IEO_SVR_SERVERS svr_table,
1795 IEO_SVR_TYPES_B type_table,
1796 IEO_SVR_RT_INFO rt_table
1797 WHERE
1798 (svr_table.TYPE_ID = type_table.TYPE_ID) AND
1799 (svr_table.SERVER_ID = rt_table.SERVER_ID) AND
1800 (svr_table.SERVER_ID <> l_exclude_server_id) AND
1801 (svr_table.MEMBER_SVR_GROUP_ID = P_GROUP_ID) AND
1802 (type_table.TYPE_UUID = P_SERVER_TYPE_UUID) AND
1803 (rt_table.STATUS > 1) AND
1804 (
1805 (l_timeout_tolerance IS NULL) OR
1806 (
1807 ABS( l_curr_time_secs -
1808 (to_number(to_char(rt_table.LAST_UPDATE_DATE,'SSSSS'))) )
1809 <= (l_refresh_rate_secs + l_timeout_tolerance)
1810 )
1811 ) AND
1812 (ROWNUM <= 1);
1813
1814 EXCEPTION
1815
1816 WHEN NO_DATA_FOUND THEN
1817 NULL;
1818
1819 WHEN OTHERS THEN
1820 RAISE;
1821
1822 END;
1823
1824
1825 <<done>>
1826 NULL;
1827
1828
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 RAISE;
1832
1833
1834 END LOCATE_BY_MINOR_LOAD;
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845 /* Locates a server of a particular type, given another server. */
1846 PROCEDURE LOCATE_LEAST_LOADED_FOR_SVR
1847 (P_SERVER_ID_LOOKING IN NUMBER
1848 ,P_SERVER_TYPE_UUID IN VARCHAR2
1849 ,X_SERVER_ID_FOUND OUT NOCOPY NUMBER
1850 ,P_TIMEOUT_TOLERANCE IN NUMBER
1851 )
1852 AS
1853
1854 l_group_id IEO_SVR_GROUPS.SERVER_GROUP_ID%TYPE;
1855
1859 IF ((P_SERVER_ID_LOOKING IS NULL) OR (P_SERVER_TYPE_UUID IS NULL)) THEN
1856 BEGIN
1857
1858
1860 raise_application_error
1861 (-20000
1862 ,'P_SERVER_ID_LOOKING and P_SERVER_TYPE_UUID cannot be NULL. (P_SERVER_ID_LOOKING = ' ||
1863 P_SERVER_ID_LOOKING || ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID || ')'
1864 ,TRUE );
1865 END IF;
1866
1867
1868 SELECT
1869 DISTINCT
1870 NVL( svr_table.USING_SVR_GROUP_ID, svr_table.MEMBER_SVR_GROUP_ID )
1871 INTO
1872 l_group_id
1873 FROM
1874 IEO_SVR_SERVERS svr_table
1875 WHERE
1876 (svr_table.SERVER_ID = P_SERVER_ID_LOOKING) AND
1877 (ROWNUM <= 1);
1878
1879 --
1880 -- Locate a server in the proper group, excluding the server that is
1881 -- trying to perform the location.
1882 --
1883 LOCATE_LEAST_LOADED_IN_GROUP(
1884 l_group_id,
1885 p_server_type_uuid,
1886 p_server_id_looking,
1887 x_server_id_found,
1888 p_timeout_tolerance );
1889
1890
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 RAISE;
1894
1895
1896 END LOCATE_LEAST_LOADED_FOR_SVR;
1897
1898
1899 /* Locates a server of a particular type, given another server, and obtains */
1900 /* the connection information, based on some default rules. */
1901 PROCEDURE LOCATE_LLS_AND_INFO
1902 (P_SERVER_ID IN NUMBER
1903 ,P_SERVER_TYPE_UUID IN VARCHAR2
1904 ,P_WIRE_PROTOCOL IN VARCHAR2
1905 ,P_COMP_DEF_NAME IN VARCHAR2
1906 ,P_COMP_DEF_VERSION IN NUMBER
1907 ,P_COMP_DEF_IMPL IN VARCHAR2
1908 ,P_COMP_NAME IN VARCHAR2
1909 ,X_SERVER_ID_FOUND OUT NOCOPY NUMBER
1910 ,X_USER_ADDRESS OUT NOCOPY VARCHAR2
1911 ,X_DNS_NAME OUT NOCOPY VARCHAR2
1912 ,X_IP_ADDRESS OUT NOCOPY VARCHAR2
1913 ,X_PORT OUT NOCOPY NUMBER
1914 ,X_COMP_NAME OUT NOCOPY VARCHAR2
1915 ,P_TIMEOUT_TOLERANCE IN NUMBER
1916 )
1917 AS
1918 BEGIN
1919
1920
1921 IF ( (P_SERVER_ID IS NULL) OR
1922 (P_SERVER_TYPE_UUID IS NULL) OR
1923 (P_WIRE_PROTOCOL IS NULL) OR
1924 (P_COMP_DEF_NAME IS NULL) OR
1925 (P_COMP_DEF_VERSION IS NULL) OR
1926 (P_COMP_DEF_IMPL IS NULL) )
1927 THEN
1928 raise_application_error
1929 (-20000
1930 ,'A required parameter is null' ||
1931 '. (P_SERVER_ID = ' || P_SERVER_ID ||
1932 ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID ||
1933 ') (P_WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL ||
1934 ') (P_COMP_DEF_NAME = ' || P_COMP_DEF_NAME ||
1935 ') (P_COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION ||
1936 ') (P_COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL ||
1937 ')'
1938 ,TRUE );
1939 END IF;
1940
1941
1942 LOCATE_LEAST_LOADED_FOR_SVR(
1943 P_SERVER_ID,
1944 P_SERVER_TYPE_UUID,
1945 x_server_id_found,
1946 p_timeout_tolerance
1947 );
1948
1949
1950 IF (x_server_id_found IS NULL) THEN
1951 raise_application_error
1952 (-20010,
1953 'Could not locate a server to connect to.',
1954 TRUE );
1955 END IF;
1956
1957
1958 IF (P_COMP_NAME IS NULL) THEN
1959
1960 SELECT
1961 DISTINCT
1962 comp_table.COMP_NAME
1963 INTO
1964 X_COMP_NAME
1965 FROM
1966 IEO_SVR_SERVERS svr_table,
1967 IEO_SVR_COMP_DEFS cdef_table,
1968 IEO_SVR_COMPS comp_table,
1969 IEO_SVR_PROTOCOL_MAP prot_table
1970 WHERE
1971 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
1972 (svr_table.SERVER_ID = x_server_id_found) AND
1973 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
1974 (prot_table.COMP_ID = comp_table.COMP_ID) AND
1975 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
1976 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
1977 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
1978 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
1979 (ROWNUM <= 1);
1980
1981 ELSE
1982
1983 X_COMP_NAME := P_COMP_NAME;
1984
1985 END IF;
1986
1987
1988 SELECT
1989 DISTINCT
1990 svr_table.USER_ADDRESS,
1991 svr_table.DNS_NAME,
1992 svr_table.IP_ADDRESS,
1993 prot_table.PORT
1994 INTO
1995 X_USER_ADDRESS,
1996 X_DNS_NAME,
1997 X_IP_ADDRESS,
1998 X_PORT
1999 FROM
2000 IEO_SVR_SERVERS svr_table,
2001 IEO_SVR_COMP_DEFS cdef_table,
2002 IEO_SVR_COMPS comp_table,
2003 IEO_SVR_PROTOCOL_MAP prot_table
2004 WHERE
2005 (svr_table.SERVER_ID = x_server_id_found) AND
2006 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
2007 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
2008 (prot_table.COMP_ID = comp_table.COMP_ID) AND
2009 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
2010 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
2011 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
2012 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
2013 (comp_table.COMP_NAME = X_COMP_NAME) AND
2014 (ROWNUM <= 1);
2015
2016
2017 EXCEPTION
2018 WHEN OTHERS THEN
2019 RAISE;
2020
2021
2022 END LOCATE_LLS_AND_INFO;
2023
2024
2025 /* Locates a server of a particular type, given a server group, and obtains */
2026 /* the connection information, based on some default rules. */
2027 PROCEDURE LOCATE_LLS_AND_INFO_BY_GROUP
2028 (P_SERVER_GROUP_ID IN NUMBER
2029 ,P_SERVER_TYPE_UUID IN VARCHAR2
2033 ,P_COMP_DEF_IMPL IN VARCHAR2
2030 ,P_WIRE_PROTOCOL IN VARCHAR2
2031 ,P_COMP_DEF_NAME IN VARCHAR2
2032 ,P_COMP_DEF_VERSION IN NUMBER
2034 ,P_COMP_NAME IN VARCHAR2
2035 ,X_SERVER_ID_FOUND OUT NOCOPY NUMBER
2036 ,X_USER_ADDRESS OUT NOCOPY VARCHAR2
2037 ,X_DNS_NAME OUT NOCOPY VARCHAR2
2038 ,X_IP_ADDRESS OUT NOCOPY VARCHAR2
2039 ,X_PORT OUT NOCOPY NUMBER
2040 ,X_COMP_NAME OUT NOCOPY VARCHAR2
2041 ,P_TIMEOUT_TOLERANCE IN NUMBER
2042 )
2043 AS
2044 BEGIN
2045
2046
2047 IF ( (P_SERVER_GROUP_ID IS NULL) OR
2048 (P_SERVER_TYPE_UUID IS NULL) OR
2049 (P_WIRE_PROTOCOL IS NULL) OR
2050 (P_COMP_DEF_NAME IS NULL) OR
2051 (P_COMP_DEF_VERSION IS NULL) OR
2052 (P_COMP_DEF_IMPL IS NULL) )
2053 THEN
2054 raise_application_error
2055 (-20000
2056 ,'A required parameter is null' ||
2057 '. (P_SERVER_GROUP_ID = ' || P_SERVER_GROUP_ID ||
2058 ') (P_SERVER_TYPE_UUID = ' || P_SERVER_TYPE_UUID ||
2059 ') (P_WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL ||
2060 ') (P_COMP_DEF_NAME = ' || P_COMP_DEF_NAME ||
2061 ') (P_COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION ||
2062 ') (P_COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL ||
2063 ')'
2064 ,TRUE );
2065 END IF;
2066
2067
2068 LOCATE_LEAST_LOADED_IN_GROUP(
2069 P_SERVER_GROUP_ID,
2070 P_SERVER_TYPE_UUID,
2071 NULL,
2072 x_server_id_found,
2073 p_timeout_tolerance
2074 );
2075
2076
2077 IF (x_server_id_found IS NULL) THEN
2078 raise_application_error
2079 (-20010,
2080 'Could not locate a server to connect to.',
2081 TRUE );
2082 END IF;
2083
2084
2085 IF (P_COMP_NAME IS NULL) THEN
2086
2087 begin
2088 SELECT
2089 DISTINCT
2090 comp_table.COMP_NAME
2091 INTO
2092 X_COMP_NAME
2093 FROM
2094 IEO_SVR_SERVERS svr_table,
2095 IEO_SVR_COMP_DEFS cdef_table,
2096 IEO_SVR_COMPS comp_table,
2097 IEO_SVR_PROTOCOL_MAP prot_table
2098 WHERE
2099 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
2100 (svr_table.SERVER_ID = x_server_id_found) AND
2101 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
2102 (prot_table.COMP_ID = comp_table.COMP_ID) AND
2103 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
2104 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
2105 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
2106 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
2107 (ROWNUM <= 1);
2108 exception
2109 when others then
2110 raise_application_error
2111 (-20020
2112 ,'Could not find Component Definition.' ||
2113 ' (SERVER_ID = ' || x_server_id_found ||
2114 ') (WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL || ')' ||
2115 ') (COMP_DEF_NAME = ' || P_COMP_DEF_NAME || ')' ||
2116 ') (COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION || ')' ||
2117 ') (COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL || ')'
2118 ,TRUE );
2119 end;
2120
2121
2122 ELSE
2123
2124 X_COMP_NAME := P_COMP_NAME;
2125
2126 END IF;
2127
2128
2129 SELECT
2133 svr_table.IP_ADDRESS,
2130 DISTINCT
2131 svr_table.USER_ADDRESS,
2132 svr_table.DNS_NAME,
2134 prot_table.PORT
2135 INTO
2136 X_USER_ADDRESS,
2137 X_DNS_NAME,
2138 X_IP_ADDRESS,
2139 X_PORT
2140 FROM
2141 IEO_SVR_SERVERS svr_table,
2142 IEO_SVR_COMP_DEFS cdef_table,
2143 IEO_SVR_COMPS comp_table,
2144 IEO_SVR_PROTOCOL_MAP prot_table
2145 WHERE
2146 (svr_table.SERVER_ID = x_server_id_found) AND
2147 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
2148 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
2149 (prot_table.COMP_ID = comp_table.COMP_ID) AND
2150 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
2151 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
2152 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
2153 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
2154 (comp_table.COMP_NAME = X_COMP_NAME) AND
2155 (ROWNUM <= 1);
2156
2157
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 RAISE;
2161
2162
2163 END LOCATE_LLS_AND_INFO_BY_GROUP;
2164
2165
2166
2167 PROCEDURE GET_SVR_CONNECT_INFO
2168 (
2169 p_api_version IN NUMBER,
2170 p_init_msg_list IN VARCHAR2 ,
2171 p_commit IN VARCHAR2,
2172 p_server_id IN NUMBER,
2173 p_server_type_id IN NUMBER,
2174 p_comp_def_name IN VARCHAR2,
2175 p_comp_def_version IN NUMBER,
2176 x_return_status OUT NOCOPY VARCHAR2 ,
2177 x_msg_count OUT NOCOPY NUMBER ,
2178 x_msg_data OUT NOCOPY VARCHAR2,
2179 x_comp_id OUT NOCOPY NUMBER,
2180 x_comp_name OUT NOCOPY VARCHAR2,
2181 x_wire_protocol OUT NOCOPY VARCHAR2,
2182 x_port OUT NOCOPY NUMBER,
2183 x_ip OUT NOCOPY VARCHAR2,
2184 x_base_url OUT NOCOPY VARCHAR2,
2185 x_url OUT NOCOPY VARCHAR2
2186 )
2187 IS
2188 l_api_name CONSTANT VARCHAR2(30) := 'GET_SVR_CONNECT_INFO';
2189 l_api_version CONSTANT NUMBER := 1.0;
2190 x_err_num NUMBER;
2191 x_err_msg VARCHAR2(256);
2192
2193 l_comp_def_id NUMBER;
2194
2195 BEGIN
2196 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2197 p_api_version ,
2198 l_api_name ,
2199 G_PKG_NAME )
2200 THEN
2201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2202 END IF;
2203 -- Initialize message list if p_init_msg_list is set to TRUE.
2204 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2205 FND_MSG_PUB.initialize;
2206 END IF;
2207 -- Initialize API return status to failure
2208 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2209 x_msg_count := 0;
2210 x_msg_data := null;
2211 x_comp_id := -1;
2212 x_comp_name := null;
2213 x_wire_protocol := null;
2214 x_port := -1;
2215
2216 -- API body
2217
2218 select ip_address into x_ip
2219 from ieo_svr_servers where server_id = p_server_id;
2220
2221 SELECT COMP_DEF_ID INTO l_comp_def_id FROM IEO_SVR_COMP_DEFS WHERE
2222 COMP_DEF_NAME = p_comp_def_name and
2223 COMP_DEF_VERSION = p_comp_def_version and
2224 SERVER_TYPE_ID = p_server_type_id;
2225
2226 SELECT A.COMP_ID, A.COMP_NAME, B.WIRE_PROTOCOL, B.PORT
2227 into x_comp_id, x_comp_name, x_wire_protocol, x_port
2228 FROM IEO_SVR_COMPS A, IEO_SVR_PROTOCOL_MAP B
2229 WHERE A.COMP_ID = B.COMP_ID
2230 AND A.COMP_DEF_ID = l_comp_def_id
2231 AND A.SERVER_ID = p_server_id;
2232
2233 x_base_url := x_wire_protocol
2234 || '://'
2235 || x_ip
2236 || ':'
2237 || x_port
2238 || '/' ;
2239 x_url := x_base_url || x_comp_name;
2240
2241 IF FND_API.To_Boolean( p_commit ) THEN
2242 COMMIT WORK;
2243 END IF;
2244
2245 EXCEPTION
2246 WHEN NO_DATA_FOUND then
2247 rollback;
2248 x_return_status := FND_API.G_RET_STS_ERROR ;
2249 x_msg_count := 1;
2250 x_err_num := SQLCODE;
2251 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2252 x_msg_data := 'GET_SVR_CONNECT_INFO: IEO_EXC_ERROR'
2253 || ' ErrorCode = ' || x_err_num
2254 || ' ErrorMsg = ' || x_err_msg;
2255
2256 WHEN FND_API.G_EXC_ERROR THEN
2257 rollback;
2258 x_return_status := FND_API.G_RET_STS_ERROR ;
2259 x_msg_count := 1;
2260 x_err_num := SQLCODE;
2261 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2262 x_msg_data := 'GET_SVR_CONNECT_INFO: IEO_EXC_ERROR'
2263 || ' ErrorCode = ' || x_err_num
2264 || ' ErrorMsg = ' || x_err_msg;
2265 --dbms_output.put_line(x_msg_data);
2266
2267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2268 --dbms_output.put_line('Unexpected error');
2269 ROLLBACK;
2270 x_return_status := FND_API.G_RET_STS_ERROR ;
2271 x_msg_count := 1;
2272 x_err_num := SQLCODE;
2276 || ' ErrorMsg = ' || x_err_msg;
2273 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2274 x_msg_data := 'GET_SVR_CONNECT_INFO: IEO_UNEXPECTED_ERROR'
2275 || ' ErrorCode = ' || x_err_num
2277 --dbms_output.put_line(x_msg_data);
2278 WHEN OTHERS THEN
2279 rollback;
2280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2281 x_msg_count := 1;
2282 x_err_num := SQLCODE;
2283 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2284 x_msg_data := 'GET_SVR_CONNECT_INFO: IEO_OTHERS_ERROR'
2285 || ' ErrorCode = ' || x_err_num
2286 || ' ErrorMsg = ' || x_err_msg;
2287 --dbms_output.put_line(x_msg_data);
2288 END GET_SVR_CONNECT_INFO;
2289
2290
2291
2292
2293 PROCEDURE IS_SERVER_UP
2294 (
2295 p_api_version IN NUMBER,
2296 p_init_msg_list IN VARCHAR2 ,
2297 p_commit IN VARCHAR2,
2298 p_server_id IN NUMBER,
2299 p_server_type_id IN NUMBER,
2300 x_return_status OUT NOCOPY VARCHAR2 ,
2301 x_msg_count OUT NOCOPY NUMBER ,
2302 x_msg_data OUT NOCOPY VARCHAR2,
2303 x_is_server_up OUT NOCOPY VARCHAR2,
2304 x_server_status OUT NOCOPY NUMBER,
2305 x_server_name OUT NOCOPY VARCHAR2,
2306 x_server_group_name OUT NOCOPY VARCHAR2
2307 )
2308 IS
2309 l_api_name CONSTANT VARCHAR2(30) := 'IS_SERVER_UP';
2310 l_api_version CONSTANT NUMBER := 1.0;
2311 x_err_num NUMBER;
2312 x_err_msg VARCHAR2(256);
2313
2314 l_refresh_rate NUMBER;
2315 l_last_update_date DATE;
2316 l_sysdate DATE;
2317 BEGIN
2318 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2319 p_api_version ,
2320 l_api_name ,
2321 G_PKG_NAME )
2322 THEN
2323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2324 END IF;
2325 -- Initialize message list if p_init_msg_list is set to TRUE.
2326 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2327 FND_MSG_PUB.initialize;
2328 END IF;
2329 -- Initialize API return status to failure
2330 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2331 x_msg_count := 0;
2332 x_msg_data := null;
2333 x_is_server_up := FND_API.G_FALSE;
2334 x_server_status := -1;
2335 x_server_name := null;
2336 x_server_group_name := null;
2337
2338
2339 SELECT RT_REFRESH_RATE into l_refresh_rate
2340 FROM IEO_SVR_TYPES_B
2341 WHERE TYPE_ID = p_server_type_id;
2342
2343 SELECT STATUS, LAST_UPDATE_DATE, SYSDATE NOW
2344 into x_server_status, l_last_update_date, l_sysdate
2345 FROM IEO_SVR_RT_INFO
2346 WHERE SERVER_ID = p_server_id;
2347
2348 --dbms_output.put_line('sysdate= ' || l_sysdate);
2349 --dbms_output.put_line('last_update_date ' || l_last_update_date);
2350 --dbms_output.put_line('refresh_rate= ' || l_refresh_rate);
2351 --dbms_output.put_line('diff= ' || to_char(l_sysdate - l_last_update_date));
2352
2353 if (x_server_status >= 4) then
2354 if (((l_sysdate - l_last_update_date)*1440) <= l_refresh_rate) then
2355 x_is_server_up := FND_API.G_TRUE;
2356 end if;
2357 end if;
2358
2359 select a.server_name, b.group_name into x_server_name, x_server_group_name
2360 from ieo_Svr_servers a, ieo_Svr_groups b
2361 where a.server_id = p_server_id and a.member_svr_group_id = b.server_group_id;
2362
2363 IF FND_API.To_Boolean( p_commit ) THEN
2364 COMMIT WORK;
2365 END IF;
2366
2367 EXCEPTION
2368 WHEN NO_DATA_FOUND then
2369 rollback;
2370 x_return_status := FND_API.G_RET_STS_ERROR ;
2371 x_msg_count := 1;
2372 x_err_num := SQLCODE;
2373 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2374 x_msg_data := 'IS_SERVER_UP: IEO_NO_DATA_FOUND_ERROR'
2375 || ' ErrorCode = ' || x_err_num
2376 || ' ErrorMsg = ' || x_err_msg;
2377
2378 WHEN FND_API.G_EXC_ERROR THEN
2379 rollback;
2380 x_return_status := FND_API.G_RET_STS_ERROR ;
2381 x_msg_count := 1;
2382 x_err_num := SQLCODE;
2383 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2384 x_msg_data := 'IS_SERVER_UP: IEO_EXC_ERROR'
2385 || ' ErrorCode = ' || x_err_num
2386 || ' ErrorMsg = ' || x_err_msg;
2387 --dbms_output.put_line(x_msg_data);
2388
2389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2390 --dbms_output.put_line('Unexpected error');
2391 ROLLBACK;
2392 x_return_status := FND_API.G_RET_STS_ERROR ;
2393 x_msg_count := 1;
2397 || ' ErrorCode = ' || x_err_num
2394 x_err_num := SQLCODE;
2395 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2396 x_msg_data := 'IS_SERVER_UP: IEO_UNEXPECTED_ERROR'
2398 || ' ErrorMsg = ' || x_err_msg;
2399 --dbms_output.put_line(x_msg_data);
2400 WHEN OTHERS THEN
2401 rollback;
2402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2403 x_msg_count := 1;
2404 x_err_num := SQLCODE;
2405 x_err_msg := SUBSTR(SQLERRM, 1, 100);
2406 x_msg_data := 'IS_SERVER_UP: IEO_OTHERS_ERROR'
2407 || ' ErrorCode = ' || x_err_num
2408 || ' ErrorMsg = ' || x_err_msg;
2409 --dbms_output.put_line(x_msg_data);
2410 END IS_SERVER_UP;
2411
2412
2413 -- Clears agent mappings to a particular server. This mapping is
2414 -- required in case of a client crash but, needs cleared following
2415 -- a server crash.
2416 PROCEDURE CLEAR_SERVER_BINDINGS
2417 (P_SERVER_ID IN NUMBER
2418 )
2419 IS
2420
2421 BEGIN
2422 UPDATE IEU_UWQ_AGENT_BINDINGS
2423 SET
2424 NOT_VALID = 'Y',
2425 LAST_UPDATE_DATE = SYSDATE
2426 WHERE
2427 SERVER_ID = P_SERVER_ID;
2428
2429 COMMIT ;
2430
2431 EXCEPTION
2432 WHEN OTHERS THEN
2433 NULL;
2434
2435 END CLEAR_SERVER_BINDINGS;
2436
2437 -- PL/SQL Block
2438 END IEO_SVR_UTIL_PVT;