1 package body BSC_AW_UTILITY AS
2 /*$Header: BSCAWUTB.pls 120.34 2006/06/16 21:32:45 vsurendr ship $*/
3
4 function in_array(
5 p_array dbms_sql.varchar2_table,
6 p_value varchar2
7 ) return boolean is
8 Begin
9 for i in 1..p_array.count loop
10 if p_array(i)=p_value then
11 return true;
12 end if;
13 end loop;
14 return false;
15 Exception when others then
16 log('Exception in in_array '||sqlerrm);
17 raise;
18 End;
19
20 function in_array(
21 p_array dbms_sql.number_table,
22 p_value number
23 ) return boolean is
24 Begin
25 for i in 1..p_array.count loop
26 if p_array(i)=p_value then
27 return true;
28 end if;
29 end loop;
30 return false;
31 Exception when others then
32 log('Exception in in_array '||sqlerrm);
33 raise;
34 End;
35
36 function in_array(
37 p_array varchar2_table,
38 p_value varchar2
39 ) return boolean is
40 l_val varchar2(200);
41 Begin
42 l_val:=p_array(p_value);
43 return true;
44 Exception when others then
45 return false;
46 End;
47
48 function get_array_index(
49 p_array dbms_sql.varchar2_table,
50 p_value varchar2
51 ) return number is
52 Begin
53 for i in 1..p_array.count loop
54 if p_array(i)=p_value then
55 return i;
56 end if;
57 end loop;
58 return null;
59 Exception when others then
60 log('Exception in get_array_index '||sqlerrm);
61 raise;
62 End;
63
64 function get_array_index(
65 p_array dbms_sql.number_table,
66 p_value number
67 ) return number is
68 Begin
69 for i in 1..p_array.count loop
70 if p_array(i)=p_value then
71 return i;
72 end if;
73 end loop;
74 return null;
75 Exception when others then
76 log('Exception in get_array_index '||sqlerrm);
77 raise;
78 End;
79
80 /*
81 given a set of options in a string like DEBUG,LOG=N,TABLESPACE=USER_DATA etc, we will pass in DEBUG and this api will return the value of
82 the parameter back
83 if param=DEBUG, return Y
84 if param=TABLESPACE return USER_DATA
85 */
86 function get_parameter_value(p_string varchar2,p_parameter varchar2,p_separator varchar2) return varchar2 is
87 l_length number;
88 l_start number;
89 l_end number;
90 --
91 l_values value_tb;
92 Begin
93 parse_parameter_values(p_string,p_separator,l_values);
94 return get_parameter_value(l_values,p_parameter);
95 Exception when others then
96 log('Exception in get_parameter_value '||sqlerrm);
97 raise;
98 End;
99
100 --this is the 2nd implementation...given a table of options, return the value
101 function get_parameter_value(p_options value_tb,p_parameter varchar2) return varchar2 is
102 Begin
103 for i in 1..p_options.count loop
104 if p_options(i).parameter=p_parameter then
105 return p_options(i).value;
106 end if;
107 end loop;
108 return null;
109 Exception when others then
110 log('Exception in get_parameter_value '||sqlerrm);
111 raise;
112 End;
113
114 function get_parameter_value(p_parameter varchar2) return varchar2 is
115 Begin
116 return get_parameter_value(g_options,p_parameter);
117 Exception when others then
118 log('Exception in get_parameter_value '||sqlerrm);
119 raise;
120 End;
121
122
123 /*
124 this procedure will parse the option string and put the values into value_tb table
125 */
126 procedure parse_parameter_values(
127 p_string varchar2,
128 p_separator varchar2,
129 p_values out nocopy value_tb
130 ) is
131 --
132 l_parse_strings dbms_sql.varchar2_table;
133 Begin
134 parse_parameter_values(p_string,p_separator,l_parse_strings);
135 for i in 1..l_parse_strings.count loop
136 resolve_into_value_r(l_parse_strings(i),p_values(p_values.count+1));
137 end loop;
138 Exception when others then
139 log('Exception in parse_parameter_values '||sqlerrm);
140 raise;
141 End;
142
143 --given TABLESPACE=USER_DATA or DEBUG LOG it will resolve it into value_r record
144 procedure resolve_into_value_r(
145 p_string varchar2,
146 p_value out nocopy value_r) is
147 l_end number;
148 l_len number;
149 Begin
150 l_end:=instr(p_string,'=');
151 if l_end=0 then
152 p_value.parameter:=p_string;
153 p_value.value:='Y';
154 else
155 l_len:=length(p_string);
156 p_value.parameter:=substr(p_string,1,l_end-1);
157 --we can have lowest level,zero code=,. here l_end=l_len
158 --open:do we have 'N' or null. we will keep null. the calling routine can interpret it as N
159 if l_end=l_len then
160 p_value.value:=null;
161 else
162 p_value.value:=substr(p_string,l_end+1,l_len-l_end);
163 end if;
164 end if;
165 Exception when others then
166 log('Exception in resolve_into_value_r '||sqlerrm);
167 raise;
168 End;
169
170 /*
171 earlier, we just had
172 parse_parameter_values(p_string,p_separator,l_values);
173 for i in 1..l_values.count loop
174 p_values(p_values.count+1):=l_values(i).parameter;
175 end loop;
176 this will not do. parse_parameter_values(p_string,p_separator,l_values); breaks into parameter and value.
177 which means if it encounters an "=" sign, its split into a parameter + value. this will not do for us.
178 we have to strictly break up the string according to p_separator
179 */
180 procedure parse_parameter_values(
181 p_string varchar2,
182 p_separator varchar2,
183 p_values out nocopy dbms_sql.varchar2_table
184 ) is
185 --
186 l_start number;
187 l_end number;
188 l_len number;
189 Begin
190 if p_string is null or p_string='' then
191 return;
192 end if;
193 l_len:=length(p_string);
194 if l_len<=0 then
195 return;
196 end if;
197 l_start:=1;
198 loop
199 l_end:=instr(p_string,p_separator,l_start);
200 if l_end=0 then
201 l_end:=l_len+1;
202 end if;
203 if l_end>l_start then
204 p_values(p_values.count+1):=ltrim(rtrim(substr(p_string,l_start,(l_end-l_start))));
205 end if;
206 l_start:=l_end+length(p_separator);
207 --we could have p_string as "recursive,multi level," a comma at the end
208 if l_end>=l_len or l_start>=l_len then
209 exit;
210 end if;
211 end loop;
212 Exception when others then
213 log('Exception in parse_parameter_values '||sqlerrm);
214 raise;
215 End;
216
217 function get_min(num1 number,num2 number) return number is
218 Begin
219 if num1<num2 then
220 return num1;
221 else
222 return num2;
223 end if;
224 Exception when others then
225 log('Exception in get_min '||sqlerrm);
226 raise;
227 End;
228
229 function contains(p_text varchar2,p_check varchar2) return boolean is
230 Begin
231 if instr(p_text,p_check)>0 then
232 return true;
233 end if;
234 return false;
235 Exception when others then
236 log('Exception in contains '||sqlerrm);
237 raise;
238 End;
239
240 --used to populate created by etc
241 function get_who return number is
242 Begin
243 return 0;
244 Exception when others then
245 log('Exception in get_who '||sqlerrm);
246 raise;
247 End;
248
249 procedure delete_aw_object(p_object varchar2) is
250 Begin
251 if p_object is not null then
252 bsc_aw_dbms_aw.execute('delete '||p_object);
253 end if;
254 Exception when others then
255 null;--may try to delete an object that does not exist
256 End;
257
258 procedure execute_ddl_ne(p_stmt varchar2) is
259 Begin
260 if g_debug then
261 log(p_stmt);
262 end if;
263 execute immediate p_stmt;
264 Exception when others then
265 log('Exception in execute_ddl_ne '||sqlerrm);
266 End;
267
268 procedure execute_ddl(p_stmt varchar2) is
269 Begin
270 if g_debug then
271 log(p_stmt);
272 end if;
273 execute immediate p_stmt;
274 Exception when others then
275 log('Exception in execute_ddl '||sqlerrm);
276 raise;
277 End;
278
279 procedure execute_stmt(p_stmt varchar2) is
280 Begin
281 if g_debug then
282 log_s('@ '||p_stmt||'(S:'||get_time);
283 end if;
284 execute immediate p_stmt;
285 if g_debug then
286 log(',E:'||get_time||') Processed '||sql%rowcount||' rows ');
287 end if;
288 Exception when others then
289 log('Exception in execute_stmt '||sqlerrm);
290 raise;
291 End;
292
293 procedure execute_stmt_ne(p_stmt varchar2) is
294 Begin
295 execute_stmt(p_stmt);
296 Exception when others then
297 null;
298 End;
299
300 procedure delete_table(p_table varchar2,p_where varchar2) is
301 --
302 l_stmt varchar2(1000);
303 Begin
304 if g_debug then
305 log('delete '||p_table||' where '||p_where);
306 end if;
307 if p_where is null then
308 l_stmt:='delete '||p_table;
309 else
310 l_stmt:='delete '||p_table||' where '||p_where;
311 end if;
312 if g_debug then
313 log(l_stmt||get_time);
314 end if;
315 execute immediate l_stmt;
316 if g_debug then
317 log('Deleted '||sql%rowcount||' rows '||get_time);
318 end if;
319 Exception when others then
320 log('Exception in delete_table '||sqlerrm);
321 raise;
322 End;
323
324 /*
325 this api will normalize a denorm relation. if the relation is already normalized,
326 no change will happen. so we can pass both denorm arrays and normal arrays
327 for each parent, check each of the children to see if the children have their
328 own children. if so, remove children's children from the list
329 parent child
330 year qtr,month,week,day
331 qtr month,day
332 month day
333 week day
334 qtr has month and day as its children. so remove month and day from year...
335 */
336 procedure normalize_denorm_relation(p_relation in out nocopy parent_child_tb) is
337 l_relation parent_child_tb;
338 l_count number;
339 Begin
340 for i in 1..p_relation.count loop
341 if p_relation(i).child is not null and p_relation(i).status is null then
342 for j in 1..p_relation.count loop
343 if p_relation(j).parent=p_relation(i).child and p_relation(j).status is null then
344 for k in 1..p_relation.count loop
345 if p_relation(k).parent=p_relation(i).parent and p_relation(k).child=p_relation(j).child then
346 p_relation(k).status:='R'; --R means remove
347 exit;
348 end if;
349 end loop;
350 end if;
351 end loop;
352 end if;
353 end loop;
354 l_relation:=p_relation;
355 p_relation.delete;
356 l_count:=0;
357 for i in 1..l_relation.count loop
358 if l_relation(i).status is null then
359 l_count:=l_count+1;
360 p_relation(l_count):=l_relation(i);
361 end if;
362 end loop;
363 Exception when others then
364 log('Exception in normalize_denorm_relation '||sqlerrm);
365 raise;
366 End;
367
368 --p_type is 'program' or 'aggmap'
369 procedure make_stmt_for_aw(p_program varchar2,p_stmt in out nocopy varchar2,p_type varchar2) is
370 Begin
371 p_stmt:=p_type||' joinlines(obj('||p_type||' '''||p_program||'''),'''||p_stmt||''')';
372 Exception when others then
373 log('Exception in make_stmt_for_aw '||sqlerrm);
374 raise;
375 End;
376
377 procedure add_g_commands(p_commands in out nocopy dbms_sql.varchar2_table,p_command varchar2) is
378 Begin
379 p_commands(p_commands.count+1):=p_command;
380 Exception when others then
381 log('Exception in add_g_commands '||sqlerrm);
382 raise;
383 End;
384
385 function get_g_commands(p_commands dbms_sql.varchar2_table,p_index number) return varchar2 is
386 Begin
387 if p_commands.count>0 then
388 if p_index is null then --latest
389 return p_commands(p_commands.count);
390 else
391 return p_commands(p_index);
392 end if;
393 else
394 return null;
395 end if;
396 Exception when others then
397 log('Exception in get_g_commands '||sqlerrm);
398 raise;
399 End;
400
401 procedure trim_g_commands(p_commands in out nocopy dbms_sql.varchar2_table,p_trim number,p_add varchar2) is
402 Begin
403 p_commands(p_commands.count):=substr(p_commands(p_commands.count),1,length(p_commands(p_commands.count))-p_trim)||p_add;
404 Exception when others then
405 log('Exception in trim_g_commands '||sqlerrm);
406 raise;
407 End;
408
409 procedure exec_program_commands(p_program varchar2,p_commands dbms_sql.varchar2_table) is
410 Begin
411 --the first stmt is 'dfn abc program' this does not have "joinlines"
412 --first try and drop the programs if it exists.
413 bsc_aw_dbms_aw.execute_ne('delete '||p_program);
414 exec_aw_program_aggmap(p_program,p_commands,'program');
415 bsc_aw_dbms_aw.execute('compile '||p_program);
416 Exception when others then
417 log('Exception in exec_program_commands '||sqlerrm);
418 raise;
419 End;
420
421 procedure exec_aggmap_commands(p_aggmap varchar2,p_commands dbms_sql.varchar2_table) is
422 Begin
423 --the first stmt is 'dfn abc program' this does not have "joinlines"
424 bsc_aw_dbms_aw.execute_ne('delete '||p_aggmap);
425 exec_aw_program_aggmap(p_aggmap,p_commands,'aggmap');
426 bsc_aw_dbms_aw.execute('compile '||p_aggmap);
427 Exception when others then
428 log('Exception in exec_aggmap_commands '||sqlerrm);
429 raise;
430 End;
431
432 --p_type is 'program' or 'aggmap'
433 procedure exec_aw_program_aggmap(p_name varchar2,p_commands dbms_sql.varchar2_table,p_type varchar2) is
434 l_commands varchar2(8000);
435 Begin
436 bsc_aw_dbms_aw.execute(p_commands(1));
437 for i in 2..p_commands.count loop
438 l_commands:=p_commands(i);
439 make_stmt_for_aw(p_name,l_commands,p_type);
440 bsc_aw_dbms_aw.execute(l_commands);
441 end loop;
442 Exception when others then
443 log('Exception in exec_aw_program_aggmap '||sqlerrm);
444 raise;
445 End;
446
447 procedure dmp_g_options(p_options value_tb) is
448 Begin
449 log('Options :-');
450 for i in 1..p_options.count loop
451 log(p_options(i).parameter||'='||p_options(i).value);
452 end loop;
453 Exception when others then
454 log('Exception in dmp_g_options '||sqlerrm);
455 raise;
456 End;
457
458 /*
459 called from bsc_aw_adapter_kpi.create_base_table_sql
460 this procedure strips out the aggregation functions from the formula.
461 the base table may have the same number of keys as the dim set in this case, there is no need to do the aggregation
462 when loading the cube from the base table. so we need to remove the agg functions
463 bsc supports
464 Apply aggregation method to the each element of the formula, e.g.: SUM(source_column1)/SUM(source_column2)
465 Apply aggregation method to the overall formula, e.g.: SUM(source_column1/source_column2)
466 Formulas between 2 calculated Measures e.g.: SUM(source_col1/source_col2)/AVG(source_col3+source_col4)
467
468 for case II, we simply remove string till the first (
469
470 if the agg function is count(), we simply replace it with 1. since there is no group by, count() will anyway be returning 1.
471 this is not working. getting this error
472 ORA-34738: (NOUPDATE) A severe problem has been detected. Analytic workspace operations have been disabled.
473 ORA-06512: at "APPS.BSC_AW_UTILITY", line 466
474 ORA-06512: at "APPS.BSC_AW_LOAD", line 112
475 ORA-37666: ** SYSTEM ERROR xsSqlImport01 **
476 A severe problem has been detected. Please save your work via EXPORT or OUTFILE and exit as soon as possible.
477 As a safety measure, analytic workspace operations have been disabled. Call Oracle OLAP technical support.
478 ORA-06512: at line 7
479 somehow, hardcoded 1 is interpreted as true / false 1.
480 so we will see if count is in the formula. if yes, we will go for groupby
481 */
482 procedure parse_out_agg_function(p_formula varchar2,p_noagg_formula out nocopy varchar2) is
483 --
484 l_agg_function varchar2(100);
485 Begin
486 l_agg_function:=ltrim(rtrim(substr(p_formula,1,instr(p_formula,'(')-1)));
487 if lower(l_agg_function)='count' then
488 p_noagg_formula:='(1)';
489 else
490 p_noagg_formula:=substr(p_formula,instr(p_formula,'('));
491 end if;
492 Exception when others then
493 log('Exception in parse_out_agg_function '||sqlerrm);
494 raise;
495 End;
496
497 /*
498 given a number array, get the max
499 */
500 function get_max(p_array dbms_sql.number_table) return number is
501 l_max number;
502 Begin
503 if p_array.count<=0 then
504 return null;
505 end if;
506 l_max:=p_array(1);
507 for i in 2..p_array.count loop
508 if p_array(i)>l_max then
509 l_max:=p_array(i);
510 end if;
511 end loop;
512 return l_max;
513 Exception when others then
514 log('Exception in get_max '||sqlerrm);
515 raise;
516 End;
517
518
519 function does_table_have_data(p_table varchar2,p_where varchar2) return varchar2 is
520 --
521 TYPE CurTyp IS REF CURSOR;
522 cv CurTyp;
523 l_res number;
524 Begin
525 g_stmt:='select 1 from '||p_table;
526 if p_where is not null then
527 g_stmt:=g_stmt||' where '||p_where||' and rownum=1';
528 else
529 g_stmt:=g_stmt||' where rownum=1';
530 end if;
531 if g_debug then
532 log(g_stmt);
533 end if;
534 open cv for g_stmt;
535 fetch cv into l_res;
536 close cv;
537 if g_debug then
538 log('result='||l_res);
539 end if;
540 if l_res=1 then
541 return 'Y';
542 else
543 return 'N';
544 end if;
545 Exception when others then
546 log('Exception in does_table_have_data '||sqlerrm);
547 raise;
548 End;
549
550 /*
551 this function is used to see if the aggregation is a simple aggregation or if this is a formula
552 its a formula when we have averaqge at the lowest level
553 */
554 function is_std_aggregation_function(p_agg_formula varchar2) return varchar2 is
555 Begin
556 if g_debug then
557 log('In is_std_aggregation_function, p_agg_formula='||p_agg_formula);
558 end if;
559 if upper(p_agg_formula)='SUM' or upper(p_agg_formula)='AVERAGE' or upper(p_agg_formula)='MAX' or upper(p_agg_formula)='MIN' or
560 upper(p_agg_formula)='COUNT' then
561 if g_debug then
562 log('Yes');
563 end if;
564 return 'Y';
565 end if;
566 if g_debug then
567 log('No');
568 end if;
569 return 'N';
570 Exception when others then
571 log('Exception in is_std_aggregation_function '||sqlerrm);
572 raise;
573 End;
574
575 /*this sees if an aggregation formula can be safely partitioned even if there are aggregations */
576 function is_PT_aggregation_function(p_agg_formula varchar2) return varchar2 is
577 Begin
578 if upper(p_agg_formula)='SUM' or upper(p_agg_formula)='MAX' or upper(p_agg_formula)='MIN' then
579 return 'Y';
580 end if;
581 return 'N';
582 Exception when others then
583 log('Exception in is_pt_aggregation_function '||sqlerrm);
584 raise;
585 End;
586
587 /*currently, only sum has CC. not able to get aggcount to work ie create cube <...> with aggcount and then aggregate cube using aggmap */
588 function is_CC_aggregation_function(p_agg_formula varchar2) return varchar2 is
589 Begin
590 if upper(p_agg_formula)='SUM' then
591 return 'Y';
592 end if;
593 return 'N';
594 Exception when others then
595 log('Exception in is_CC_aggregation_function '||sqlerrm);
596 raise;
597 End;
598
599 /*
600 if we have non-std agg, we execute it as cube=formula. there is no aggmap
601 so we look for std agg : average
602 */
603 function is_avg_aggregation_function(p_agg_formula varchar2) return varchar2 is
604 Begin
605 if upper(p_agg_formula)='AVERAGE' or upper(p_agg_formula)='AVG' then
606 return 'Y';
607 else
608 return 'N';
609 end if;
610 Exception when others then
611 log('Exception in is_avg_aggregation_function '||sqlerrm);
612 raise;
613 End;
614
615 /*
616 this function is used by aggregation module to see if a measure is a part of an agg formula
617 the aggregtion module will then substitute the measure with the cube name
618
619 p_strng is the original string
620 p_text thisis the text to search for
621 p_location a table of all locations in p_string where p_text occurs
622 */
623 function is_string_present(
624 p_string varchar2,
625 p_text varchar2,
626 p_location out nocopy dbms_sql.number_table
627 ) return boolean is
628 --
629 l_start number;
630 l_end number;
631 l_len number;
632 l_char varchar2(10);
633 l_flag boolean;
634 Begin
635 --there can be multiple occurances of text in string
636 l_start:=1;
637 l_len:=length(p_string);
638 loop
639 l_start:=instr(p_string,p_text,l_start);
640 if l_start=0 then
641 exit;
642 else --l_start>1
643 l_flag:=true;
644 l_end:=l_start+length(p_text);
645 if l_start>1 then
646 l_char:=substr(p_string,l_start-1,1);
647 if is_ascii(l_char) then
648 l_flag:=false;
649 end if;
650 end if;
651 if l_flag and l_end<l_len then
652 l_char:=substr(p_string,l_end,1);
653 if is_ascii(l_char) then
654 l_flag:=false;
655 end if;
656 end if;
657 if l_flag then --we did find p_text in p_string
658 p_location(p_location.count+1):=l_start;
659 end if;
660 l_start:=l_end;
661 end if;
662 end loop;
663 if p_location.count>0 then
664 return true;
665 else
666 return false;
667 end if;
668 Exception when others then
669 log('Exception in is_string_present '||sqlerrm);
670 raise;
671 End;
672
673 /*
674 this fucntion sees if a character is a special char or a variable name continuing
675 example
676 sees if the char is between A and Z, 0 and 9 or _
677
678 p_char is one char
679 */
680 function is_ascii(p_char varchar2) return boolean is
681 l_ascii_a number;
682 l_ascii_z number;
683 l_ascii_0 number;
684 l_ascii_9 number;
685 l_char number;
686 Begin
687 l_ascii_a:=ascii('A');
688 l_ascii_z:=ascii('Z');
689 l_ascii_0:=ascii('0');
690 l_ascii_9:=ascii('9');
691 l_char:=ascii(upper(p_char));
692 if p_char='_' then
693 return true;
694 elsif is_in_between(l_char,l_ascii_a,l_ascii_z) then
695 return true;
696 elsif is_in_between(l_char,l_ascii_0,l_ascii_9) then
697 return true;
698 else
699 return false;
700 end if;
701 Exception when others then
702 log('Exception in is_ascii '||sqlerrm);
703 raise;
704 End;
705
706 function is_in_between(p_input number,p_left number,p_right number) return boolean is
707 Begin
708 if p_left>p_right then
709 if p_input>=p_right and p_input<=p_left then
710 return true;
711 else
712 return false;
713 end if;
714 else
715 if p_input<=p_right and p_input>=p_left then
716 return true;
717 else
718 return false;
719 end if;
720 end if;
721 Exception when others then
722 log('Exception in is_in_between '||sqlerrm);
723 raise;
724 End;
725
726 /*
727 this procedure is used by the agg module to replace the measure names with the cube names
728 database replace procedure does not support replacing the occurance of a string at a particular location. we cannot
729 blindly replace. example, we may have formula : cons_revenue / revenue + cost
730 if we have to replace revenue with "cube_revenue", we cannot do replace(agg_formula,'revenue','cube_revenue'). it will make
731 this cons_cube_revenue + cube_revenue + cost. this is wrong. so we have this procedure which will replace at the
732 secified locations
733
734 if a string is mm + abcd/123 + abcd/mnq
735 we need to relace abcd with cube_abcd
736 we will first break it into parts without abcd
737 l_string_parts(1):=mm +
738 l_string_parts(2):=/123 +
739 l_string_parts(3):=/mnq
740 then we simply put them together as
741 l_string_parts(1)||cube_abcd||l_string_parts(2)||cube_abcd||l_string_parts(3)
742 */
743 procedure replace_string(
744 p_string in out nocopy varchar2,
745 p_old_text varchar2,
746 p_new_text varchar2,
747 p_start_array dbms_sql.number_table
748 ) is
749 --
750 l_string_parts dbms_sql.varchar2_table;
751 l_start number;
752 l_len number;
753 l_length number;
754 Begin
755 l_start:=1;
756 l_len:=length(p_old_text);
757 l_length:=length(p_string);
758 for i in 1..p_start_array.count loop
759 l_string_parts(l_string_parts.count+1):=substr(p_string,l_start,p_start_array(i)-l_start);
760 l_start:=p_start_array(i)+l_len;
761 end loop;
762 --now we add the last part of the strin
763 if l_start<l_length then
764 l_string_parts(l_string_parts.count+1):=substr(p_string,l_start,l_length-l_start+1);
765 else
766 l_string_parts(l_string_parts.count+1):=null;
767 end if;
768 --now reform the string , this time with the new string
769 p_string:=null;
770 for i in 1..l_string_parts.count-1 loop
771 p_string:=p_string||l_string_parts(i)||p_new_text;
772 end loop;
773 --append the last part
774 p_string:=p_string||l_string_parts(l_string_parts.count);
775 Exception when others then
776 log('Exception in replace_string '||sqlerrm);
777 raise;
778 End;
779
780 function get_adv_sum_profile return number is
781 --
782 l_adv_sum_profile number;
783 Begin
784 l_adv_sum_profile:=bsc_aw_utility.get_parameter_value(g_options,'SUMMARIZATION LEVEL');
785 if l_adv_sum_profile is null or l_adv_sum_profile=0 then
786 l_adv_sum_profile:=1000000;
787 end if;
788 return l_adv_sum_profile;
789 Exception when others then
790 log('Exception in get_adv_sum_profile '||sqlerrm);
791 raise;
792 End;
793 ---------------------------
794
795 procedure truncate_table(p_table varchar2) is
796 --
797 l_owner varchar2(100);
798 l_stmt varchar2(1000);
799 Begin
800 if instr(p_table,'.')>0 then
801 l_stmt:='truncate table '||p_table;
802 else
803 l_owner:=get_table_owner(p_table);
804 l_stmt:='truncate table '||l_owner||'.'||p_table;
805 end if;
806 if g_debug then
807 log(l_stmt||get_time);
808 end if;
809 execute immediate l_stmt;
810 if g_debug then
811 log(get_time);
812 end if;
813 Exception when others then
814 log('Exception in truncate_table '||sqlerrm);
815 raise;
816 End;
817
818 function get_table_owner(p_table varchar2) return varchar2 is
819 --
820 cursor c1 is select table_owner from user_synonyms where synonym_name=upper(p_table);
821 l_owner varchar2(100);
822 Begin
823 if instr(p_table,'.')>0 then
824 l_owner:=substr(p_table,1,instr(p_table,'.')-1);
825 return l_owner;
826 end if;
827 open c1;
828 fetch c1 into l_owner;
829 close c1;
830 if l_owner is null then
831 l_owner:=get_apps_schema_name;
832 end if;
833 return l_owner;
834 Exception when others then
835 log('Exception in get_table_owner '||sqlerrm);
836 return null;
837 End;
838
839 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
840 l_apps_schema_name VARCHAR2(30);
841 CURSOR c_apps_schema_name IS
842 SELECT oracle_username
843 FROM fnd_oracle_userid WHERE oracle_id
844 BETWEEN 900 AND 999 AND read_only_flag = 'U';
845 BEGIN
846 OPEN c_apps_schema_name;
847 FETCH c_apps_schema_name INTO l_apps_schema_name;
848 CLOSE c_apps_schema_name;
849 RETURN l_apps_schema_name;
850 EXCEPTION
851 WHEN OTHERS THEN
852 RETURN NULL;
853 END get_apps_schema_name;
854
855 ---------------------------
856 procedure init_all(p_debug boolean) is
857 Begin
858 g_debug:=p_debug;
859 if nvl(get_parameter_value('TRACE'),'N')='Y' then
860 set_aw_trace;
861 end if;
862 Exception when others then
863 raise;
864 End;
865
866 /*
867 this will init all procedures
868 this is useful if we dont need to worry about which all packs's init have been called.
869 useful for dbms jobs
870 */
871 procedure init_all_procedures is
872 Begin
873 if get_parameter_value(g_options,'DEBUG LOG')='Y'
874 or bsc_aw_utility.g_log_level>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
875 g_debug:=true;
876 else
877 g_debug:=false;
878 end if;
879 init_all(g_debug);
880 bsc_aw_adapter_dim.init_all;
881 bsc_aw_adapter_kpi.init_all;
882 bsc_aw_bsc_metadata.init_all;
883 bsc_metadata.init_all;
884 bsc_aw_load_dim.init_all;
885 bsc_aw_load_kpi.init_all;
886 bsc_aw_dbms_aw.init_all;
887 bsc_aw_md_api.init_all;
888 bsc_aw_md_wrapper.init_all;
889 bsc_aw_management.init_all;
890 bsc_aw_calendar.init_all;
891 bsc_aw_adapter.init_all;
892 bsc_aw_load.init_all;
893 bsc_aw_read.init_all;
894 Exception when others then
895 raise;
896 End;
897
898
899 --write to same line
900 procedure log_s(p_message varchar2) is
901 Begin
902 write_to_file('LOG',p_message,false);
903 Exception when others then
904 null;
905 End;
906
907 procedure log(p_message varchar2) is
908 Begin
909 write_to_file('LOG',p_message,true);
910 Exception when others then
911 null;
912 End;
913
914 procedure log_n(p_message varchar2) is
915 Begin
916 log(' ');
917 log(p_message);
918 Exception when others then
919 null;
920 End;
921
922 function get_time return varchar2 is
923 Begin
924 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')||' ';
925 Exception when others then
926 null;
927 End;
928
929 procedure write_to_file(p_type varchar2,p_message varchar2,p_new_line boolean) is
930 l_len number;
931 l_start number:=1;
932 l_end number:=1;
933 last_reached boolean:=false;
934 Begin
935 if p_message is null or p_message='' then
936 return;
937 end if;
938 l_len:=nvl(length(p_message),0);
939 if l_len <=0 then
940 return;
941 end if;
942 loop
943 l_end:=l_start+250;
944 if l_end >= l_len then
945 l_end:=l_len;
946 last_reached:=true;
947 end if;
948 if p_new_line then
949 if p_type='LOG' then
950 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_message, l_start, 250));
951 else
952 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,substr(p_message, l_start, 250));
953 end if;
954 else
955 if p_type='LOG' then
956 FND_FILE.PUT(FND_FILE.LOG,substr(p_message, l_start, 250)||' ');
957 else
958 FND_FILE.PUT(FND_FILE.OUTPUT,substr(p_message, l_start, 250)||' ');
959 end if;
960 end if;
961 l_start:=l_start+250;
962 if last_reached then
963 exit;
964 end if;
965 end loop;
966 log_fnd(p_message,bsc_aw_utility.g_log_level);
967 Exception when others then
968 null;
969 End;
970
971 procedure log_fnd(p_message varchar2,p_severity number) is
972 l_table dbms_sql.varchar2_table;
973 Begin
974 if p_severity>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
975 convert_varchar2_to_table(p_message,3990,l_table);
976 for i in 1..l_table.count loop
977 FND_LOG.STRING(p_severity,'BSC-AW',l_table(i));
978 end loop;
979 end if;
980 Exception when others then
981 null;
982 End;
983
984 procedure open_file(p_object_name varchar2) is
985 l_dir varchar2(200);
986 Begin
987 l_dir:=null;
988 l_dir:=get_parameter_value('UTL_FILE_LOG');
989 if l_dir is null then
990 l_dir:=fnd_profile.value('UTL_FILE_LOG');
991 end if;
992 if l_dir is null then
993 l_dir:='/sqlcom/log';
994 end if;
995 FND_FILE.PUT_NAMES(p_object_name||'.log',p_object_name||'.out',l_dir);
996 log('File Directory is '||l_dir);
997 if l_dir is null then
998 log('Please set profile option UTL_FILE_LOG to point to a valid directory the database can write to. Only then can you see the log files');
999 log('generated from parallel jobs(when Debug Mode is true) for load and aggregations');
1000 end if;
1001 Exception when others then
1002 raise;
1003 End;
1004
1005 procedure create_temp_tables is
1006 Begin
1007 g_stmt:='create global temporary table bsc_aw_temp_vn (name varchar2(400),id number)';
1008 execute_ddl_ne(g_stmt);--execute_ddl_ne = ignore exception
1009 g_stmt:='create global temporary table bsc_aw_temp_pc(parent varchar2(300),child varchar2(300),id number)';
1010 execute_ddl_ne(g_stmt);--execute_ddl_ne = ignore exception
1011 g_stmt:='create global temporary table bsc_aw_temp_cv(change_vector_min_value number,change_vector_max_value number,change_vector_base_table varchar2(30))'; --for change vector
1012 execute_ddl_ne(g_stmt);--execute_ddl_ne = ignore exception
1013 --tables to normalize a denorm hierarchy
1014 --for parent, child, we can re-use bsc_aw_temp_pc
1015 --for count of each dim value, we can use bsc_aw_temp_vn
1016 Exception when others then
1017 log_n('Exception in create_temp_tables '||sqlerrm);
1018 raise;
1019 End;
1020
1021 /*5121276 bsc_aw_dim_delete needs to be created here too*/
1022 procedure create_perm_tables is
1023 table_columns bsc_update_util.t_array_temp_table_cols;
1024 l_tablespace varchar2(300);
1025 l_idx_tablespace varchar2(300);
1026 all_tables all_tables_tb;
1027 table_name varchar2(40);
1028 stmt varchar2(2000);
1029 flag boolean;
1030 Begin
1031 /*use BSC api to do the creation same way as loader */
1032 table_name:='bsc_aw_dim_delete';
1033 BSC_APPS.Init_Bsc_Apps;
1034 l_tablespace:=BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type);
1035 l_idx_tablespace:=BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type);
1036 table_columns.delete;
1037 table_columns(table_columns.count+1).column_name:='DIM_LEVEL';
1038 table_columns(table_columns.count).data_type:='VARCHAR2';
1039 table_columns(table_columns.count).data_size:=300;
1040 table_columns(table_columns.count).add_to_index:='N';
1041 table_columns(table_columns.count+1).column_name:='DELETE_VALUE';
1042 table_columns(table_columns.count).data_type:='VARCHAR2';
1043 table_columns(table_columns.count).data_size:=400;
1044 table_columns(table_columns.count).add_to_index:='N';
1045 /* */
1046 flag:=false;
1047 all_tables:=get_db_table_parameters(table_name,get_table_owner(table_name));
1048 if all_tables.count>0 then
1049 if all_tables(1).TEMPORARY='Y' then
1050 /*this is old implementation. drop and recreate */
1051 log(table_name||' is Temp Table. Dropping to create as permanent');
1052 stmt:='drop table '||table_name;
1053 BSC_APPS.Do_DDL(stmt,AD_DDL.DROP_TABLE,table_name);
1054 flag:=true;
1055 end if;
1056 else
1057 flag:=true;
1058 end if;
1059 if flag then
1060 if bsc_update_util.Create_Permanent_Table(table_name,table_columns,table_columns.count,l_tablespace,l_idx_tablespace)=false then
1061 log_n('Exception in bsc_update_util.Create_Permanent_Table. For now, not raising this exception...');
1062 end if;
1063 end if;
1064 Exception when others then
1065 log_n('Exception in create_perm_tables '||sqlerrm);
1066 raise;
1067 End;
1068
1069 /*
1070 9.204 for 9i
1071 10.104 or 10.103 etc for 10g
1072 */
1073 function get_db_version return number is
1074 l_version varchar2(200);
1075 l_compatibility varchar2(200);
1076 counter number;
1077 Begin
1078 if g_db_version is null then
1079 l_version:=get_parameter_value('DB VERSION');
1080 /*5335425. some languages have decimal representation as 10,2 instead od 10.2 */
1081 if l_version is null then
1082 dbms_utility.db_version(l_version,l_compatibility);
1083 l_version:=substr(l_version,1,instr(l_version,'.'))||replace(substr(l_version,instr(l_version,'.')+1),'.');
1084 l_version:=substr(l_version,1,instr(l_version,','))||replace(substr(l_version,instr(l_version,',')+1),',');
1085 end if;
1086 counter:=0;
1087 loop
1088 begin
1089 counter:=counter+1;
1090 if counter>=3 then
1091 log('get_db_version failed for attempts with , and . could not derive db version info');
1092 raise g_exception;
1093 end if;
1094 g_db_version:=to_number(l_version);
1095 exit;
1096 exception when others then
1097 if sqlcode=-6502 then /*ORA-06502: PL/SQL: numeric or value error: character to number conversion error */
1098 if instr(l_version,'.')>0 then
1099 l_version:=replace(l_version,'.',',');
1100 elsif instr(l_version,',')>0 then
1101 l_version:=replace(l_version,',','.');
1102 else
1103 raise;
1104 end if;
1105 else
1106 raise;
1107 end if;
1108 end;
1109 end loop;
1110 if g_debug then
1111 log('DB Version='||g_db_version);
1112 end if;
1113 end if;
1114 return g_db_version;
1115 Exception when others then
1116 log_n('Exception in get_db_version '||sqlerrm);
1117 raise;
1118 End;
1119
1120 /*
1121 procedure takes in a string upto 32000 characters and breaks it up to the specified length based on space " "
1122 and creates varchar2 table
1123 we need this to make the filter stmt.
1124 say start =1, limit =1000. see 1001th character. is it " " ? if yes, end=end-1. break up string
1125 if not, run backwards till we find " ". end=end-1. break up string
1126 */
1127 procedure convert_varchar2_to_table(
1128 p_string varchar2,
1129 p_limit number,
1130 p_table out nocopy dbms_sql.varchar2_table
1131 ) is
1132 --
1133 l_length number;
1134 l_start number;
1135 l_end number;
1136 l_space_found boolean;
1137 Begin
1138 if p_string is null then
1139 return;
1140 end if;
1141 l_length:=length(p_string);
1142 if l_length<=p_limit then
1143 p_table(1):=p_string;
1144 return;
1145 end if;
1146 --
1147 l_start:=1;
1148 loop
1149 l_end:=l_start+p_limit;
1150 if l_end>=l_length then
1151 l_end:=l_length;
1152 p_table(p_table.count+1):=substr(p_string,l_start,l_end-l_start+1);
1153 exit;
1154 else
1155 if substr(p_string,l_end,1) <> ' ' then
1156 --go backwards till we find ' '
1157 l_space_found:=false;
1158 for i in reverse l_start..l_end loop
1159 if substr(p_string,i,1)=' ' then
1160 l_end:=i;
1161 l_space_found:=true;
1162 exit;
1163 end if;
1164 end loop;
1165 --if we dont get a space, we cannot split the line. we have an exception
1166 if l_space_found=false then
1167 log('Could not find space to split line in convert_varchar2_to_table. Fatal...');
1168 raise no_data_found;
1169 end if;
1170 --
1171 end if;
1172 --split the line
1173 p_table(p_table.count+1):=substr(p_string,l_start,l_end-l_start);
1174 l_start:=l_end+1;
1175 end if;
1176 end loop;
1177 Exception when others then
1178 log_n('Exception in convert_varchar2_to_table '||sqlerrm);
1179 log('String='||p_string);
1180 raise;
1181 End;
1182
1183 procedure drop_db_object_ne(p_object varchar2,p_object_type varchar2) is
1184 Begin
1185 drop_db_object(p_object,p_object_type);
1186 Exception when others then
1187 null;
1188 End;
1189
1190 procedure drop_db_object(p_object varchar2,p_object_type varchar2) is
1191 Begin
1192 execute immediate 'drop '||p_object_type||' '||p_object;
1193 Exception when others then
1194 log_n('Exception in drop_db_object '||sqlerrm);
1195 raise;
1196 End;
1197
1198 /*
1199 sleep_time=20 sec
1200 random_time=10 sec
1201 sleep for 20+random(1 to 10)
1202 */
1203 procedure sleep(p_sleep_time integer,p_random_time integer) is
1204 Begin
1205 dbms_lock.sleep(p_sleep_time);
1206 if p_random_time is not null and p_random_time>0 then
1207 dbms_lock.sleep(get_random_number(p_random_time));
1208 end if;
1209 Exception when others then
1210 log_n('Exception in sleep '||sqlerrm);
1211 raise;
1212 End;
1213
1214 function get_random_number(p_seed number) return number is
1215 Begin
1216 return dbms_utility.get_hash_value(to_char(get_dbms_time),0,p_seed);
1217 Exception when others then
1218 log_n('Exception in get_random_number '||sqlerrm);
1219 raise;
1220 End;
1221
1222 procedure remove_array_element(p_array in out nocopy dbms_sql.varchar2_table,p_object varchar2) is
1223 Begin
1224 for i in 1..p_array.count loop
1225 if p_array(i)=p_object then
1226 for j in i..p_array.count-1 loop
1227 p_array(j):=p_array(j+1);
1228 end loop;
1229 p_array.delete(p_array.count);
1230 exit;
1231 end if;
1232 end loop;
1233 Exception when others then
1234 log_n('Exception in remove_array_element '||sqlerrm);
1235 raise;
1236 End;
1237
1238 /*
1239 handle dbms jobs for parallel loading and aggregations in 10g+
1240 we will have a global table with info on all current jobs. procedure
1241 wait on jobs will wait on jobs. jobs will communicate with the main process
1242 using dbms_pipe
1243 create job will add entries to the global table. clean job procedure will clean
1244 up the table
1245 p_process: will contain the string with the full procedure call and parameters.
1246
1247 */
1248 procedure start_job(
1249 p_job_name varchar2,
1250 p_run_id number,
1251 p_process varchar2,
1252 p_options varchar2
1253 ) is
1254 PRAGMA AUTONOMOUS_TRANSACTION; --we have a commit here to start dbms job
1255 --
1256 l_job_id integer;
1257 l_parallel_job parallel_job_r;
1258 Begin
1259 --should we have protection to make sure we do not launch the same job more than once? lets have it for now
1260 l_parallel_job:=get_parallel_job(p_job_name);
1261 if l_parallel_job.job_name is not null then
1262 if l_parallel_job.status <> 'success' and l_parallel_job.status <> 'error' then
1263 log_n('The same job '||p_job_name||' is currently running');
1264 raise bsc_aw_utility.g_exception;
1265 end if;
1266 end if;
1267 --create a pipe for communication
1268 create_pipe(p_job_name);
1269 if g_debug then
1270 log_n('dbms_job.submit('||p_process||')'||bsc_aw_utility.get_time);
1271 end if;
1272 dbms_job.submit(l_job_id,p_process);
1273 --add to the global array list
1274 g_parallel_jobs(g_parallel_jobs.count+1).job_name:=p_job_name;
1275 g_parallel_jobs(g_parallel_jobs.count).run_id:=p_run_id; --1,2, 3 etc
1276 g_parallel_jobs(g_parallel_jobs.count).job_id:=l_job_id;
1277 g_parallel_jobs(g_parallel_jobs.count).start_time:=get_time;
1278 g_parallel_jobs(g_parallel_jobs.count).status:='running';
1279 commit;
1280 Exception when others then
1281 log_n('Exception in start_job '||sqlerrm);
1282 raise;
1283 End;
1284
1285 function get_parallel_job(p_job_name varchar2) return parallel_job_r is
1286 Begin
1287 for i in 1..g_parallel_jobs.count loop
1288 if g_parallel_jobs(i).job_name=p_job_name then
1289 return g_parallel_jobs(i);
1290 end if;
1291 end loop;
1292 return null;
1293 Exception when others then
1294 log_n('Exception in get_parallel_job '||sqlerrm);
1295 raise;
1296 End;
1297
1298 procedure wait_on_jobs(p_options varchar2,p_job_status out nocopy parallel_job_tb) is
1299 Begin
1300 if g_debug then
1301 log('Wait on jobs >>'||get_time);
1302 dmp_parallel_jobs;
1303 end if;
1304 clean_up_jobs(null); --clean up done jobs
1305 if check_all_jobs_complete(g_parallel_jobs)=false then
1306 wait_on_jobs_sleep(p_options,p_job_status);
1307 /*looked at dbms_alert as a way to avoid active wait. but it looks like dbms_alert internally goes for active wait */
1308 end if;
1309 if g_debug then
1310 log_n('Wait on jobs Done >>'||get_time);
1311 dmp_parallel_jobs;
1312 end if;
1313 clean_up_jobs(null); --clean up done jobs
1314 Exception when others then
1315 log_n('Exception in wait_on_jobs '||sqlerrm);
1316 raise;
1317 End;
1318
1319 procedure wait_on_jobs_sleep(p_options varchar2,p_job_status out nocopy parallel_job_tb) is
1320 --
1321 l_prev_time number;
1322 Begin
1323 if g_debug then
1324 log_n('wait_on_jobs_sleep');
1325 end if;
1326 loop
1327 if l_prev_time is null then
1328 l_prev_time:=g_job_wait_time_large;
1329 else
1330 l_prev_time:=g_job_wait_time_small;
1331 end if;
1332 sleep(l_prev_time,null);
1333 check_jobs(g_parallel_jobs);
1334 if check_all_jobs_complete(g_parallel_jobs) then
1335 exit;
1336 end if;
1337 end loop;
1338 p_job_status:=g_parallel_jobs;
1339 Exception when others then
1340 log_n('Exception in wait_on_jobs_sleep '||sqlerrm);
1341 raise;
1342 End;
1343
1344 procedure check_jobs(p_parallel_jobs in out nocopy parallel_job_tb) is
1345 Begin
1346 for i in 1..p_parallel_jobs.count loop
1347 if p_parallel_jobs(i).status <> 'success' and p_parallel_jobs(i).status <> 'error' then
1348 if is_job_running(p_parallel_jobs(i).job_id)='N' then --mark this job as done
1349 update_job_status(p_parallel_jobs(i));--read from the pipe. then update the fields
1350 end if;
1351 end if;
1352 end loop;
1353 Exception when others then
1354 log_n('Exception in check_jobs '||sqlerrm);
1355 raise;
1356 End;
1357
1358 function check_all_jobs_complete(p_parallel_jobs parallel_job_tb) return boolean is
1359 Begin
1360 for i in 1..p_parallel_jobs.count loop
1361 if p_parallel_jobs(i).status <> 'success' and p_parallel_jobs(i).status <> 'error' then
1362 return false;
1363 end if;
1364 end loop;
1365 return true;
1366 Exception when others then
1367 log_n('Exception in check_all_jobs_complete '||sqlerrm);
1368 raise;
1369 End;
1370
1371 procedure update_job_status(p_parallel_job in out nocopy parallel_job_r) is
1372 --
1373 l_pipe_message varchar2(2000);
1374 Begin
1375 --get pipe message
1376 l_pipe_message:=get_pipe_message(p_parallel_job.job_name);
1377 if l_pipe_message is null then
1378 --the process core dumped without getting a chance to write to the pipe
1379 p_parallel_job.status:='error';
1380 p_parallel_job.sqlcode:=-20000;
1381 p_parallel_job.message:=null;
1382 p_parallel_job.end_time:=get_time;
1383 else
1384 p_parallel_job.status:=get_parameter_value(l_pipe_message,'status',',');
1385 p_parallel_job.sqlcode:=get_parameter_value(l_pipe_message,'sqlcode',','); --null for success
1386 p_parallel_job.message:=get_parameter_value(l_pipe_message,'message',','); --null for success
1387 p_parallel_job.end_time:=get_time;
1388 end if;
1389 remove_pipe(p_parallel_job.job_name);
1390 Exception when others then
1391 log_n('Exception in update_job_status '||sqlerrm);
1392 raise;
1393 End;
1394
1395 procedure create_pipe(p_pipe_name varchar2) is
1396 l_status number;
1397 Begin
1398 remove_pipe(p_pipe_name);
1399 l_status:=dbms_pipe.create_pipe(pipename=>p_pipe_name,private=>false);
1400 Exception when others then
1401 if sqlcode=-23322 then --naming conflict
1402 if g_debug then
1403 log_n('Pipe with same name '||p_pipe_name||' exists');
1404 end if;
1405 else
1406 log_n('Exception in create_pipe '||sqlerrm);
1407 raise;
1408 end if;
1409 End;
1410
1411 function get_pipe_message(p_pipe_name varchar2) return varchar2 is
1412 --
1413 l_status number;
1414 l_message varchar2(2000);
1415 Begin
1416 l_status:=dbms_pipe.receive_message(pipename=>p_pipe_name);
1417 if l_status=0 then
1418 dbms_pipe.unpack_message(l_message);
1419 end if;
1420 return l_message;
1421 Exception when others then
1422 log_n('Exception in get_pipe_message '||sqlerrm);
1423 raise;
1424 End;
1425
1426 procedure send_pipe_message(p_pipe_name varchar2,p_message varchar2) is
1427 PRAGMA AUTONOMOUS_TRANSACTION;
1428 --
1429 l_status number;
1430 Begin
1431 dbms_pipe.reset_buffer;
1432 dbms_pipe.pack_message(p_message);
1433 l_status:=dbms_pipe.send_message(pipename=>p_pipe_name);
1434 commit;
1435 Exception when others then
1436 log_n('Exception in send_pipe_message '||sqlerrm);
1437 raise;
1438 End;
1439
1440 procedure remove_pipe(p_pipe_name varchar2) is
1441 --
1442 l_status number;
1443 Begin
1444 l_status:=dbms_pipe.remove_pipe(pipename=>p_pipe_name);
1445 Exception when others then
1446 log_n('Exception in remove_pipe '||sqlerrm);
1447 End;
1448
1449 /*
1450 we look at all_jobs and dba_jobs_running because when a job is launched, it takes some time
1451 for it to start.
1452 */
1453 function is_job_running(p_job_id number) return varchar2 is
1454 --
1455 cursor c1 is select 1 from all_jobs where job=p_job_id;
1456 l_status number;
1457 Begin
1458 open c1;
1459 fetch c1 into l_status;
1460 close c1;
1461 if l_status=1 then
1462 return 'Y';
1463 else
1464 return 'N';
1465 end if;
1466 Exception when others then
1467 log_n('Exception in is_job_running '||sqlerrm);
1468 raise;
1469 End;
1470
1471 --for now, clean up all the jobs
1472 procedure clean_up_jobs(p_options varchar2) is
1473 l_parallel_jobs parallel_job_tb;
1474 Begin
1475 if get_parameter_value(p_options,'all',',')='Y' then
1476 g_parallel_jobs.delete;
1477 else
1478 --clean up success and error
1479 for i in 1..g_parallel_jobs.count loop
1480 if g_parallel_jobs(i).status <> 'success' and g_parallel_jobs(i).status <> 'error' then
1481 l_parallel_jobs(l_parallel_jobs.count+1):=g_parallel_jobs(i);
1482 end if;
1483 end loop;
1484 g_parallel_jobs.delete;
1485 g_parallel_jobs:=l_parallel_jobs;
1486 end if;
1487 Exception when others then
1488 log_n('Exception in clean_up_jobs '||sqlerrm);
1489 raise;
1490 End;
1491
1492 procedure dmp_parallel_jobs is
1493 Begin
1494 log_n('dmp parallel jobs >>');
1495 for i in 1..g_parallel_jobs.count loop
1496 log('job name='||g_parallel_jobs(i).job_name||',run id='||g_parallel_jobs(i).run_id||
1497 ',job id='||g_parallel_jobs(i).job_id||',status='||g_parallel_jobs(i).status||',sqlcode='||g_parallel_jobs(i).sqlcode||
1498 ',message='||g_parallel_jobs(i).message||'(S:'||g_parallel_jobs(i).start_time||' -> E:'||g_parallel_jobs(i).end_time||')');
1499 end loop;
1500 log_n('-------------');
1501 Exception when others then
1502 log_n('Exception in dmp_parallel_jobs '||sqlerrm);
1503 raise;
1504 End;
1505
1506 /*
1507 to launch jobs, we need to make sure that
1508 (job_queue_processes-count_jobs_running) > p_number_jobs
1509 */
1510 function can_launch_jobs(p_number_jobs number) return varchar2 is
1511 Begin
1512 /*we will enable parallel by default */
1513 if get_db_version>=10 and nvl(get_parameter_value('exclusive lock'),'N')='N'
1514 and nvl(get_parameter_value('NO PARALLEL'),'N')='N' then
1515 if p_number_jobs<=1 then
1516 return 'N';
1517 end if;
1518 return can_launch_dbms_job(p_number_jobs);
1519 end if;
1520 return 'N'; --if 9i, no jobs possible
1521 Exception when others then
1522 log_n('Exception in can_launch_jobs '||sqlerrm);
1523 raise;
1524 End;
1525
1526 function can_launch_dbms_job(p_number_jobs number) return varchar2 is
1527 l_job_queue_processes number;
1528 l_jobs_running number;
1529 l_status varchar2(20);
1530 Begin
1531 l_status:='Y';
1532 l_job_queue_processes:=to_number(get_vparameter('job_queue_processes'));
1533 l_jobs_running:=count_jobs_running;
1534 if g_debug then
1535 log_n('can_launch_jobs,l_job_queue_processes='||l_job_queue_processes||',l_jobs_running='||l_jobs_running||', p_number_jobs='||p_number_jobs);
1536 end if;
1537 if l_status='Y' then
1538 if l_job_queue_processes is null then
1539 l_status:='N';
1540 elsif (l_job_queue_processes-l_jobs_running)<=p_number_jobs then
1541 l_status:='N';
1542 end if;
1543 end if;
1544 if l_status='Y' then
1545 /*p_number_jobs is more than 2 times the cpu count, we disable jobs */
1546 if p_number_jobs>(2*get_cpu_count) then
1547 l_status:='N';
1548 end if;
1549 end if;
1550 return l_status;
1551 Exception when others then
1552 log_n('Exception in can_launch_dbms_job '||sqlerrm);
1553 raise;
1554 End;
1555
1556 function count_jobs_running return number is
1557 --
1558 TYPE CurTyp IS REF CURSOR;
1559 cv CurTyp;
1560 l_count number;
1561 Begin
1562 open cv for 'select count(*) from all_scheduler_running_jobs';
1563 fetch cv into l_count;
1564 close cv;
1565 return l_count;
1566 Exception when others then
1567 if g_debug then
1568 log_n('Exception in count_jobs_running '||sqlerrm);
1569 end if;
1570 return 0;
1571 End;
1572
1573 function get_vparameter(p_name varchar2) return varchar2 is
1574 --
1575 cursor c1 is select value from v$parameter param where name=p_name;
1576 l_value varchar2(200);
1577 Begin
1578 open c1;
1579 fetch c1 into l_value;
1580 return l_value;
1581 Exception when others then
1582 log_n('Exception in get_vparameter '||sqlerrm);
1583 raise;
1584 End;
1585
1586 --makes a string out of the options. for dbms jobs
1587 function get_option_string return varchar2 is
1588 l_string varchar2(4000);
1589 Begin
1590 for i in 1..g_options.count loop
1591 l_string:=l_string||g_options(i).parameter;
1592 if g_options(i).value is not null then
1593 l_string:=l_string||'='||g_options(i).value;
1594 end if;
1595 l_string:=l_string||',';
1596 end loop;
1597 return l_string;
1598 Exception when others then
1599 log_n('Exception in get_option_string '||sqlerrm);
1600 raise;
1601 End;
1602
1603 function get_session_id return number is
1604 l_sid number;
1605 Begin
1606 select mystat.sid into l_sid from v$mystat mystat where rownum=1;
1607 return l_sid;
1608 Exception when others then
1609 return userenv('SESSIONID');
1610 End;
1611
1612 --used primarily as job_name in 10g multi threading to get a unique name
1613 function get_dbms_time return number is
1614 Begin
1615 return dbms_utility.get_time;
1616 Exception when others then
1617 log_n('Exception in get_dbms_time '||sqlerrm);
1618 raise;
1619 End;
1620
1621 function make_string_from_list(p_list dbms_sql.varchar2_table) return varchar2 is
1622 Begin
1623 return make_string_from_list(p_list,',');
1624 Exception when others then
1625 log_n('Exception in make_string_from_list '||sqlerrm);
1626 raise;
1627 End;
1628
1629 function make_string_from_list(p_list dbms_sql.varchar2_table,p_separator varchar2) return varchar2 is
1630 --
1631 l_string varchar2(20000);
1632 Begin
1633 l_string:=null;
1634 if p_list.count>0 then
1635 for i in 1..p_list.count loop
1636 l_string:=l_string||p_list(i)||p_separator;
1637 end loop;
1638 l_string:=substr(l_string,1,length(l_string)-1);
1639 end if;
1640 return l_string;
1641 Exception when others then
1642 log_n('Exception in make_string_from_list '||sqlerrm);
1643 raise;
1644 End;
1645
1646 /*
1647 see if the options are part of g_options. if not, add them
1648 if p_option_value is null, add all in p_options. else add p_option_value in p_options
1649 this will also update the option value if already in g_options
1650 */
1651 procedure add_option(p_options varchar2,p_option_value varchar2,p_separator varchar2) is
1652 l_values value_tb;
1653 l_found boolean;
1654 Begin
1655 parse_parameter_values(p_options,p_separator,l_values);
1656 for i in 1..l_values.count loop
1657 if p_option_value is null or l_values(i).parameter=p_option_value then
1658 l_found:=false;
1659 for j in 1..g_options.count loop
1660 if g_options(j).parameter=l_values(i).parameter then
1661 l_found:=true;
1662 g_options(j):=l_values(i);
1663 exit;
1664 end if;
1665 end loop;
1666 if l_found=false then
1667 g_options(g_options.count+1):=l_values(i);
1668 end if;
1669 end if;
1670 end loop;
1671 Exception when others then
1672 log_n('Exception in add_option '||sqlerrm);
1673 raise;
1674 End;
1675
1676 procedure set_option(p_parameter varchar2,p_value varchar2) is
1677 Begin
1678 for i in 1..g_options.count loop
1679 if g_options(i).parameter=p_parameter then
1680 g_options(i).value:=p_value;
1681 return;
1682 end if;
1683 end loop;
1684 g_options(g_options.count+1).parameter:=p_parameter;
1685 g_options(g_options.count).value:=p_value;
1686 Exception when others then
1687 log_n('Exception in set_option '||sqlerrm);
1688 raise;
1689 End;
1690
1691 function get_hash_value(p_string varchar2,p_start number,p_end number) return varchar2 is
1692 l_hash_value number;
1693 Begin
1694 l_hash_value:=dbms_utility.get_hash_value(p_string,p_start,p_end);
1695 return to_char(l_hash_value);
1696 Exception when others then
1697 log_n('Exception in get_hash_value '||sqlerrm);
1698 raise;
1699 End;
1700
1701 procedure merge_array(p_array in out nocopy dbms_sql.varchar2_table,p_values dbms_sql.varchar2_table) is
1702 Begin
1703 for i in 1..p_values.count loop
1704 if in_array(p_array,p_values(i))=false then
1705 p_array(p_array.count+1):=p_values(i);
1706 end if;
1707 end loop;
1708 Exception when others then
1709 log_n('Exception in merge_array '||sqlerrm);
1710 raise;
1711 End;
1712
1713 procedure merge_value(p_array in out nocopy dbms_sql.varchar2_table,p_value varchar2) is
1714 Begin
1715 if p_value is not null then
1716 if in_array(p_array,p_value)=false then
1717 p_array(p_array.count+1):=p_value;
1718 end if;
1719 end if;
1720 Exception when others then
1721 log_n('Exception in merge_value '||sqlerrm);
1722 raise;
1723 End;
1724
1725 procedure subtract_array(p_array in out nocopy dbms_sql.varchar2_table,p_values dbms_sql.varchar2_table) is
1726 l_array dbms_sql.varchar2_table;
1727 Begin
1728 if p_values.count>0 and p_array.count>0 then
1729 for i in 1..p_array.count loop
1730 l_array(i):=p_array(i);
1731 end loop;
1732 p_array.delete;
1733 for i in 1..l_array.count loop
1734 if in_array(p_values,l_array(i))=false then
1735 p_array(p_array.count+1):=l_array(i);
1736 end if;
1737 end loop;
1738 end if;
1739 Exception when others then
1740 log_n('Exception in subtract_array '||sqlerrm);
1741 raise;
1742 End;
1743
1744 procedure merge_array(p_array in out nocopy dbms_sql.number_table,p_values dbms_sql.number_table) is
1745 Begin
1746 for i in 1..p_values.count loop
1747 if in_array(p_array,p_values(i))=false then
1748 p_array(p_array.count+1):=p_values(i);
1749 end if;
1750 end loop;
1751 Exception when others then
1752 log_n('Exception in merge_array '||sqlerrm);
1753 raise;
1754 End;
1755
1756 procedure merge_value(p_array in out nocopy dbms_sql.number_table,p_value number) is
1757 Begin
1758 if p_value is not null then
1759 if in_array(p_array,p_value)=false then
1760 p_array(p_array.count+1):=p_value;
1761 end if;
1762 end if;
1763 Exception when others then
1764 log_n('Exception in merge_value '||sqlerrm);
1765 raise;
1766 End;
1767
1768 procedure set_aw_trace is
1769 Begin
1770 if g_trace_set is null or g_trace_set=false then
1771 execute immediate 'alter session set sql_trace=true';
1772 execute immediate 'alter session set events=''10046 trace name context forever, level 12''';
1773 execute immediate 'alter session set events=''37395 trace name context forever, level 1''';
1774 bsc_aw_dbms_aw.execute('dotf tracefile');
1775 g_trace_set:=true;
1776 end if;
1777 Exception when others then
1778 log_n('Exception in set_aw_trace '||sqlerrm);
1779 raise;
1780 End;
1781
1782 procedure dmp_values(p_table dbms_sql.varchar2_table,p_text varchar2) is
1783 Begin
1784 log('------');
1785 log(p_text);
1786 for i in 1..p_table.count loop
1787 log(p_table(i));
1788 end loop;
1789 Exception when others then
1790 log_n('Exception in dmp_values '||sqlerrm);
1791 raise;
1792 End;
1793
1794 function get_sqlerror(p_sqlcode number,p_action varchar2) return sqlerror_r is
1795 Begin
1796 for i in 1..g_sqlerror.count loop
1797 if g_sqlerror(i).sql_code=p_sqlcode and g_sqlerror(i).action=p_action then
1798 return g_sqlerror(i);
1799 end if;
1800 end loop;
1801 return null;
1802 Exception when others then
1803 log_n('Exception in get_sqlerror '||sqlerrm);
1804 raise;
1805 End;
1806
1807 procedure add_sqlerror(p_sqlcode number,p_action varchar2,p_message varchar2) is
1808 l_sqlerror sqlerror_r;
1809 Begin
1810 l_sqlerror:=get_sqlerror(p_sqlcode,p_action);
1811 if l_sqlerror.sql_code is null then
1812 g_sqlerror(g_sqlerror.count+1).sql_code:=p_sqlcode;
1813 g_sqlerror(g_sqlerror.count).action:=p_action;
1814 g_sqlerror(g_sqlerror.count).message:=p_message;
1815 end if;
1816 Exception when others then
1817 log_n('Exception in add_sqlerror '||sqlerrm);
1818 raise;
1819 End;
1820
1821 procedure remove_sqlerror(p_sqlcode number,p_action varchar2) is
1822 l_sqlerror sqlerror_tb;
1823 Begin
1824 l_sqlerror:=g_sqlerror;
1825 g_sqlerror.delete;
1826 for i in 1..l_sqlerror.count loop
1827 if not(l_sqlerror(i).sql_code=p_sqlcode and l_sqlerror(i).action=p_action) then
1828 g_sqlerror(g_sqlerror.count+1):=l_sqlerror(i);
1829 end if;
1830 end loop;
1831 Exception when others then
1832 log_n('Exception in remove_sqlerror '||sqlerrm);
1833 raise;
1834 End;
1835
1836 procedure remove_all_sqlerror is --remove all sqlerror
1837 Begin
1838 g_sqlerror.delete;
1839 Exception when others then
1840 log_n('Exception in remove_all_sqlerror '||sqlerrm);
1841 raise;
1842 End;
1843
1844 function is_sqlerror(p_sqlcode number,p_action varchar2) return boolean is
1845 l_sqlerror sqlerror_r;
1846 Begin
1847 l_sqlerror:=get_sqlerror(p_sqlcode,p_action);
1848 if l_sqlerror.sql_code is null then
1849 return false;
1850 else
1851 return true;
1852 end if;
1853 Exception when others then
1854 log_n('Exception in is_sqlerror '||sqlerrm);
1855 raise;
1856 End;
1857
1858 /*
1859 -1 : 2 rel diff
1860 0 : 2 rel same
1861 1 : 1 is in 2
1862 2: 2 is in 1
1863 */
1864 function compare_pc_relations(p_pc_1 parent_child_tb,p_pc_2 parent_child_tb) return number is
1865 --
1866 TYPE CurTyp IS REF CURSOR;
1867 cv CurTyp;
1868 l_1_minus_2 number;
1869 l_2_minus_1 number;
1870 l_temp1 varchar2(200);
1871 l_temp2 varchar2(200);
1872 l_return_code number;
1873 Begin
1874 bsc_aw_utility.delete_table('bsc_aw_temp_pc',null);
1875 for i in 1..p_pc_1.count loop
1876 if p_pc_1(i).parent is not null and p_pc_1(i).child is not null then
1877 execute immediate 'insert into bsc_aw_temp_pc(parent,child,id) values (:1,:2,:3)' using nvl(p_pc_1(i).parent,'null'),
1878 nvl(p_pc_1(i).child,'null'),1;
1879 end if;
1880 end loop;
1881 for i in 1..p_pc_2.count loop
1882 if p_pc_2(i).parent is not null and p_pc_2(i).child is not null then
1883 execute immediate 'insert into bsc_aw_temp_pc(parent,child,id) values (:1,:2,:3)' using nvl(p_pc_2(i).parent,'null'),
1884 nvl(p_pc_2(i).child,'null'),2;
1885 end if;
1886 end loop;
1887 l_1_minus_2:=0;
1888 l_2_minus_1:=0;
1889 --
1890 l_temp1:=null;
1891 l_temp2:=null;
1892 open cv for 'select parent,child from bsc_aw_temp_pc where id=1 minus select parent,child from bsc_aw_temp_pc where id=2';
1893 fetch cv into l_temp1,l_temp2;
1894 close cv;
1895 if l_temp1 is not null then
1896 l_1_minus_2:=1;
1897 end if;
1898 if g_debug then
1899 log('1 minus 2 '||l_temp1||' '||l_temp2);
1900 end if;
1901 --
1902 l_temp1:=null;
1903 l_temp2:=null;
1904 open cv for 'select parent,child from bsc_aw_temp_pc where id=2 minus select parent,child from bsc_aw_temp_pc where id=1';
1905 fetch cv into l_temp1,l_temp2;
1906 close cv;
1907 if l_temp1 is not null then
1908 l_2_minus_1:=1;
1909 end if;
1910 if g_debug then
1911 log('2 minus 1 '||l_temp1||' '||l_temp2);
1912 end if;
1913 --
1914 if l_1_minus_2=0 and l_2_minus_1=0 then --same
1915 l_return_code:=0;
1916 elsif l_1_minus_2=0 and l_2_minus_1>0 then --1 is in 2
1917 l_return_code:=1;
1918 elsif l_1_minus_2>0 and l_2_minus_1=0 then --2 is in 1
1919 l_return_code:=2;
1920 elsif l_1_minus_2>0 and l_2_minus_1>0 then --not same
1921 l_return_code:=-1;
1922 end if;
1923 if g_debug then
1924 log('compare_pc_relations, Return code='||l_return_code);
1925 end if;
1926 return l_return_code;
1927 Exception when others then
1928 log_n('Exception in compare_pc_relations '||sqlerrm);
1929 raise;
1930 End;
1931
1932 procedure init_is_new_value is
1933 Begin
1934 g_values.delete;
1935 Exception when others then
1936 log_n('Exception in init_is_new_value '||sqlerrm);
1937 raise;
1938 End;
1939
1940 procedure init_is_new_value(p_index number) is
1941 Begin
1942 g_values(p_index).id:=p_index;
1943 g_values(p_index).new_values.delete;
1944 Exception when others then
1945 log_n('Exception in init_is_new_value '||sqlerrm);
1946 raise;
1947 End;
1948
1949 --goes hand in hand with init_is_new_value
1950 function is_new_value(p_value varchar2,p_index number) return boolean is
1951 l_flag boolean;
1952 Begin
1953 l_flag:=in_array(g_values(p_index).new_values,p_value);
1954 if l_flag=false then
1955 g_values(p_index).new_values(g_values(p_index).new_values.count+1):=p_value;
1956 end if;
1957 return not(l_flag);
1958 Exception when others then
1959 log_n('Exception in is_new_value '||sqlerrm);
1960 raise;
1961 End;
1962
1963 --goes hand in hand with init_is_new_value
1964 function is_new_value(p_value number,p_index number) return boolean is
1965 Begin
1966 return is_new_value(to_char(p_value),p_index);
1967 Exception when others then
1968 log_n('Exception in is_new_value '||sqlerrm);
1969 raise;
1970 End;
1971
1972 function order_array(p_array dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
1973 l_array dbms_sql.varchar2_table;
1974 TYPE CurTyp IS REF CURSOR;
1975 cv CurTyp;
1976 Begin
1977 if p_array.count>0 then
1978 delete_table('bsc_aw_temp_vn',null);
1979 forall i in 1..p_array.count
1980 execute immediate 'insert into bsc_aw_temp_vn(name) values (:1)' using p_array(i);
1981 open cv for 'select name from bsc_aw_temp_vn order by name';
1982 loop
1983 fetch cv into l_array(l_array.count+1);
1984 exit when cv%notfound;
1985 end loop;
1986 end if;
1987 return l_array;
1988 Exception when others then
1989 log_n('Exception in order_array '||sqlerrm);
1990 raise;
1991 End;
1992
1993 function make_upper(p_array dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
1994 l_array dbms_sql.varchar2_table;
1995 Begin
1996 for i in 1..p_array.count loop
1997 l_array(i):=upper(p_array(i));
1998 end loop;
1999 return l_array;
2000 Exception when others then
2001 log_n('Exception in make_upper '||sqlerrm);
2002 raise;
2003 End;
2004
2005 /*
2006 getting db locks. used for co-ordinating multiple MO sessions
2007 for now, we do not implement this. in lock management, we already do spin wait. so 2 sessions cane be started, the 2nd will spin
2008 and try to get the lock
2009 */
2010 procedure get_db_lock(p_lock_name varchar2) is
2011 l_lock_handle varchar2(200);
2012 l_flag integer;
2013 Begin
2014 l_lock_handle:=get_lock_handle(p_lock_name);
2015 l_flag:=dbms_lock.request(lockhandle=>l_lock_handle,timeout=>bsc_aw_utility.g_max_wait_time);
2016 if l_flag=1 then --timeout
2017 log('Timeout in dbms_lock.request('||p_lock_name||') ...');
2018 raise bsc_aw_utility.g_exception;
2019 end if;
2020 Exception when others then
2021 log_n('Exception in get_db_lock '||sqlerrm);
2022 raise;
2023 End;
2024
2025 procedure release_db_lock(p_lock_name varchar2) is
2026 l_lock_handle varchar2(2000);
2027 l_flag integer;
2028 Begin
2029 l_lock_handle:=get_lock_handle(p_lock_name);
2030 l_flag:=dbms_lock.release(lockhandle=>l_lock_handle);
2031 if l_flag=3 or l_flag=5 then --error
2032 log('Exception in dbms_lock.release. Return flag='||l_flag);
2033 raise bsc_aw_utility.g_exception;
2034 end if;
2035 Exception when others then
2036 log_n('Exception in release_db_lock '||sqlerrm);
2037 raise;
2038 End;
2039
2040 function get_lock_handle(p_lock_name varchar2) return varchar2 is
2041 l_lock_handle varchar2(2000);
2042 Begin
2043 dbms_lock.allocate_unique(lockname=>p_lock_name,lockhandle=>l_lock_handle);
2044 return l_lock_handle;
2045 Exception when others then
2046 log_n('Exception in get_lock_handle '||sqlerrm);
2047 raise;
2048 End;
2049
2050 function get_closest_2_power_number(p_number number) return number is
2051 l_num number;
2052 Begin
2053 l_num:=1;
2054 if p_number is null or p_number<2 then
2055 return 0;
2056 end if;
2057 loop
2058 l_num:=l_num*2;
2059 if l_num>p_number then
2060 return l_num/2;
2061 end if;
2062 end loop;
2063 Exception when others then
2064 log_n('Exception in get_closest_2_power '||sqlerrm);
2065 raise;
2066 End;
2067
2068 procedure analyze_table(p_table varchar2,p_interval number) is
2069 l_table varchar2(100);
2070 l_owner varchar2(100);
2071 l_all_tables all_tables_tb;
2072 l_analyze boolean;
2073 Begin
2074 l_table:=substr(p_table,instr(p_table,'.')+1);
2075 l_owner:=get_table_owner(p_table);
2076 l_analyze:=false;
2077 if p_interval is null or p_interval=0 then
2078 l_analyze:=true;
2079 else
2080 l_all_tables:=get_db_table_parameters(l_table,l_owner);
2081 if l_all_tables(1).last_analyzed is null then
2082 l_analyze:=true;
2083 else
2084 if (sysdate-l_all_tables(1).last_analyzed)>p_interval then
2085 l_analyze:=true;
2086 end if;
2087 end if;
2088 end if;
2089 if l_analyze then
2090 analyze_table(l_table,l_owner);
2091 end if;
2092 Exception when others then
2093 log_n('Exception in analyze_table '||sqlerrm);
2094 raise;
2095 End;
2096
2097 procedure analyze_table(p_table varchar2,p_owner varchar2) is
2098 Begin
2099 if g_debug then
2100 log('Analyze '||p_owner||'.'||p_table||get_time);
2101 end if;
2102 dbms_stats.gather_table_stats(OWNNAME=>p_owner,TABNAME=>p_table);
2103 if g_debug then
2104 log('Done '||get_time);
2105 end if;
2106 Exception when others then
2107 log_n('Exception in analyze_table '||sqlerrm);
2108 raise;
2109 End;
2110
2111 function get_db_table_parameters(p_table varchar2,p_owner varchar2) return all_tables_tb is
2112 l_tables all_tables_tb;
2113 cursor c1 is select * from all_tables where table_name=upper(p_table) and owner=upper(p_owner);
2114 Begin
2115 open c1;
2116 fetch c1 into l_tables(1);
2117 close c1;
2118 return l_tables;
2119 Exception when others then
2120 log_n('Exception in get_db_table_parameters '||sqlerrm);
2121 raise;
2122 End;
2123
2124 /*given a relation and a start value, get the trim hier with all upper values*/
2125 procedure get_upper_trim_hier(p_parent_child parent_child_tb,p_seed varchar2,p_trim_parent_child in out nocopy parent_child_tb) is
2126 l_parents parent_child_tb;
2127 flag boolean;
2128 Begin
2129 get_parent_values(p_parent_child,p_seed,l_parents);
2130 for i in 1..l_parents.count loop
2131 get_upper_trim_hier(p_parent_child,l_parents(i).parent,p_trim_parent_child);
2132 end loop;
2133 if l_parents.count>0 then
2134 for i in 1..l_parents.count loop
2135 flag:=false;
2136 for j in 1..p_trim_parent_child.count loop --add only distinct list
2137 if p_trim_parent_child(j).parent=l_parents(i).parent and p_trim_parent_child(j).child=l_parents(i).child then
2138 flag:=true;
2139 exit;
2140 end if;
2141 end loop;
2142 if flag=false then
2143 p_trim_parent_child(p_trim_parent_child.count+1):=l_parents(i);
2144 end if;
2145 end loop;
2146 end if;
2147 Exception when others then
2148 log_n('Exception in get_upper_trim_hier '||sqlerrm);
2149 raise;
2150 End;
2151
2152 /*given a relation and a start value, get the trim hier with all lower values*/
2153 procedure get_lower_trim_hier(p_parent_child parent_child_tb,p_seed varchar2,p_trim_parent_child in out nocopy parent_child_tb) is
2154 l_children parent_child_tb;
2155 flag boolean;
2156 Begin
2157 get_child_values(p_parent_child,p_seed,l_children);
2158 for i in 1..l_children.count loop
2159 get_lower_trim_hier(p_parent_child,l_children(i).child,p_trim_parent_child);
2160 end loop;
2161 if l_children.count>0 then
2162 for i in 1..l_children.count loop
2163 flag:=false;
2164 for j in 1..p_trim_parent_child.count loop --add only distinct list
2165 if p_trim_parent_child(j).parent=l_children(i).parent and p_trim_parent_child(j).child=l_children(i).child then
2166 flag:=true;
2167 exit;
2168 end if;
2169 end loop;
2170 if flag=false then
2171 p_trim_parent_child(p_trim_parent_child.count+1):=l_children(i);
2172 end if;
2173 end loop;
2174 end if;
2175 Exception when others then
2176 log_n('Exception in get_lower_trim_hier '||sqlerrm);
2177 raise;
2178 End;
2179
2180 procedure get_parent_values(p_parent_child parent_child_tb,p_child varchar2,p_parents out nocopy parent_child_tb) is
2181 Begin
2182 for i in 1..p_parent_child.count loop
2183 if p_parent_child(i).child=p_child and p_parent_child(i).parent is not null then
2184 p_parents(p_parents.count+1):=p_parent_child(i);
2185 end if;
2186 end loop;
2187 Exception when others then
2188 log_n('Exception in get_parent_values '||sqlerrm);
2189 raise;
2190 End;
2191
2192 procedure get_child_values(p_parent_child parent_child_tb,p_parent varchar2,p_children out nocopy parent_child_tb) is
2193 Begin
2194 for i in 1..p_parent_child.count loop
2195 if p_parent_child(i).parent=p_parent and p_parent_child(i).child is not null then
2196 p_children(p_children.count+1):=p_parent_child(i);
2197 end if;
2198 end loop;
2199 Exception when others then
2200 log_n('Exception in get_child_values '||sqlerrm);
2201 raise;
2202 End;
2203
2204 procedure get_all_parents(p_parent_child parent_child_tb,p_child varchar2,p_parents in out nocopy dbms_sql.varchar2_table) is
2205 l_parents parent_child_tb;
2206 Begin
2207 get_parent_values(p_parent_child,p_child,l_parents);
2208 for i in 1..l_parents.count loop
2209 get_all_parents(p_parent_child,l_parents(i).parent,p_parents);
2210 end loop;
2211 if l_parents.count>0 then
2212 for i in 1..l_parents.count loop
2213 if l_parents(i).parent is not null then
2214 merge_value(p_parents,l_parents(i).parent);
2215 end if;
2216 end loop;
2217 end if;
2218 Exception when others then
2219 log_n('Exception in get_all_parents '||sqlerrm);
2220 raise;
2221 End;
2222
2223 procedure get_all_children(p_parent_child parent_child_tb,p_parent varchar2,p_children in out nocopy dbms_sql.varchar2_table) is
2224 l_children parent_child_tb;
2225 Begin
2226 get_child_values(p_parent_child,p_parent,l_children);
2227 for i in 1..l_children.count loop
2228 get_all_children(p_parent_child,l_children(i).child,p_children);
2229 end loop;
2230 if l_children.count>0 then
2231 for i in 1..l_children.count loop
2232 if l_children(i).child is not null then
2233 merge_value(p_children,l_children(i).child);
2234 end if;
2235 end loop;
2236 end if;
2237 Exception when others then
2238 log_n('Exception in get_all_children '||sqlerrm);
2239 raise;
2240 End;
2241
2242 /*we have a string like property1=value1,property2,property3=value3
2243 we pass new property and or new value like property2 value value2 or property 3 value 33 or property4 with value 4
2244 we update p_string with the values*/
2245 procedure update_property(p_string in out nocopy varchar2,p_parameter varchar2,p_value varchar2,p_separator varchar2) is
2246 l_values dbms_sql.varchar2_table;
2247 l_index number;
2248 Begin
2249 parse_parameter_values(p_string,p_separator,l_values);
2250 for i in 1..l_values.count loop
2251 if l_values(i)=p_parameter or instr(l_values(i),p_parameter||'=')>0 then
2252 l_index:=i;
2253 exit;
2254 end if;
2255 end loop;
2256 if l_index is null then
2257 l_index:=l_values.count+1;
2258 end if;
2259 if p_value is null then
2260 l_values(l_index):=p_parameter;
2261 else
2262 l_values(l_index):=p_parameter||'='||p_value;
2263 end if;
2264 p_string:=make_string_from_list(l_values,p_separator); /* trailing separator*/
2265 Exception when others then
2266 log_n('Exception in update_property '||sqlerrm);
2267 raise;
2268 End;
2269
2270 /* p_property_string contains prop1=value1,prop2=value2 etc*/
2271 procedure merge_property(p_property in out nocopy property_tb,p_property_string varchar2,p_separator varchar2) is
2272 l_values value_tb;
2273 Begin
2274 if p_property_string is null then
2275 return;
2276 end if;
2277 parse_parameter_values(p_property_string,p_separator,l_values);
2278 for i in 1..l_values.count loop
2279 merge_property(p_property,l_values(i).parameter,null,l_values(i).value);
2280 end loop;
2281 Exception when others then
2282 log_n('Exception in merge_property '||sqlerrm);
2283 raise;
2284 End;
2285
2286 /*add/update property */
2287 procedure merge_property(p_property in out nocopy property_tb,p_property_name varchar2,p_property_type varchar2,p_property_value varchar2) is
2288 Begin
2289 for i in 1..p_property.count loop
2290 if p_property(i).property_name=p_property_name then
2291 p_property(i).property_type:=p_property_type;
2292 p_property(i).property_value:=p_property_value;
2293 exit;
2294 end if;
2295 end loop;
2296 p_property(p_property.count+1).property_name:=p_property_name;
2297 p_property(p_property.count).property_type:=p_property_type;
2298 p_property(p_property.count).property_value:=p_property_value;
2299 Exception when others then
2300 log_n('Exception in merge_property '||sqlerrm);
2301 raise;
2302 End;
2303
2304 procedure remove_property(p_property in out nocopy property_tb,p_property_name varchar2) is
2305 l_property property_tb;
2306 Begin
2307 for i in 1..p_property.count loop
2308 if p_property(i).property_name<>p_property_name then
2309 l_property(l_property.count+1):=p_property(i);
2310 end if;
2311 end loop;
2312 p_property:=l_property;
2313 Exception when others then
2314 log_n('Exception in remove_property '||sqlerrm);
2315 raise;
2316 End;
2317
2318 function get_property(p_property property_tb,p_property_name varchar2) return property_r is
2319 l_property property_r;
2320 Begin
2321 for i in 1..p_property.count loop
2322 if p_property(i).property_name=p_property_name then
2323 l_property:=p_property(i);
2324 exit;
2325 end if;
2326 end loop;
2327 return l_property;
2328 Exception when others then
2329 log_n('Exception in get_property '||sqlerrm);
2330 raise;
2331 End;
2332
2333 function get_property_string(p_property property_tb) return varchar2 is
2334 l_string varchar2(8000);
2335 Begin
2336 for i in 1..p_property.count loop
2337 if p_property(i).property_value is null then
2338 l_string:=l_string||p_property(i).property_name||',';
2339 else
2340 l_string:=l_string||p_property(i).property_name||'='||p_property(i).property_value||',';
2341 end if;
2342 end loop;
2343 if l_string is not null then
2344 l_string:=substr(l_string,1,length(l_string)-1);
2345 end if;
2346 return l_string;
2347 Exception when others then
2348 log_n('Exception in get_property_string '||sqlerrm);
2349 raise;
2350 End;
2351
2352 function get_cpu_count return number is
2353 Begin
2354 return to_number(get_vparameter('cpu_count'));
2355 Exception when others then
2356 log_n('Exception in get_cpu_count '||sqlerrm);
2357 raise;
2358 End;
2359
2360 /*example load_stats('before load'); */
2361 procedure load_stats(p_name varchar2,p_group varchar2) is
2362 ig pls_integer;
2363 Begin
2364 for i in 1..g_ssg.count loop
2365 if g_ssg(i).group_name=p_group then
2366 ig:=i;
2367 exit;
2368 end if;
2369 end loop;
2370 if ig is null then
2371 ig:=g_ssg.count+1;
2372 end if;
2373 --
2374 g_ssg(ig).group_name:=p_group;
2375 g_ssg(ig).session_stats(g_ssg(ig).session_stats.count+1).stats_name:=p_name;
2376 g_ssg(ig).session_stats(g_ssg(ig).session_stats.count).stats_time:=sysdate;
2377 load_session_stats(g_ssg(ig).session_stats(g_ssg(ig).session_stats.count).stats);
2378 load_session_waits(g_ssg(ig).session_stats(g_ssg(ig).session_stats.count).wait_events);
2379 Exception when others then
2380 log_n('Exception in load_stats '||sqlerrm);
2381 raise;
2382 End;
2383
2384 procedure load_session_stats(p_stats out nocopy stats_tb) is
2385 cursor c1 is select statname.name, mystat.value from v$mystat mystat,v$statname statname where mystat.statistic#=statname.statistic#
2386 order by statname.name;
2387 Begin
2388 for r1 in c1 loop
2389 p_stats(p_stats.count+1).stats_name:=r1.name;
2390 p_stats(p_stats.count).value:=r1.value;
2391 end loop;
2392 Exception when others then
2393 log_n('Exception in load_session_stats '||sqlerrm);
2394 raise;
2395 End;
2396
2397 procedure load_session_waits(p_wait_events out nocopy wait_event_tb) is
2398 cursor c1 is select event.event,event.total_waits,event.total_timeouts,round(event.time_waited) time_waited,
2399 round(event.average_wait) average_wait,round(event.max_wait) max_wait
2400 from v$session_event event where event.sid=get_session_id order by event.event;
2401 Begin
2402 for r1 in c1 loop
2403 p_wait_events(p_wait_events.count+1).event_name:=r1.event;
2404 p_wait_events(p_wait_events.count).total_waits:=r1.total_waits;
2405 p_wait_events(p_wait_events.count).total_timeouts:=r1.total_timeouts;
2406 p_wait_events(p_wait_events.count).time_waited:=r1.time_waited;
2407 p_wait_events(p_wait_events.count).average_wait:=r1.average_wait;
2408 p_wait_events(p_wait_events.count).max_wait:=r1.max_wait;
2409 end loop;
2410 Exception when others then
2411 log_n('Exception in load_session_waits '||sqlerrm);
2412 raise;
2413 End;
2414
2415 /*only 1 group object per group name */
2416 function get_session_stats_group(p_group varchar2) return session_stats_group_r is
2417 l_ssg session_stats_group_r;
2418 Begin
2419 for i in 1..g_ssg.count loop
2420 if g_ssg(i).group_name=p_group then
2421 l_ssg:=g_ssg(i);
2422 exit;
2423 end if;
2424 end loop;
2425 return l_ssg;
2426 Exception when others then
2427 log_n('Exception in get_session_stats_group '||sqlerrm);
2428 raise;
2429 End;
2430
2431 function get_ssg_index(p_group varchar2) return pls_integer is
2432 Begin
2433 for i in 1..g_ssg.count loop
2434 if g_ssg(i).group_name=p_group then
2435 return i;
2436 end if;
2437 end loop;
2438 return null;
2439 Exception when others then
2440 log_n('Exception in get_ssg_index '||sqlerrm);
2441 raise;
2442 End;
2443
2444 /*printing cleans the stats */
2445 procedure print_stats(p_group varchar2) is
2446 l_ssg pls_integer;
2447 Begin
2448 l_ssg:=get_ssg_index(p_group);
2449 if l_ssg is not null then
2450 diff_stats(g_ssg(l_ssg));
2451 diff_waits(g_ssg(l_ssg));
2452 print_stats(g_ssg(l_ssg));
2453 clean_stats(g_ssg(l_ssg));
2454 end if;
2455 Exception when others then
2456 log_n('Exception in print_stats '||sqlerrm);
2457 raise;
2458 End;
2459
2460 procedure clean_stats(p_group varchar2) is
2461 l_ssg pls_integer;
2462 Begin
2463 l_ssg:=get_ssg_index(p_group);
2464 if l_ssg is not null then
2465 clean_stats(g_ssg(l_ssg));
2466 end if;
2467 Exception when others then
2468 log_n('Exception in clean_stats '||sqlerrm);
2469 raise;
2470 End;
2471
2472 procedure clean_stats(p_ssg in out nocopy session_stats_group_r) is
2473 Begin
2474 p_ssg.session_stats.delete;
2475 Exception when others then
2476 log_n('Exception in clean_stats '||sqlerrm);
2477 raise;
2478 End;
2479
2480 procedure print_stats(p_ssg session_stats_group_r) is
2481 Begin
2482 if p_ssg.group_name is not null then
2483 log('===========================Stats=============================================================');
2484 log('Stats Group : '||p_ssg.group_name);
2485 for i in 2..p_ssg.session_stats.count loop
2486 print_stats(p_ssg.session_stats(i));
2487 end loop;
2488 end if;
2489 Exception when others then
2490 log_n('Exception in print_stats '||sqlerrm);
2491 raise;
2492 End;
2493
2494 procedure print_stats(p_session_stats session_stats_r) is
2495 Begin
2496 log('.');
2497 log('Stats : '||p_session_stats.stats_name);
2498 log('Stats Time : '||to_char(p_session_stats.stats_time,'MM/DD/YYYY HH24:MI:SS'));
2499 print_session_stats(p_session_stats.stats);
2500 log('.');
2501 print_session_wait(p_session_stats.wait_events);
2502 log('.');
2503 Exception when others then
2504 log_n('Exception in print_stats '||sqlerrm);
2505 raise;
2506 End;
2507
2508 procedure print_session_stats(p_stats stats_tb) is
2509 Begin
2510 log(rpad('Stats Name',64,'-')||'Value (Diff Value)');
2511 for i in 1..p_stats.count loop
2512 log(rpad(p_stats(i).stats_name,64,'-')||p_stats(i).value||' ('||p_stats(i).diff_value||')');
2513 end loop;
2514 Exception when others then
2515 log_n('Exception in print_session_stats '||sqlerrm);
2516 raise;
2517 End;
2518
2519 procedure print_session_wait(p_wait_events wait_event_tb) is
2520 Begin
2521 log(rpad('Wait Event',52,'-')||rpad('Total Waited (Diff)',30,'-')||rpad('Total Waits (Diff)',30,'-')||rpad('Timeouts (Diff)',20));
2522 for i in 1..p_wait_events.count loop
2523 log(rpad(p_wait_events(i).event_name,52,'-')||rpad(p_wait_events(i).time_waited||'('||p_wait_events(i).diff_time_waited||')',30,'-')||
2524 rpad(p_wait_events(i).total_waits||'('||p_wait_events(i).diff_total_waits||')',30,'-')||
2525 rpad(p_wait_events(i).total_timeouts||'('||p_wait_events(i).diff_total_timeouts||')',20));
2526 end loop;
2527 Exception when others then
2528 log_n('Exception in print_session_wait '||sqlerrm);
2529 raise;
2530 End;
2531
2532 procedure diff_stats(p_ssg in out nocopy session_stats_group_r) is
2533 Begin
2534 for i in 2..p_ssg.session_stats.count loop
2535 diff_session_stats(p_ssg.session_stats(i).stats,p_ssg.session_stats(i-1).stats);
2536 end loop;
2537 Exception when others then
2538 log_n('Exception in diff_stats '||sqlerrm);
2539 raise;
2540 End;
2541
2542 /*assumes stats is ordered by stats name */
2543 procedure diff_session_stats(p_new_stats in out nocopy stats_tb,p_old_stats stats_tb) is
2544 js pls_integer;
2545 flag boolean;
2546 Begin
2547 js:=1;
2548 for i in 1..p_new_stats.count loop
2549 flag:=false;
2550 for j in js..p_old_stats.count loop
2551 if p_old_stats(j).stats_name=p_new_stats(i).stats_name then
2552 flag:=true;
2553 js:=j+1;
2554 p_new_stats(i).diff_value:=p_new_stats(i).value-p_old_stats(j).value;
2555 exit;
2556 end if;
2557 end loop;
2558 if flag=false then
2559 js:=1;
2560 end if;
2561 end loop;
2562 Exception when others then
2563 log_n('Exception in diff_session_stats '||sqlerrm);
2564 raise;
2565 End;
2566
2567 /*assumes waits are ordered by event name */
2568 procedure diff_waits(p_ssg in out nocopy session_stats_group_r) is
2569 Begin
2570 for i in 2..p_ssg.session_stats.count loop
2571 diff_session_wait(p_ssg.session_stats(i).wait_events,p_ssg.session_stats(i-1).wait_events);
2572 end loop;
2573 Exception when others then
2574 log_n('Exception in diff_waits '||sqlerrm);
2575 raise;
2576 End;
2577
2578 procedure diff_session_wait(p_new_wait in out nocopy wait_event_tb,p_old_wait wait_event_tb) is
2579 js pls_integer;
2580 flag boolean;
2581 Begin
2582 js:=1;
2583 for i in 1..p_new_wait.count loop
2584 flag:=false;
2585 for j in js..p_old_wait.count loop
2586 if p_new_wait(i).event_name=p_old_wait(j).event_name then
2587 flag:=true;
2588 js:=j+1;
2589 p_new_wait(i).diff_total_waits:=p_new_wait(i).total_waits-p_old_wait(j).total_waits;
2590 p_new_wait(i).diff_total_timeouts:=p_new_wait(i).total_timeouts-p_old_wait(j).total_timeouts;
2591 p_new_wait(i).diff_time_waited:=p_new_wait(i).time_waited-p_old_wait(j).time_waited;
2592 end if;
2593 end loop;
2594 if flag=false then
2595 js:=1;
2596 end if;
2597 end loop;
2598 Exception when others then
2599 log_n('Exception in diff_session_wait '||sqlerrm);
2600 raise;
2601 End;
2602
2603 procedure kill_session(p_sid number,p_serial number) is
2604 Begin
2605 execute immediate 'alter system kill session '''||p_sid||','||p_serial||'''';
2606 Exception when others then
2607 log_n('Exception in kill_session '||sqlerrm);
2608 raise;
2609 End;
2610
2611 function get_table_count(p_table varchar2,p_where varchar2) return number is
2612 stmt varchar2(4000);
2613 TYPE RefCurTyp IS REF CURSOR;
2614 cv RefCurTyp;
2615 table_count number;
2616 Begin
2617 stmt:='select count(*) from '||p_table;
2618 if p_where is not null then
2619 stmt:=stmt||' where '||p_where;
2620 end if;
2621 if g_debug then
2622 log(stmt||get_time);
2623 end if;
2624 open cv for stmt;
2625 fetch cv into table_count;
2626 close cv;
2627 if g_debug then
2628 log('Result='||table_count||get_time);
2629 end if;
2630 return table_count;
2631 Exception when others then
2632 log_n('Exception in get_table_count '||sqlerrm);
2633 raise;
2634 End;
2635
2636 function is_number(p_number varchar2) return boolean is
2637 l_number number;
2638 Begin
2639 l_number:=to_number(p_number);
2640 return true;
2641 Exception when others then
2642 return false;
2643 End;
2644
2645 END BSC_AW_UTILITY;
[Home] [Help]