1 PACKAGE BODY EDW_DB_STATUS as
2 /* $Header: EDWDBSTB.pls 115.11 2003/09/16 13:59:57 smulye ship $ */
3
4 /*===========================================================================*/
5
6 /*
7 Name : check_db_status_all
8
9 Purpose : Sees is all the source dbs are up and running
10 It returns a concatenated string containing the
11 instance_codes for all the source systems that
12 are down. Also, if any of the source systems are
13 down, it returns a flag of FALSE. If all the source
14 DBs are up, it returns true and an empty string in the
15 OUT variable.
16
17 Arguments
18
19 Input
20 Type IN : NONE
21
22 Type OUT : x_instance_code
23 This contains the instance codes for all the source systems
24 that are down. It is a concatenated string
25 Ex : if source systems "source1" and "source5" are down,
26 x_instance_code is "source1 source5"
27 If all systems are up, x_instance_code is an empty
28 string as "".
29
30 Ouput
31 l_status : FALSE if any of the source systems are down
32 TRUE if all the source systems are up.
33 Data Type : BOOLEAN
34 */
35
36 /*
37 Name : check_db_status_site
38
39 Purpose : Sees is the specified source db is up and running
40 Returns TRUE if the DB is up, false if the DB is
41 down.
42 Arguments
43
44 Input
45 Type IN : p_instance_code
46 This is the instance code of the DB that is being
47 checked to see if up or down.
48
49 Type OUT : NONE
50
51 Ouput
52 l_status : FALSE if any of the source systems are down
53 TRUE if all the source systems are up.
54 Data Type : BOOLEAN
55
56 */
57
58 /*Name : check_repository_status
59
60 Purpose : checks for two things
61 1. Is the repository DB up and running.
62 2. If yes, then is the meta data frozen.
63
64 If the rep DB is up and running and the meta data is
65 not frozen, returns a status of 'N'.
66 If the DB is not up, returns 'D'.
67 If the rep DB is up but the meta data is frozen,
68 it writes an error message.
69
70 NOTE : This function assumes that the Warehouse to the
71 Repository DB link is EDW_WH_TO_REP
72
73 Arguments
74 Input :
75 Type IN : NONE
76 Type OUT : NONE
77
78 Output : l_status_flag
79 Returns a flag of 'N' if the REP DB is up and the meta data
80 is not frozen.
81 If the REP DB is not up, returns a flag of 'Y'
82 Data Type : VARCHAR2(1)
83
84 */
85
86
87 /*
88 Name : replicate_tbl_all_site
89
90 Purpose : This function replicates a given table in the WH to all
91 source systems defined in the table EDW_SOURCE_INSTANCES
92 in the WH.
93
94 Arguments :
95 Input :
96 Type IN : p_table_name
97 This is the name of the table to be replicated.
98 Data Type VARCHAR2
99
100 Type OUT : NONE
101
102 Output :
103 l_status : If this function is able to replicate the table at all
104 the source sites, it returns a concat string of all
105 source system where table replication failed.
106 Ex : if source systems "source1" and "source5" have failed
107 to replicate the table,
108 l_status is "source1 source5"
109 If replication is successfull at all the sites, l_status
110 is an empty string.
111 Data Type : VARCHAR2(200)
112 */
113
114
115 /*
116
117 Name : replicate_tbl_to_site
118
119 Purpose : This function replicates a given table in the WH to the
120 source system passed as the input argument
121
122 Arguments :
123 Input :
124 Type IN : p_instance_code
125 This is the name of the source system where the table
126 needs to be replicated.
127 Data Type VARCHAR2
128
129 : p_table_name
130 This is the name of the table to be replicated.
131 Data Type VARCHAR2
132
133 Type OUT : NONE
134
135 Output :
136 l_status : 'TRUE' if replication is successfull
137 'FALSE' if the replication is unsuccessfull
138 Data Type : VARCHAR2(200)
139 */
140
141
142 FUNCTION check_db_status_all(x_instance_code OUT NOCOPY VARCHAR2) return boolean IS
143
144 l_status BOOLEAN := TRUE;
145 l_instance_code VARCHAR2(30);
146 l_db_link VARCHAR2(30);
147 l_progress VARCHAR2(3):= '000';
148 l_dummy VARCHAR2(30);
149 l_dummy_int NUMBER;
150 cid NUMBER;
151
152 l_temp number := 0;
153
154 CURSOR instances IS
155 SELECT instance_code, warehouse_to_instance_link
156 FROM edw_source_instances_vl
157 WHERE enabled_flag = 'Y';
158
159 BEGIN
160
161 x_instance_code:='';
162 l_progress := '010';
163 edw_misc_util.globalNamesOff;
164
165 -- Check to make sure that all the enabled OLTP sources are up and running
166
167 cid := DBMS_SQL.open_cursor;
168
169 OPEN instances;
170
171 LOOP
172 BEGIN
173 l_progress := '020';
174
175 FETCH instances INTO l_instance_code, l_db_link;
176 EXIT WHEN instances%NOTFOUND;
177
178 -- Store the instance name in the out parameter to return
179
180 DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
181 l_dummy_int := DBMS_SQL.EXECUTE(cid);
182
183 l_progress := '030';
184 EXCEPTION
185 when others then
186 l_status := FALSE;
187 x_instance_code:=x_instance_code||l_instance_code||' ';
188 edw_message_s.sql_error('check_db_status',l_progress,sqlcode);
189 END;
190 END LOOP;
191
192 CLOSE instances;
193
194 DBMS_SQL.close_cursor(cid);
195
196 return l_status;
197
198 EXCEPTION when others then
199 x_instance_code := null;
200 raise;
201
202 END check_db_status_all;
203
204
205 FUNCTION check_db_status_site(p_instance_code IN VARCHAR2) return boolean IS
206
207 l_status BOOLEAN := TRUE;
208 l_db_link VARCHAR2(30);
209 l_instance_code VARCHAR2(30);
210 l_progress VARCHAR2(3):= '000';
211 l_dummy VARCHAR2(30);
212 l_dummy_int NUMBER;
213 cid NUMBER;
214
215 l_temp number := 0;
216
217 CURSOR instances IS
218 SELECT instance_code, warehouse_to_instance_link
219 FROM edw_source_instances_vl
220 WHERE enabled_flag = 'Y'
221 AND instance_code=p_instance_code;
222
223 BEGIN
224
225 l_progress := '010';
226
227 edw_misc_util.globalNamesOff;
228 -- Check to make sure that all the enabled OLTP sources are up and running
229
230 cid := DBMS_SQL.open_cursor;
231
232 OPEN instances;
233
234 l_progress := '020';
235
236 FETCH instances INTO l_instance_code, l_db_link;
237
238 -- Store the instance name in the out parameter to return
239
240 DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
241 l_dummy_int:=DBMS_SQL.EXECUTE(cid);
242
243
244 l_progress := '030';
245
246 CLOSE instances;
247
248 DBMS_SQL.close_cursor(cid);
249
250 return l_status;
251
252 EXCEPTION
253 when others then
254 DBMS_SQL.close_cursor(cid);
255 l_status := FALSE;
256 edw_message_s.sql_error('check_db_status',l_progress,sqlcode);
257
258 END check_db_status_site;
259
260 FUNCTION check_repository_status RETURN VARCHAR2 IS
261
262 l_sql_stmt VARCHAR2(400);
263 l_sql_stmt1 VARCHAR2(400);
264 l_status_flag VARCHAR2(1) := 'N';
265 l_inst_mod_id NUMBER;
266 l_progress VARCHAR2(3) := '000';
267 l_dummy VARCHAR2(30);
268 l_dummy_int NUMBER;
269 cid NUMBER;
270 cid1 NUMBER;
271 cid2 NUMBER;
272 l_repchk NUMBER;
273 l_num NUMBER := 0;
274
275 BEGIN
276
277 -- Make sure that the repository is up and running
278 -- ASSUMPTION : That the db link from runtime to rep is fixed and is edw_wh_to_rep
279
280 l_progress := '010';
281 edw_misc_util.globalNamesOff;
282
283 cid := DBMS_SQL.open_cursor;
284 cid1 := DBMS_SQL.open_cursor;
285 cid2 := DBMS_SQL.open_cursor;
286
287
288 BEGIN
289
290 l_sql_stmt := ' SELECT 1 FROM dual@edw_wh_to_rep';
291 DBMS_SQL.PARSE(cid, l_sql_stmt ,dbms_sql.V7);
292
293 l_dummy_int :=DBMS_SQL.EXECUTE(cid);
294
295 l_progress := '015';
296
297 EXCEPTION
298 when others then
299 l_status_flag := 'D';
300 fnd_message.set_name('BIS', 'EDW_REPOSITORY_DOWN');
301 return l_status_flag;
302 END;
303
304 l_progress := '020';
305
306 -- Check Whether the Meta Data is Frozen
307 -- The repository can not be frozen for Flex Wizard to continue
308
309 /*
310
311 SELECT inst_mod_id
312 INTO l_inst_mod_id
313 FROM wh_inst_mods_v@EDW_WH_TO_REP
314 WHERE sw_mod_type_code = 'DTWH';
315
316 */
317
318 l_sql_stmt := 'SELECT inst_mod_id FROM wh_inst_mods_v@edw_wh_to_rep WHERE sw_mod_type_code = ''DTWH''';
319
320 DBMS_SQL.PARSE(cid1, l_sql_stmt ,dbms_sql.V7);
321 DBMS_SQL.DEFINE_COLUMN(cid1, 1, l_inst_mod_id);
322 l_dummy_int :=DBMS_SQL.EXECUTE(cid1);
323
324 l_num := DBMS_SQL.FETCH_ROWS(cid1);
325 DBMS_SQL.COLUMN_VALUE(cid1, 1, l_inst_mod_id);
326
327
328 -- l_status_flag := wh_inst_mods_pkg.freeze_check@EDW_WH_TO_REP(l_inst_mod_id);
329
330 l_sql_stmt1 := 'declare x VARCHAR2(1);' ||
331 'begin x := wh_inst_mods_pkg.freeze_check@EDW_WH_TO_REP('||l_inst_mod_id||');' ||
332 ':l_status_flag := x;'||
333 'end;';
334
335
336 dbms_sql.parse(cid2, l_sql_stmt1,dbms_sql.native);
337 dbms_sql.bind_variable(cid2, 'l_status_flag', l_status_flag, 1) ;
338 l_dummy_int := dbms_sql.execute(cid2);
339 dbms_sql.variable_value(cid2, 'l_status_flag', l_status_flag) ;
340
341
342 IF (l_status_flag = 'Y') THEN
343 fnd_message.set_name('BIS', 'EDW_REP_META_FROZEN');
344 END IF;
345
346 DBMS_SQL.close_cursor(cid);
347 DBMS_SQL.close_cursor(cid1);
348 DBMS_SQL.close_cursor(cid2);
349
350 return l_status_flag;
351
352 EXCEPTION
353 when others then
354
355 DBMS_SQL.close_cursor(cid);
356 DBMS_SQL.close_cursor(cid1);
357 DBMS_SQL.close_cursor(cid2);
358
359 edw_message_s.sql_error('check_repository_status',l_progress,sqlcode);
360
361
362 END check_repository_status;
363
364 FUNCTION replicate_tbl_all_site(
365 p_table_name in varchar2) return varchar2 IS
366
367 CURSOR instances IS
368 SELECT instance_code, warehouse_to_instance_link
369 FROM edw_source_instances_vl
370 WHERE enabled_flag = 'Y';
371
372 l_progress VARCHAR2(3) := '000';
373 l_db_link VARCHAR2(30);
374 l_instance_code VARCHAR2(30);
375 l_sql_stmt VARCHAR2(2000);
376 cid NUMBER;
377 l_dummy integer;
378 l_status VARCHAR2(200):='';
379
380 BEGIN
381 l_progress := '010';
382 edw_misc_util.globalNamesOff;
383 cid := DBMS_SQL.open_cursor;
384
385 OPEN instances;
386
387 LOOP
388 BEGIN
389 l_progress := '020';
390
391 FETCH instances INTO l_instance_code, l_db_link;
392
393 IF( instances%NOTFOUND ) THEN
394 exit;
395 END IF;
396
397 /* First delete existing date from the source db */
398
399 -- Check to see if the link points to itself
400
401
402 IF ( edw_db_status.is_dblink_to_itself(l_db_link) = FALSE ) THEN
403
404 l_progress := '030';
405
406 l_sql_stmt:=' DELETE '||p_table_name||'@'||l_db_link;
407
408 DBMS_SQL.PARSE(cid, l_sql_stmt, dbms_sql.native);
409
410 l_dummy := dbms_sql.execute(cid);
411
412 l_progress := '040';
413
414 l_sql_stmt:=' INSERT INTO '||p_table_name||'@'||l_db_link||' SELECT * FROM '||p_table_name;
415 DBMS_SQL.PARSE(cid,l_sql_stmt, dbms_sql.native);
416
417 l_dummy := dbms_sql.execute(cid);
418
419 END IF;
420
421 EXCEPTION
422 when others then
423 edw_message_s.sql_error('replicate_table_to_all_site',l_progress,sqlcode);
424 l_status:=l_status||' '||l_instance_code||' ';
425 END;
426
427 END LOOP;
428
429 DBMS_SQL.close_cursor(cid);
430
431 CLOSE instances;
432
433 return l_status;
434
435 END replicate_tbl_all_site;
436
437 FUNCTION replicate_tbl_to_site(
438 p_instance_code in varchar2,
439 p_table_name in varchar2) return VARCHAR2 IS
440
441 CURSOR instances IS
442 SELECT warehouse_to_instance_link
443 FROM edw_source_instances_vl
444 WHERE enabled_flag = 'Y'
445 AND instance_code=p_instance_code;
446
447 l_progress VARCHAR2(3) := '000';
448 l_db_link VARCHAR2(30);
449 l_sql_stmt VARCHAR2(2000);
450 cid NUMBER;
451 l_dummy integer;
452 l_status VARCHAR2(200):='TRUE';
453
454 BEGIN
455
456 l_progress := '010';
457
458 cid := DBMS_SQL.open_cursor;
459
460 edw_misc_util.globalNamesOff;
461 OPEN instances;
462
463 l_progress := '020';
464
465 FETCH instances INTO l_db_link;
466
467 -- Check to see if the link points to itself
468
469 IF ( edw_db_status.is_dblink_to_itself(l_db_link) = FALSE ) THEN
470 l_progress := '020';
471
472 l_sql_stmt:=' DELETE '||p_table_name||'@'||l_db_link;
473
474 DBMS_SQL.PARSE(cid, l_sql_stmt, dbms_sql.native);
475 l_dummy := dbms_sql.execute(cid);
476
477 l_progress := '040';
478
479 l_sql_stmt:=' INSERT INTO '||p_table_name||'@'||l_db_link
480 ||' SELECT * FROM '||p_table_name;
481
482 DBMS_SQL.PARSE(cid,l_sql_stmt, dbms_sql.native);
483
484 l_dummy := dbms_sql.execute(cid);
485
486 DBMS_SQL.close_cursor(cid);
487
488 END IF;
489
490 CLOSE instances;
491
492 return l_status;
493
494
495 EXCEPTION
496 when others then
497 DBMS_SQL.close_cursor(cid);
498 edw_message_s.sql_error('replicate_table_to_all_site1',l_progress,sqlcode);
499 l_status:='FALSE';
500 return l_status;
501
502 END replicate_tbl_to_site;
503
504
505 FUNCTION is_dblink_to_itself(p_dblink IN VARCHAR2) RETURN BOOLEAN
506
507 IS
508 l_host VARCHAR2(2000);
509 l_db_name VARCHAR2(2000);
510 l_stmt varchar2(5000);
511 TYPE CurTyp IS REF CURSOR;
512 cv CurTyp;
513 BEGIN
514
515 edw_misc_util.globalNamesOff;
516
517 l_stmt:='select upper(db.name) from v$database@'||p_dblink ||' db';
518 open cv for l_stmt ;
519 fetch cv into l_host;
520 close cv;
521
522 SELECT upper(db.name)
523 INTO l_db_name
524 FROM v$database db;
525
526 IF (l_host = l_db_name) THEN
527 RETURN TRUE;
528 ELSE
529 RETURN FALSE;
530 END IF;
531
532 EXCEPTION
533 when others then
534 RETURN FALSE;
535
536 END is_dblink_to_itself;
537
538
539 END EDW_DB_STATUS;
[Home] [Help]