1 package BSC_AW_UTILITY AS
2 /*$Header: BSCAWUTS.pls 120.25 2006/03/27 14:46 vsurendr noship $*/
3
4 /*
5 Supported options
6 option -> calling program -> description
7
8 Common:
9 --------
10 DEBUG LOG -> ALL -> Turns on debug logging
11 TRACE -> ALL -> Turns trace on
12 UTL_FILE_LOG -> Loader -> specifies the dir for the child parallel processes to write the log file to
13 NO PARALLEL -> Loader -> Hint to turn off parallel load and aggregations
14 RECREATE KPI -> GDB -> drops and recreates the KPI if the kpi exists
15 SUMMARIZATION LEVEL -> GDB -> Adv summarization level
16
17 Less Common:
18 ------------
19 TABLESPACE -> GDB -> Specify the tablespace to create the AW worksace on
20 SEGMENTSIZE -> GDB -> Specify the segment size for the AW workspace
21 AGGREGATE TIME -> GDB -> Specifies that all levels of time be aggregated
22 PARTITION -> GDB -> Force Creates partitioned cubes for KPI, 10g and beyond
23 NO PARTITION -> GDB -> Force Non partitioned cubes for KPI, even if partitions possible
24 NUMBER PARTITION -> GDB -> Overrides default number of partitions
25 NO COMPRESSED COMPOSITE -> GDB -> No compressed composite, even if compressed composite possible
26 NO DISPLAY CUBE -> GDB -> Do not create display cubes. This means no partitions when there are avg measures
27 AGGREGATE TIME -> GDB -> Force aggregation at load on time dim rather than at query time
28 NO TARGET PARTITION -> GDB -> Do not partition dimsets that have targets at higher levels
29
30 Special Purpose:
31 ----------------
32 RECREATE PROGRAM -> GDB -> Recreates the dml load programs for the object
33 RECREATE CALENDAR -> Calendar Module -> Forces the recreation of the calendar
34 COMPRESSED COMPOSITE -> GDB -> Hint to the system to try and create the kpi with compressed composites for 10.1.0.4.
35
36 Advanced, Rarely used:
37 ----------------------
38 NO LIMIT CUBE COMPOSITE -> GDB -> Creates limit cubes without composite
39 NO DATACUBE -> GDB -> Creates individual cubes per measure in 10g
40 NO DETACH WORKSPACE -> Loader/Calendar -> Refreshes the calendar without detaching the workspace
41 FILE LOG -> used in bscawrdb, for internal purposes to turn on file debugging instead of fnd table debugging
42 */
43
44 --program runtime parameters
45 g_debug boolean;
46 g_debug_level varchar2(30);
47 g_stmt varchar2(32000);
48 g_job_wait_time_large constant number:=10;
49 g_job_wait_time_small constant number:=5;
50 g_max_wait_time constant number:=36000; --max system wait of 10 hrs
51 g_max_partitions constant number:=16;
52 g_infinite_loop constant number:=1000000;
53 g_exception exception;
54 g_db_version number;
55 g_newline constant varchar2(20):='
56 ';
57 g_trace_set boolean;
58 pragma exception_init(g_exception,-20000);
59 g_log_level constant number:=FND_LOG.LEVEL_STATEMENT;
60 g_upgrade_version constant number:=3;
61 g_parallel_load_cutoff constant number:=50000;/*more than these rows is parallel load if possible */
62 g_parallel_aggregate_cutoff constant number:=1000000;/*more than these composite nodes is parallel formula if possible*/
63 g_parallel_target_cutoff constant number:=1000000;/*more than these composite nodes is parallel target to actual copy if possible*/
64 ---TYPES------------
65 type varchar2_table is table of varchar2(400) index by varchar2(400);
66 type number_table is table of number index by varchar2(400);
67 type boolean_table is table of boolean index by varchar2(400);
68 --sqlerror is sqlcode and message
69 type sqlerror_r is record(
70 sql_code number,
71 action varchar2(40),--ignore etc
72 message varchar2(2000)
73 );
74 type sqlerror_tb is table of sqlerror_r index by pls_integer;
75 g_sqlerror sqlerror_tb;
76 --value_tb is used to parse out the options
77 type value_r is record(
78 parameter varchar2(8000),
79 value varchar2(8000)
80 );
81 type value_tb is table of value_r index by pls_integer;
82 type value_tv is table of value_r index by varchar2(200);
83 --
84 type property_r is record(
85 property_name varchar2(200),
86 property_type varchar2(200),
87 property_value varchar2(8000)
88 );
89 type property_tb is table of property_r index by pls_integer;
90 type property_tv is table of property_r index by varchar2(200);
91 --
92 type object_r is record(
93 object_name varchar2(300),
94 object_type varchar2(100)
95 );
96 type object_tb is table of object_r index by pls_integer;
97 --
98 --used to normalize a denorm parent child relation
99 type parent_child_r is record(
100 parent varchar2(200),
101 child varchar2(200),
102 status varchar2(40)
103 );
104 type parent_child_tb is table of parent_child_r index by pls_integer;
105 --
106 g_options value_tb;
107 --
108 type parallel_job_r is record(
109 job_name varchar2(100),--same name is used in the pipe
110 run_id integer,--we give it a id 1,2 3 etc
111 job_id integer,--dbms job id
112 start_time varchar2(40),--populate with get_time
113 end_time varchar2(40),
114 status varchar2(40),
115 sqlcode number,
116 message varchar2(2000) --if there is some error
117 );
118 type parallel_job_tb is table of parallel_job_r index by pls_integer;
119 g_parallel_jobs parallel_job_tb;
120 --
121 type new_values_r is record(
122 id number,
123 new_values dbms_sql.varchar2_table
124 );
125 type new_values_tb is table of new_values_r index by pls_integer;
126 g_values new_values_tb;
127 --
128 type all_tables_tb is table of all_tables%rowtype index by pls_integer;
129 -------
130 type partition_r is record(
131 partition_name varchar2(40), --P0
132 partition_value varchar2(40), --'0'
133 partition_position number
134 );
135 type partition_tb is table of partition_r index by pls_integer;
136 --
137 type partition_set_r is record( --contains the partitions
138 set_name varchar2(40),
139 partition_type varchar2(40), --range, list, hash
140 partition_column varchar2(32000), --for hash, we will have comma separated list
141 partition_column_data_type varchar2(32000), --data type
142 partitions partition_tb
143 );
144 --
145 type object_partition_r is record(
146 main_partition partition_set_r,
147 sub_partition partition_set_r
148 );
149 --
150 /*data structures to hold statistic information */
151 type stats_r is record(
152 stats_name varchar2(100),
153 value number,
154 diff_value number
155 );
156 type stats_tb is table of stats_r index by pls_integer;
157 --
158 type wait_event_r is record(
159 event_name varchar2(100),
160 total_waits number,
161 total_timeouts number,
162 time_waited number, /*100th of a second*/
163 average_wait number,/*100th of a second*/
164 max_wait number, /*100th of a second*/
165 --
166 diff_total_waits number,
167 diff_total_timeouts number,
168 diff_time_waited number
169 );
170 type wait_event_tb is table of wait_event_r index by pls_integer;
171 --
172 type session_stats_r is record(
173 stats_name varchar2(200),
174 stats_time date,
175 stats stats_tb,
176 wait_events wait_event_tb
177 );
178 type session_stats_tb is table of session_stats_r index by pls_integer;
179 --
180 type session_stats_group_r is record(
181 group_name varchar2(200),
182 session_stats session_stats_tb
183 );
184 type session_stats_group_tb is table of session_stats_group_r index by pls_integer;
185 --
186 g_ssg session_stats_group_tb;
187 --------
188 --procedures-------------------------------------------------------
189 function in_array(
190 p_array dbms_sql.varchar2_table,
191 p_value varchar2
192 ) return boolean;
193 function in_array(
194 p_array dbms_sql.number_table,
195 p_value number
196 ) return boolean;
197 function in_array(
198 p_array varchar2_table,
199 p_value varchar2
200 ) return boolean;
201 function get_parameter_value(p_string varchar2,p_parameter varchar2,p_separator varchar2) return varchar2;
202 function get_parameter_value(p_options value_tb,p_parameter varchar2) return varchar2 ;
203 function get_min(num1 number,num2 number) return number;
204 function contains(p_text varchar2,p_check varchar2) return boolean;
205 --used to populate created by etc
206 function get_who return number;
207 procedure delete_aw_object(p_object varchar2);
208 procedure execute_ddl_ne(p_stmt varchar2);
209 procedure execute_ddl(p_stmt varchar2);
210 procedure delete_table(p_table varchar2,p_where varchar2);
211 procedure resolve_into_value_r(
212 p_string varchar2,
213 p_value out nocopy value_r);
214 procedure parse_parameter_values(
215 p_string varchar2,
216 p_separator varchar2,
217 p_values out nocopy value_tb
218 );
219 procedure normalize_denorm_relation(p_relation in out nocopy parent_child_tb);
220 procedure make_stmt_for_aw(p_program varchar2,p_stmt in out nocopy varchar2,p_type varchar2);
221 procedure add_g_commands(p_commands in out nocopy dbms_sql.varchar2_table,p_command varchar2);
222 procedure trim_g_commands(p_commands in out nocopy dbms_sql.varchar2_table,p_trim number,p_add varchar2) ;
223 procedure exec_program_commands(p_program varchar2,p_commands dbms_sql.varchar2_table);
224 procedure dmp_g_options(p_options value_tb);
225 procedure create_temp_tables;
226 procedure parse_out_agg_function(p_formula varchar2,p_noagg_formula out nocopy varchar2);
227 function get_max(p_array dbms_sql.number_table) return number;
228 procedure exec_aw_program_aggmap(p_name varchar2,p_commands dbms_sql.varchar2_table,p_type varchar2);
229 procedure exec_aggmap_commands(p_aggmap varchar2,p_commands dbms_sql.varchar2_table);
230 function does_table_have_data(p_table varchar2,p_where varchar2) return varchar2;
231 function is_std_aggregation_function(p_agg_formula varchar2) return varchar2;
232 function is_in_between(p_input number,p_left number,p_right number) return boolean ;
233 function is_ascii(p_char varchar2) return boolean ;
234 function is_string_present(
235 p_string varchar2,
236 p_text varchar2,
237 p_location out nocopy dbms_sql.number_table
238 ) return boolean ;
239 procedure replace_string(
240 p_string in out nocopy varchar2,
241 p_old_text varchar2,
242 p_new_text varchar2,
243 p_start_array dbms_sql.number_table
244 );
245 function get_adv_sum_profile return number ;
246 FUNCTION get_apps_schema_name RETURN VARCHAR2;
247 function get_table_owner(p_table varchar2) return varchar2 ;
248 procedure truncate_table(p_table varchar2);
249 function get_db_version return number ;
250 procedure write_to_file(p_type varchar2,p_message varchar2,p_new_line boolean);
251 procedure log(p_message varchar2);
252 procedure log_s(p_message varchar2) ;
253 procedure log_n(p_message varchar2);
254 procedure convert_varchar2_to_table(
255 p_string varchar2,
256 p_limit number,
257 p_table out nocopy dbms_sql.varchar2_table
258 );
259 procedure drop_db_object_ne(p_object varchar2,p_object_type varchar2);
260 procedure drop_db_object(p_object varchar2,p_object_type varchar2);
261 procedure execute_stmt(p_stmt varchar2);
262 procedure sleep(p_sleep_time integer,p_random_time integer);
263 procedure remove_array_element(p_array in out nocopy dbms_sql.varchar2_table,p_object varchar2);
264 procedure start_job(
265 p_job_name varchar2,
266 p_run_id number,
267 p_process varchar2,
268 p_options varchar2
269 );
270 function get_parallel_job(p_job_name varchar2) return parallel_job_r;
271 procedure wait_on_jobs(
272 p_options varchar2,
273 p_job_status out nocopy parallel_job_tb
274 );
275 procedure update_job_status(p_parallel_job in out nocopy parallel_job_r);
276 function get_pipe_message(p_pipe_name varchar2) return varchar2;
277 procedure remove_pipe(p_pipe_name varchar2);
278 function is_job_running(p_job_id number) return varchar2;
279 procedure clean_up_jobs(p_options varchar2);
280 function can_launch_jobs(p_number_jobs number) return varchar2;
281 function count_jobs_running return number;
282 function get_vparameter(p_name varchar2) return varchar2;
283 function get_option_string return varchar2;
284 function get_session_id return number;
285 procedure create_pipe(p_pipe_name varchar2);
286 procedure send_pipe_message(p_pipe_name varchar2,p_message varchar2);
287 procedure dmp_parallel_jobs;
288 function make_string_from_list(p_list dbms_sql.varchar2_table) return varchar2;
289 function make_string_from_list(p_list dbms_sql.varchar2_table,p_separator varchar2) return varchar2;
290 procedure parse_parameter_values(
291 p_string varchar2,
292 p_separator varchar2,
293 p_values out nocopy dbms_sql.varchar2_table
294 );
295 function get_parameter_value(p_parameter varchar2) return varchar2;
299 function get_dbms_time return number;
296 procedure add_option(p_options varchar2,p_option_value varchar2,p_separator varchar2);
297 procedure execute_stmt_ne(p_stmt varchar2);
298 function get_hash_value(p_string varchar2,p_start number,p_end number) return varchar2 ;
300 function get_random_number(p_seed number) return number;
301 procedure merge_array(p_array in out nocopy dbms_sql.varchar2_table,p_values dbms_sql.varchar2_table);
302 procedure merge_value(p_array in out nocopy dbms_sql.varchar2_table,p_value varchar2);
303 procedure subtract_array(p_array in out nocopy dbms_sql.varchar2_table,p_values dbms_sql.varchar2_table);
304 procedure set_aw_trace;
305 procedure dmp_values(p_table dbms_sql.varchar2_table,p_text varchar2);
306 function get_sqlerror(p_sqlcode number,p_action varchar2) return sqlerror_r;
307 procedure add_sqlerror(p_sqlcode number,p_action varchar2,p_message varchar2);
308 function is_sqlerror(p_sqlcode number,p_action varchar2) return boolean;
309 procedure remove_sqlerror(p_sqlcode number,p_action varchar2);
310 procedure remove_all_sqlerror;
311 function compare_pc_relations(p_pc_1 parent_child_tb,p_pc_2 parent_child_tb) return number;
312 procedure init_is_new_value;
313 procedure init_is_new_value(p_index number);
314 function is_new_value(p_value varchar2,p_index number) return boolean;
315 function is_new_value(p_value number,p_index number) return boolean;
316 function order_array(p_array dbms_sql.varchar2_table) return dbms_sql.varchar2_table;
317 function make_upper(p_array dbms_sql.varchar2_table) return dbms_sql.varchar2_table;
318 function is_avg_aggregation_function(p_agg_formula varchar2) return varchar2;
319 procedure get_db_lock(p_lock_name varchar2);
320 procedure release_db_lock(p_lock_name varchar2);
321 function get_lock_handle(p_lock_name varchar2) return varchar2 ;
322 function can_launch_dbms_job(p_number_jobs number) return varchar2;
323 function get_closest_2_power_number(p_number number) return number;
324 function get_db_table_parameters(p_table varchar2,p_owner varchar2) return all_tables_tb;
325 procedure analyze_table(p_table varchar2,p_owner varchar2);
326 procedure analyze_table(p_table varchar2,p_interval number);
327 procedure log_fnd(p_message varchar2,p_severity number);
328 procedure set_option(p_parameter varchar2,p_value varchar2);
329 function get_g_commands(p_commands dbms_sql.varchar2_table,p_index number) return varchar2;
330 procedure get_upper_trim_hier(p_parent_child parent_child_tb,p_seed varchar2,p_trim_parent_child in out nocopy parent_child_tb);
331 procedure get_lower_trim_hier(p_parent_child parent_child_tb,p_seed varchar2,p_trim_parent_child in out nocopy parent_child_tb);
332 procedure get_parent_values(p_parent_child parent_child_tb,p_child varchar2,p_parents out nocopy parent_child_tb);
333 procedure get_child_values(p_parent_child parent_child_tb,p_parent varchar2,p_children out nocopy parent_child_tb);
334 procedure get_all_parents(p_parent_child parent_child_tb,p_child varchar2,p_parents in out nocopy dbms_sql.varchar2_table);
335 procedure get_all_children(p_parent_child parent_child_tb,p_parent varchar2,p_children in out nocopy dbms_sql.varchar2_table);
336 procedure update_property(p_string in out nocopy varchar2,p_parameter varchar2,p_value varchar2,p_separator varchar2);
337 procedure merge_property(p_property in out nocopy property_tb,p_property_name varchar2,p_property_type varchar2,p_property_value varchar2);
338 procedure remove_property(p_property in out nocopy property_tb,p_property_name varchar2);
339 function get_property(p_property property_tb,p_property_name varchar2) return property_r;
340 procedure merge_property(p_property in out nocopy property_tb,p_property_string varchar2,p_separator varchar2);
341 function get_property_string(p_property property_tb) return varchar2;
342 procedure merge_array(p_array in out nocopy dbms_sql.number_table,p_values dbms_sql.number_table);
343 procedure merge_value(p_array in out nocopy dbms_sql.number_table,p_value number);
344 function get_cpu_count return number;
345 procedure load_stats(p_name varchar2,p_group varchar2);
346 procedure load_session_stats(p_stats out nocopy stats_tb);
347 procedure load_session_waits(p_wait_events out nocopy wait_event_tb);
348 function get_session_stats_group(p_group varchar2) return session_stats_group_r;
349 procedure print_stats(p_group varchar2);
350 procedure clean_stats(p_group varchar2);
351 procedure clean_stats(p_ssg in out nocopy session_stats_group_r);
352 procedure print_stats(p_ssg session_stats_group_r);
353 procedure print_stats(p_session_stats session_stats_r);
354 procedure print_session_stats(p_stats stats_tb);
355 procedure print_session_wait(p_wait_events wait_event_tb);
356 procedure diff_stats(p_ssg in out nocopy session_stats_group_r);
357 procedure diff_session_stats(p_new_stats in out nocopy stats_tb,p_old_stats stats_tb);
358 procedure diff_waits(p_ssg in out nocopy session_stats_group_r);
359 procedure diff_session_wait(p_new_wait in out nocopy wait_event_tb,p_old_wait wait_event_tb);
360 function get_ssg_index(p_group varchar2) return pls_integer;
361 procedure kill_session(p_sid number,p_serial number);
362 function is_PT_aggregation_function(p_agg_formula varchar2) return varchar2 ;
363 function get_array_index(
364 p_array dbms_sql.varchar2_table,
365 p_value varchar2
366 ) return number;
367 function get_array_index(
368 p_array dbms_sql.number_table,
369 p_value number
370 ) return number;
371 function is_CC_aggregation_function(p_agg_formula varchar2) return varchar2;
372 procedure check_jobs(p_parallel_jobs in out nocopy parallel_job_tb);
373 function check_all_jobs_complete(p_parallel_jobs parallel_job_tb) return boolean;
374 procedure wait_on_jobs_sleep(p_options varchar2,p_job_status out nocopy parallel_job_tb);
375 function get_table_count(p_table varchar2,p_where varchar2) return number;
376 function is_number(p_number varchar2) return boolean ;
377 procedure create_perm_tables;
378 --procedures-------------------------------------------------------
379 procedure init_all(p_debug boolean);
380 procedure init_all_procedures;
381 function get_time return varchar2;
382 procedure open_file(p_object_name varchar2);
383 -------------------------------------------------------------------
385 END BSC_AW_UTILITY;
384
[Home] [Help]