1 PACKAGE BODY CZ_FCE_COMPILE_UTILS AS
2 /* $Header: czfceutb.pls 120.13 2008/03/12 20:04:47 asiaston ship $ */
3
4 const_long_reverse CONSTANT NUMBER := const_max_long + const_max_long + 2;
5 ---------------------------------------------------------------------------------------
6 TYPE type_timing_structure IS RECORD (
7 start_time NUMBER
8 , total_time NUMBER
9 , active_flag PLS_INTEGER
10 );
11
12 TYPE type_timing_table IS TABLE OF type_timing_structure INDEX BY VARCHAR2(4000);
13 timing_table type_timing_table;
14 ---------------------------------------------------------------------------------------
15 FUNCTION assert_unsigned_byte ( p_int IN PLS_INTEGER ) RETURN BOOLEAN IS
16 BEGIN
17
18 IF ( p_int < const_min_unsigned_byte OR p_int > const_max_unsigned_byte ) THEN RETURN FALSE; END IF;
19 RETURN TRUE;
20
21 END assert_unsigned_byte;
22 ---------------------------------------------------------------------------------------
23 FUNCTION assert_byte ( p_int IN PLS_INTEGER ) RETURN BOOLEAN IS
24 BEGIN
25
26 IF ( p_int < const_min_byte OR p_int > const_max_byte ) THEN RETURN FALSE; END IF;
27 RETURN TRUE;
28
29 END assert_byte;
30 ---------------------------------------------------------------------------------------
31 FUNCTION assert_unsigned_word ( p_int IN PLS_INTEGER ) RETURN BOOLEAN IS
32 BEGIN
33
34 IF ( p_int < const_min_unsigned_word OR p_int > const_max_unsigned_word ) THEN RETURN FALSE; END IF;
35 RETURN TRUE;
36
37 END assert_unsigned_word;
38 ---------------------------------------------------------------------------------------
39 FUNCTION assert_word ( p_int IN PLS_INTEGER ) RETURN BOOLEAN IS
40 BEGIN
41
42 IF ( p_int < const_min_word OR p_int > const_max_word ) THEN RETURN FALSE; END IF;
43 RETURN TRUE;
44
45 END assert_word;
46 ---------------------------------------------------------------------------------------
47 FUNCTION assert_iconst ( p_int IN PLS_INTEGER ) RETURN BOOLEAN IS
48 BEGIN
49
50 IF ( p_int < const_iconst_min OR p_int > const_iconst_max ) THEN RETURN FALSE; END IF;
51 RETURN TRUE;
52
53 END assert_iconst;
54 ---------------------------------------------------------------------------------------
55 FUNCTION assert_integer ( p_int IN NUMBER ) RETURN BOOLEAN IS
56 BEGIN
57
58 IF ( p_int < const_min_integer OR p_int > const_max_integer ) THEN RETURN FALSE; END IF;
59 RETURN TRUE;
60
61 END assert_integer;
62 ---------------------------------------------------------------------------------------
63 FUNCTION assert_long ( p_int IN NUMBER ) RETURN BOOLEAN IS
64 BEGIN
65
66 IF ( p_int < const_min_long OR p_int > const_max_long ) THEN RETURN FALSE; END IF;
67 RETURN TRUE;
68
69 END assert_long;
70 ---------------------------------------------------------------------------------------
71 FUNCTION unsigned_byte ( p_int IN PLS_INTEGER ) RETURN RAW IS
72 BEGIN
73
74 RETURN UTL_RAW.SUBSTR ( UTL_RAW.CAST_FROM_BINARY_INTEGER ( p_int ), 4);
75
76 END unsigned_byte;
77 ---------------------------------------------------------------------------------------
78 FUNCTION byte ( p_int IN PLS_INTEGER ) RETURN RAW IS
79 BEGIN
80
81 RETURN UTL_RAW.SUBSTR ( UTL_RAW.CAST_FROM_BINARY_INTEGER ( p_int ), 4);
82
83 END byte;
84 ---------------------------------------------------------------------------------------
85 FUNCTION unsigned_word ( p_int IN PLS_INTEGER ) RETURN RAW IS
86 BEGIN
87
88 RETURN UTL_RAW.SUBSTR ( UTL_RAW.CAST_FROM_BINARY_INTEGER ( p_int ), 3);
89
90 END unsigned_word;
91 ---------------------------------------------------------------------------------------
92 FUNCTION word ( p_int IN PLS_INTEGER ) RETURN RAW IS
93 BEGIN
94
95 RETURN UTL_RAW.SUBSTR ( UTL_RAW.CAST_FROM_BINARY_INTEGER ( p_int ), 3);
96
97 END word;
98 ---------------------------------------------------------------------------------------
99 FUNCTION integer_raw ( p_int IN NUMBER ) RETURN RAW IS
100 BEGIN
101
102 RETURN UTL_RAW.CAST_FROM_BINARY_INTEGER ( p_int );
103
104 END integer_raw;
105 ---------------------------------------------------------------------------------------
106 FUNCTION long_raw ( p_int IN NUMBER ) RETURN RAW IS
107
108 l_num NUMBER;
109
110 BEGIN
111
112 IF ( p_int < 0 ) THEN
113
114 l_num := const_long_reverse + p_int;
115
116 ELSE
117
118 l_num := p_int;
119
120 END IF;
121
122 RETURN HEXTORAW( TO_CHAR ( l_num, 'FM0XXXXXXXXXXXXXXX'));
123 END long_raw;
124 ---------------------------------------------------------------------------------------
125 FUNCTION float_raw ( p_number IN NUMBER ) RETURN RAW IS
126 BEGIN
127
128 RETURN UTL_RAW.CAST_FROM_BINARY_FLOAT ( p_number );
129
130 EXCEPTION
131 WHEN OTHERS THEN
132
133 RAISE cz_cpl_internal_float;
134
135 END float_raw;
136 ---------------------------------------------------------------------------------------
137 FUNCTION double_raw ( p_number IN NUMBER ) RETURN RAW IS
138 BEGIN
139
140 RETURN UTL_RAW.CAST_FROM_BINARY_DOUBLE ( p_number );
141
142 END double_raw;
143 ---------------------------------------------------------------------------------------
144 ---------------------------------------------------------------------------------------
145 ---------------------------------------------------------------------------------------
146 PROCEDURE init_timing IS
147 BEGIN
148
149 timing_table.DELETE;
150
151 END init_timing;
152 ---------------------------------------------------------------------------------------
153 PROCEDURE start_timing ( p_label IN VARCHAR2 ) IS
154 BEGIN
155
156 IF ( NOT timing_table.EXISTS ( p_label )) THEN
157
158 timing_table ( p_label ).total_time := 0;
159 timing_table ( p_label ).active_flag := 0;
160
161 END IF;
162
163 IF ( timing_table ( p_label ).active_flag = 0 ) THEN
164
165 timing_table ( p_label ).start_time := DBMS_UTILITY.GET_TIME ();
166 timing_table ( p_label ).active_flag := 1;
167
168 END IF;
169 END start_timing;
170 ---------------------------------------------------------------------------------------
171 PROCEDURE end_timing ( p_label IN VARCHAR2 ) IS
172 BEGIN
173
174 IF ( timing_table.EXISTS ( p_label ) AND timing_table ( p_label ).active_flag = 1 ) THEN
175
176 timing_table ( p_label ).active_flag := 0;
177 timing_table ( p_label ).total_time := timing_table ( p_label ).total_time + (( DBMS_UTILITY.GET_TIME () - timing_table ( p_label ).start_time ) / 100.00 );
178
179 END IF;
180 END end_timing;
181 ---------------------------------------------------------------------------------------
182 PROCEDURE spool_timing_data ( p_run_id IN NUMBER ) IS
183
184 l_value VARCHAR2(4000);
185
186 BEGIN
187
188 l_value := timing_table.FIRST;
189
190 WHILE ( l_value IS NOT NULL ) LOOP
191
192 report_info (
193 p_message => l_value || ': ' || TO_CHAR ( timing_table ( l_value ).total_time )
194 , p_run_id => p_run_id
195 , p_model_id => null );
196 l_value := timing_table.NEXT ( l_value );
197
198 END LOOP;
199 END spool_timing_data;
200 ---------------------------------------------------------------------------------------
201 /**
202 This is an internal report procedure that is used by declared public report_
203 procedures.
204 */
205 PROCEDURE report_any_error (
206 p_urgency IN NUMBER
207 , p_message IN VARCHAR2
208 , p_run_id IN NUMBER
209 , p_model_id IN NUMBER
210 , p_ps_node_id IN NUMBER DEFAULT NULL
211 , p_rule_id IN NUMBER DEFAULT NULL
212 , p_error_stack IN VARCHAR2 DEFAULT NULL
213 , p_message_id IN VARCHAR2 DEFAULT NULL
214 ) IS
215 PRAGMA AUTONOMOUS_TRANSACTION;
216
217 l_fit_message CZ_DB_LOGS.MESSAGE%TYPE := NULL;
218 l_fit_error_stack CZ_DB_LOGS.ERROR_STACK%TYPE := NULL;
219
220 BEGIN
221
222 -- Truncate the message if it is more than 4000 characters
223
224 IF p_message IS NOT NULL THEN
225 l_fit_message := SUBSTR( p_message, 1, 4000 );
226 END IF;
227
228 -- Truncate the error stack if it is more than 4000 charcaters
229
230 IF p_error_stack IS NOT NULL THEN
231 l_fit_error_stack := SUBSTR( p_error_stack, 1, 4000 );
232 END IF;
233
234 /*
235 List of all columns in CZ_DB_LOGS. Not all columns are populated because some
236 of them are not relavent for FCE_COMPILE case.
237 LOGTIME
238 LOGUSER
239 URGENCY
240 CALLER
241 STATUSCODE
242 MESSAGE
243 CREATED_BY
244 CREATION_DATE
245 SESSION_ID
246 MESSAGE_ID
247 RUN_ID
248 MODEL_ID
249 OBJECT_TYPE
250 OBJECT_ID
251 MODEL_CONVERSION_SET_ID
252 ERROR_STACK
253 PS_NODE_ID
254 RULE_ID
255 Note: STATUSCODE is currently displayed by CZ Developer to show the section
256 where the validation exception happening. This must be changed to display
257 stack traces associated with the message. In FCE we are inserting urgency
258 code in place of status code.
259 */
260
261 INSERT INTO CZ_DB_LOGS (
262 LOGTIME, LOGUSER, URGENCY, CALLER, MESSAGE,
263 RUN_ID, MODEL_ID, PS_NODE_ID, RULE_ID, ERROR_STACK, STATUSCODE, message_id )
264 VALUES (
265 SYSDATE, USER, p_urgency, 'CZ_FCE_COMPILE', l_fit_message,
266 p_run_id, p_model_id, p_ps_node_id, p_rule_id, l_fit_error_stack, p_urgency, p_message_id );
267
268 COMMIT;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 -- TODO SV:Find out in the case of logging failures what is the right thing to do?
273 -- For now reraise the exception and let the caller decide what to do.
274 RAISE;
275 END report_any_error;
276 ---------------------------------------------------------------------------------------
277 /**
278 This procedure is used to report the FCE model compile warnings.
279 This procedure is responsible for truncating the message to the
280 database limit of 4000 characters. Its sets the
281 urgency level to 1.
282
283 Note: URGENCY - 0 - fatal error, 1 - warning, 2 - informational message;
284 */
285
286 PROCEDURE report_warning (
287 p_message IN VARCHAR2
288 , p_run_id IN NUMBER
289 , p_model_id IN NUMBER
290 , p_ps_node_id IN NUMBER DEFAULT NULL
291 , p_rule_id IN NUMBER DEFAULT NULL
292 , p_error_stack IN VARCHAR2 DEFAULT NULL
293 , p_message_id IN VARCHAR2 DEFAULT NULL
294 ) IS
295
296 BEGIN
297
298 REPORT_ANY_ERROR ( CONST_URGENCY_WARNING, p_message, p_run_id, p_model_id, p_ps_node_id, p_rule_id, p_error_stack, p_message_id );
299
300 END report_warning;
301 ---------------------------------------------------------------------------------------
302 /**
303 This procedure is used to report the FCE model compile system warnings.
304 System warnings are caused most likely due to code bugs or
305 environment level issues.
306 This procedure is responsible for truncating the message to the
307 database limit of 4000 characters. Its sets the
308 urgency level to 1.
309
310 Note: URGENCY - 0 - fatal error, 1 - warning, 2 - informational message;
311 */
312
313 PROCEDURE report_sys_warning (
314 p_message IN VARCHAR2
315 , p_run_id IN NUMBER
316 , p_model_id IN NUMBER
317 , p_ps_node_id IN NUMBER DEFAULT NULL
318 , p_rule_id IN NUMBER DEFAULT NULL
319 , p_error_stack IN VARCHAR2 DEFAULT NULL
320 , p_message_id IN VARCHAR2 DEFAULT NULL
321 ) IS
322
323 BEGIN
324
325 -- TODO: SV Consider added an explicit exception message prefix that states
326 -- this is a system type exception
327
328 REPORT_ANY_ERROR ( CONST_URGENCY_WARNING, p_message, p_run_id, p_model_id, p_ps_node_id, p_rule_id, p_error_stack, p_message_id );
329
330 END report_sys_warning;
331 ---------------------------------------------------------------------------------------
332 /**
333 This procedure is used to report the FCE model compile errors that
334 are caused due to the user defined model errors that can be fixed
335 by the user.
336 This procedure is responsible for truncating the message to the
337 database limit of 4000 characters. Its sets the
338 urgency level to 0.
339
340 Note: URGENCY - 0 - fatal error, 1 - warning, 2 - informational message;
341 */
342 PROCEDURE report_error (
343 p_message IN VARCHAR2
344 , p_run_id IN NUMBER
345 , p_model_id IN NUMBER
346 , p_ps_node_id IN NUMBER DEFAULT NULL
347 , p_rule_id IN NUMBER DEFAULT NULL
348 , p_error_stack IN VARCHAR2 DEFAULT NULL
349 , p_message_id IN VARCHAR2 DEFAULT NULL
350 ) IS
351
352 BEGIN
353
354 REPORT_ANY_ERROR ( CONST_URGENCY_ERROR, p_message, p_run_id, p_model_id, p_ps_node_id, p_rule_id, p_error_stack, p_message_id );
355
356 END report_error;
357 ---------------------------------------------------------------------------------------
358 /**
359 This procedure is used to report the FCE model compile errors that
363 a code bug etc.
360 are caused due to the unexpected failures that are caused by the
361 system errors. These errors are caused typically due to
362 environment issues (like data corruption etc.), can potentially
364
365 This procedure is responsible for truncating the message to the
366 database limit of 4000 characters. It sets the
367 urgency level to 0.
368
369 Note: URGENCY - 0 - fatal error, 1 - warning, 2 - informational message;
370 */
371 PROCEDURE report_system_error (
372 p_message IN VARCHAR2
373 , p_run_id IN NUMBER
374 , p_model_id IN NUMBER
375 , p_ps_node_id IN NUMBER DEFAULT NULL
376 , p_rule_id IN NUMBER DEFAULT NULL
377 , p_error_stack IN VARCHAR2 DEFAULT NULL
378 , p_message_id IN VARCHAR2 DEFAULT NULL
379 ) IS
380
381 BEGIN
382
383 -- TODO: SV Consider added an explicit exception message that states
384 -- this is a system type exception
385
386 REPORT_ANY_ERROR ( CONST_URGENCY_ERROR, p_message, p_run_id, p_model_id, p_ps_node_id, p_rule_id, p_error_stack, p_message_id );
387
388 END report_system_error;
389 ---------------------------------------------------------------------------------------
390 /**
391 This procedure is used to report the FCE model information messages that
392 are useful for debugging purposes or other information purposes.
393
394 This procedure is responsible for truncating the message to the
395 database limit of 4000 characters. It sets the
396 urgency level to 0.
397
398 Note: URGENCY - 0 - fatal error, 1 - warning, 2 - informational message;
399 */
400 PROCEDURE report_info (
401 p_message IN VARCHAR2
402 , p_run_id IN NUMBER
403 , p_model_id IN NUMBER
404 , p_ps_node_id IN NUMBER DEFAULT NULL
405 , p_rule_id IN NUMBER DEFAULT NULL
406 , p_error_stack IN VARCHAR2 DEFAULT NULL
407 , p_message_id IN VARCHAR2 DEFAULT NULL
408 ) IS
409
410 BEGIN
411
412 REPORT_ANY_ERROR ( CONST_URGENCY_INFORMATION, p_message, p_run_id, p_model_id, p_ps_node_id, p_rule_id, p_error_stack, p_message_id );
413
414 END report_info;
415 ---------------------------------------------------------------------------------------
416 /**
417 This function returns the full path of the model for the given model id.
418 For the root model id, only model name will be returned.
419 */
420 FUNCTION GET_MODEL_PATH(
421 p_model_id IN NUMBER,
422 p_model_path IN VARCHAR2 DEFAULT NULL)
423 RETURN VARCHAR2 IS
424 l_model_name VARCHAR2(255) := NULL;
425 BEGIN
426 IF p_model_path IS NULL THEN
427 BEGIN --select name from cz_devl_projects where devl_project_id=285121
428 EXECUTE IMMEDIATE 'SELECT NAME from cz_devl_projects where devl_project_id= :1'
429 INTO l_model_name
430 USING p_model_id;
431 RETURN '"' || l_model_name || '"';
432 EXCEPTION WHEN OTHERS THEN
433 RETURN TO_CHAR(p_model_id);
434 END;
435 ELSE
436 RETURN '"' || p_model_path || '"';
437 END IF;
438 END;
439 ---------------------------------------------------------------------------------------
440 /**
441 This function returns the full path of the rule for the given rule id.
442 */
443 FUNCTION GET_RULE_PATH(
444 p_rule_id IN NUMBER,
445 p_rule_name IN VARCHAR2 DEFAULT NULL)
446 RETURN VARCHAR2 IS
447 v_rule_type VARCHAR2(10) := 'RUL';
448 BEGIN
449 BEGIN
450 EXECUTE IMMEDIATE 'SELECT CASE WHEN rule_type = 30 THEN ''DCH'' WHEN rule_type=24 THEN ''XCP'' ELSE ''RUL'' END from cz_rules where rule_id = :1'
451 INTO v_rule_type
452 USING p_rule_id;
453 EXCEPTION WHEN OTHERS THEN
454 v_rule_type := 'RUL';
455 END;
456 RETURN '"' || CZ_DEVELOPER_UTILS_PVT.get_Rule_Folder_Path(p_rule_id, v_rule_type) || '"';
457 EXCEPTION WHEN OTHERS THEN
458 IF p_rule_name IS NULL THEN
459 RETURN TO_CHAR(p_rule_id);
460 ELSE
461 RETURN '"' || p_rule_name || '"';
462 END IF;
463 END;
464 ---------------------------------------------------------------------------------------
465 /**
466 This function returns the full path of the node for the given node id.
467 */
468 FUNCTION GET_NODE_PATH(
469 p_node_id IN NUMBER,
470 p_node_path IN VARCHAR2 DEFAULT NULL)
471 RETURN VARCHAR2 IS
472 l_node_name VARCHAR2(1000) := NULL;
473 BEGIN
474 IF p_node_path IS NULL THEN
475 BEGIN --select name from cz_devl_projects where devl_project_id=285121
476 EXECUTE IMMEDIATE 'SELECT NAME from cz_ps_nodes where PS_NODE_ID = :1'
477 INTO l_node_name
478 USING p_node_id;
479 RETURN '"' || l_node_name || '"';
480 EXCEPTION WHEN OTHERS THEN
481 RETURN TO_CHAR(p_node_id);
482 END;
483 ELSE
484 RETURN '"' || p_node_path || '"';
485 END IF;
486 END;
487 ---------------------------------------------------------------------------------------
488 /**
489 This function returns the full path of the property for the given property id.
490 Note: Full path is needed for properties, because property names must be unique,
491 and not allowed to be duplicate.
492 */
493 FUNCTION GET_PROPERTY_PATH(
494 p_prop_id IN NUMBER)
495 RETURN VARCHAR2 IS
496 v_prop_name VARCHAR2(255) := null;
497 BEGIN
498 EXECUTE IMMEDIATE 'select name from cz_properties where property_id=:1'
499 INTO v_prop_name
500 USING p_prop_id;
501 RETURN '"' || v_prop_name || '"';
502 EXCEPTION WHEN OTHERS THEN
503 BEGIN
504 EXECUTE IMMEDIATE 'select name from cz_system_properties_v where rule_id=:1'
505 INTO v_prop_name
506 USING p_prop_id;
507 RETURN '"' || v_prop_name || '"';
508 EXCEPTION WHEN OTHERS THEN
509 RETURN '"' || TO_CHAR(p_prop_id) || '"';
510 END;
511 END;
512 ---------------------------------------------------------------------------------------
513 END;