1 PACKAGE BODY JTF_DIAGNOSTIC_ADAPTUTIL AS
2 /* $Header: jtfdiagadptutl_b.pls 120.13.12020000.2 2012/07/17 12:06:43 rlandows ship $ */
3
4 -----------------------------------------------------------
5 -- checkValidApi takes in the test package name and
6 -- queries the all_arguments data dictionary to check if the
7 -- api required for this to be a valid diagnostic test exist.
8 -- checks for the runTest procedure call in the package name
9 -- and then checks if the API exists. Currently it is just the
10 -- runTest method that is checked.
11 -- probably not the best wasy to do this - need to look further
12 -- into the dbms_describe package and describe_procedure calls.
13 -----------------------------------------------------------
14
15
16
17 FUNCTION checkValidApi(packageName IN VARCHAR2) RETURN INTEGER IS
18 arg1 VARCHAR2(100);
19 arg2 VARCHAR2(100);
20 arg3 VARCHAR2(100);
21 maxPos INTEGER;
22 b_true NUMBER := 1;
23 b_false NUMBER :=0;
24 BEGIN
25 -- checking if a valid RUNTEST API has been written.
26 select nvl(TYPE_NAME,PLS_TYPE) into arg1
27 from all_arguments z
28 where UPPER(z.package_name) = UPPER(packageName)
29 and upper(z.owner) in ('APPS', 'JTF', 'APPLSYS')
30 and UPPER(z.object_name) = 'RUNTEST'
31 and z.position = 1
32 and z.IN_OUT = 'IN';
33
34 select nvl(z.TYPE_NAME,z.PLS_TYPE) into arg2
35 from all_arguments z
36 where UPPER(z.package_name) = UPPER(packageName)
37 and upper(z.owner) in ('APPS', 'JTF', 'APPLSYS')
38 and UPPER(z.object_name) = 'RUNTEST'
39 and z.position = 2
40 and z.IN_OUT = 'OUT';
41
42 select nvl(z.TYPE_NAME,z.PLS_TYPE) into arg3
43 from all_arguments z
44 where UPPER(z.package_name) = UPPER(packageName)
45 and upper(z.owner) in ('APPS', 'JTF', 'APPLSYS')
46 and UPPER(z.object_name) = 'RUNTEST'
47 and z.position = 3
48 and z.IN_OUT = 'OUT';
49
50 -- make sure that function has correct no of params.
51 select max(z.position) into maxPos
52 from all_arguments z
53 where UPPER(z.package_name) = UPPER(packageName)
54 and upper(z.owner) in ('APPS', 'JTF', 'APPLSYS')
55 and UPPER(z.object_name) = 'RUNTEST';
56 IF maxPos > 3 THEN
57 return b_false;
58 END IF;
59 IF (UPPER(arg1) <> 'JTF_DIAG_INPUTTBL') THEN
60 return b_false;
61 END IF;
62 IF (UPPER(arg2) <> 'JTF_DIAG_REPORT') THEN
63 return b_false;
64 END IF;
65 IF (UPPER(arg3) <> 'CLOB') THEN
66 return b_false;
67 END IF;
68 return b_true;
69 EXCEPTION
70 WHEN others THEN
71 return b_false;
72 END checkValidApi;
73
74
75 -----------------------------------------------------------
76 -- checkValidPackage takes in the test package name and
77 -- queries the all_objects data dictionary to check if the
78 -- package and package body for the specified package exist
79 -- and are marked as valid objects
80 -- returns '1' if valid and '0' if not valid
81 -----------------------------------------------------------
82
83 FUNCTION checkValidPackage(packageName IN VARCHAR2) RETURN INTEGER IS
84 s_status VARCHAR2(30);
85 b_status VARCHAR2(30);
86 b_true NUMBER := 1;
87 b_false NUMBER :=0;
88 BEGIN
89
90 -- OWNER CHANGE
91 select y.status into s_status from all_objects y
92 where y.object_name = UPPER(packageName)
93 and y.object_type = 'PACKAGE'
94 and upper(y.owner) in ('APPS', 'JTF', 'APPLSYS');
95
96 select y.status into b_status from all_objects y
97 where y.object_name = UPPER(packageName)
98 and y.object_type = 'PACKAGE BODY'
99 and upper(y.owner) in ('APPS', 'JTF', 'APPLSYS');
100
101 IF s_status = 'VALID' and b_status = 'VALID' THEN
102 return b_true;
103 ELSE
104 return b_false;
105 END IF;
106 EXCEPTION
107 WHEN others THEN
108 return b_false;
109 END checkValidPackage;
110
111 -----------------------------------------------------------
112 -- checkPackageExists takes in the test package name and
113 -- queries the all_objects data dictionary to check if the
114 -- package and package body for the specified package exist
115 -- returns '1' if exists '0' if doesnt exist.
116 -----------------------------------------------------------
117
118 FUNCTION checkPackageExists(packageName IN VARCHAR2) RETURN INTEGER IS
119 v_pspec VARCHAR2(100);
120 v_pbody VARCHAR2(100);
121 b_true NUMBER := 1;
122 b_false NUMBER :=0;
123 BEGIN
124 select y.owner into v_pspec from all_objects y
125 where y.object_name = UPPER(packageName)
126 and y.object_type = 'PACKAGE'
127 and upper(y.owner) in ('APPS', 'JTF', 'APPLSYS');
128
129 select y.owner into v_pbody from all_objects y
130 where y.object_name = UPPER(packageName)
131 and y.object_type = 'PACKAGE BODY'
132 and upper(y.owner) in ('APPS', 'JTF', 'APPLSYS');
133
134 return b_true;
135 EXCEPTION
136 WHEN others THEN
137 -- logging here..
138 return b_false;
139 END;
140
141 -----------------------------------------------------------
142 -- getVersion takes in the test package name and returns the
143 -- RCS Header information for the body of the PLSQL package body
144 -- file.
145 -----------------------------------------------------------
146
147 FUNCTION getVersion(packageName IN VARCHAR2) RETURN VARCHAR2 IS
148 rcs_id VARCHAR2(4000) := 'Version Unknown.';
149 BEGIN
150 select text into rcs_id from all_source
151 where name like UPPER(packageName)
152 and upper(owner) in ('APPS', 'JTF', 'APPLSYS')
153 and text like '%$%Header%ship%' and
154 type like 'PACKAGE BODY';
155 return rcs_id;
156 EXCEPTION
157 WHEN others THEN
158 -- logging here..
159 return 'Version Unknown.';
160 END;
161
162
163 -----------------------------------------------------------
164 -- constructReport takes in four parameters.
165 -- status - the result of the test - either SUCCESS or FAILED
166 -- errStr - the error that has been populated by the user
167 -- could be SQLERRM or a user defined error message
168 -- fixInfo - string to help the user to fix the associated problem
169 -- isFatal - either TRUE or FALSE (sring representations)
170 -----------------------------------------------------------
171
172 FUNCTION constructReport(status IN VARCHAR2 DEFAULT 'FAILED',
173 errStr IN VARCHAR2 DEFAULT 'Internal Error',
174 fixInfo IN VARCHAR2 DEFAULT 'No Fix Information Available',
175 isFatal IN VARCHAR2 DEFAULT 'FALSE') RETURN JTF_DIAG_REPORT IS
176 tempReport JTF_DIAG_REPORT;
177 BEGIN
178 b_html_on := false;
179 tempReport := JTF_DIAG_REPORT(status,
180 errStr,
181 fixInfo,
182 isFatal);
183 return tempReport;
184 EXCEPTION
185 WHEN others THEN
186 return null;
187 END constructReport;
188
189 ---------------------------------------------------------------------
190 -- initialise a inputTable object and return it to the
191 -- caller method. A table of a single empty JTF_DIAG_INPUTS
192 -- is created and then removed with the call to trim -
193 -- this initializes the collection
194 ---------------------------------------------------------------------
195
196 FUNCTION initInputTable RETURN JTF_DIAG_INPUTTBL IS
197 temp JTF_DIAG_INPUTTBL;
198 BEGIN
199 temp := JTF_DIAG_INPUTTBL(JTF_DIAG_INPUTS(-1,'',''));
200 temp.trim;
201 return temp;
202 EXCEPTION
203 WHEN others THEN
204 return null;
205 END;
206
207 ---------------------------------------------------------------------
208 -- initialise a reportTable object and return it to the
209 -- caller method. A table of a single empty JTF_DIAG_REPORT
210 -- is created and then removed with the call to trim -
211 -- this initializes the collection
212 ---------------------------------------------------------------------
213
214 FUNCTION initReportTable RETURN JTF_DIAG_REPORTTBL IS
215 tempRpt JTF_DIAG_REPORT;
216 temp JTF_DIAG_REPORTTBL;
217 BEGIN
218 tempRpt := JTF_DIAG_REPORT('','','','');
219 temp := JTF_DIAG_REPORTTBL(tempRpt);
220 temp.trim;
221 return temp;
222 EXCEPTION
223 WHEN others THEN
224 return null;
225 END;
226
227 ----------------------------------------------------------------------
228 -- initVarcharTabble returns an empty table of VARCHAR2(4000)
229 ----------------------------------------------------------------------
230
231 FUNCTION initVarcharTable RETURN JTF_VARCHAR2_TABLE_4000 IS
232 temp JTF_VARCHAR2_TABLE_4000;
233 BEGIN
234 temp := JTF_VARCHAR2_TABLE_4000('');
235 temp.trim;
236 return temp;
237 EXCEPTION
238 WHEN others THEN
239 return null;
240 END;
241
242
243 ----------------------------------------------------------------------
244 -- initReportClob returns an initialised CLOB
245 ----------------------------------------------------------------------
246
247 FUNCTION initReportClob RETURN CLOB IS
248 temp CLOB;
249 BEGIN
250 dbms_lob.createTemporary(temp,true,dbms_lob.call);
251 return temp;
252 EXCEPTION
253 WHEN others THEN
254 -- logging here
255 return null;
256 END;
257
258
259 ----------------------------------------------------------------------
260 -- getReportClob returns the CLOB containing the session report.
261 ----------------------------------------------------------------------
262
263 FUNCTION getReportClob RETURN CLOB IS
264 BEGIN
265 return reportClob;
266 END;
267
268
269
270 ----------------------------------------------------------------------
271 -- compareResults takes a 3 arguments the operator that is to be performed
272 -- the expected String value and the String value that is to be tested
273 -- i.e passing in = 'string1' 'StRiNg' would evaluate to true as the
274 -- two strings match.
275 ----------------------------------------------------------------------
276
277 FUNCTION compareResults(oper IN VARCHAR2,
278 arg1 IN VARCHAR2,
279 arg2 IN VARCHAR2) RETURN BOOLEAN IS
280 BEGIN
281 IF (oper = '=') THEN
282 IF (UPPER(arg1) = UPPER(arg2)) THEN
283 return true;
284 ELSE
285 return false;
286 END IF;
287 ELSE
288 return false;
289 END IF;
290 EXCEPTION
291 WHEN others THEN
292 -- logging here
293 return false;
294 END;
295
296
297 ----------------------------------------------------------------------
298 -- compareResults takes a 3 arguments the operator that is to be performed
299 -- the expected value and the value that is to be tested
300 -- i.e passing in > 50 1 would evaluate to true as 50 IS greater than
301 -- 1 etc... > 1 50 would evaluate to false.
302 ----------------------------------------------------------------------
303
304 FUNCTION compareResults(oper IN VARCHAR2,
305 arg1 IN INTEGER,
306 arg2 IN INTEGER) RETURN BOOLEAN IS
307 BEGIN
308 IF (oper = '=') THEN
309 IF (arg1 = arg2) THEN
310 return true;
311 ELSE
312 return false;
313 END IF;
314 END IF;
315 IF (oper = '>') THEN
316 IF (arg1 > arg2) THEN
317 return true;
318 ELSE
319 return false;
320 END IF;
321 END IF;
322 IF (oper = '<') THEN
323 IF (arg1 < arg2) THEN
324 return true;
325 ELSE
326 return false;
327 END IF;
328 END IF;
329 EXCEPTION
330 WHEN others THEN
331 return false;
332 END;
333
334 ----------------------------------------------------------------------
335 -- extractVersion takes the full version string as argument i.e the
336 -- full RCS_ID and strips out the version number of the test. i.e
337 -- it is assuming that RCS_ID tags are in the format of :
338 -- '$Header: <filename>.sql xxx.xx yyyy/mm/dd hh:mm:ss <userid> <ship status>';
339 -- and in this case the version i.e xxx.xx would be returned.
340 ----------------------------------------------------------------------
341
342 FUNCTION extractVersion(versionStr IN VARCHAR2) RETURN VARCHAR2 IS
343 startPos INTEGER;
344 endPos INTEGER;
345 numChars INTEGER;
346 tempStr VARCHAR2(100);
347 BEGIN
348 startPos := instr(versionStr,' ',1,3);
349 endPos := instr(versionStr,' ',startPos+1,1);
350 numChars := endPos - startPos;
351 tempStr := substr(versionStr,startPos,numChars);
352 return tempStr;
353 EXCEPTION
354 WHEN others THEN
355 return 'No Version';
356 END;
357
358 ----------------------------------------------------------------------
359 -- setUpVars passes in a reference to an out variable reportCLOB
360 -- which is of type CLOB - this procedure initializes this clob
361 ----------------------------------------------------------------------
362
363
364 PROCEDURE setUpVars IS
365 BEGIN
366 b_html_on := false;
367 reportClob:= initReportClob; -- initialize reportClob
368 EXCEPTION
369 WHEN others THEN
370 -- logging here
371 null;
372 END;
373
374 ----------------------------------------------------------------------
375 -- addInput takes a table of JTF_DIAG_INPUTS in the form JTF_DIAG_INPUTTBL
376 -- a varchar2 representing the variable to add and a varchar2 representing
377 -- the value to add and this function will add create a new JTF_DIAG_INPUTS
378 -- object from the variable and value and add this JTF_DIAG_INPUTS to
379 -- a table of JTF_DIAG_INPUTS (JTF_DIAG_INPUTTBL) and return this. As this
380 -- method is overloaded and no "showValue" is passed - this field is
381 -- set as TRUE or VISIBLE
382 ----------------------------------------------------------------------
383
384 FUNCTION addInput(inputs IN JTF_DIAG_INPUTTBL,
385 var IN VARCHAR2,
386 val IN VARCHAR2) RETURN JTF_DIAG_INPUTTBL IS
387 tempInput JTF_DIAG_INPUTS;
388 tempInputTable JTF_DIAG_INPUTTBL;
389 valueStrTestInfo VARCHAR2(100);
390 BEGIN
391 tempInputTable := inputs;
392 tempInput := JTF_DIAG_INPUTS(VISIBLE,var,val);
393 tempInputTable.extend(1);
394 tempInputTable(tempInputTable.COUNT) := tempInput;
395 return tempInputTable;
396 EXCEPTION
397 WHEN others THEN
398 -- logging here...
399 return inputs;
400 END;
401
402
403
404 ----------------------------------------------------------------------
405 -- addInput takes a table of JTF_DIAG_INPUTS in the form JTF_DIAG_INPUTTBL
406 -- a varchar2 representing the variable to add and a varchar2 representing
407 -- the value to add and this function will add create a new JTF_DIAG_INPUTS
408 -- object from the variable and value and add this JTF_DIAG_INPUTS to
409 -- a table of JTF_DIAG_INPUTS (JTF_DIAG_INPUTTBL) and return this.
410 -- showValue can either be set to VISIBLE or HIDDEN. This is to indicate
411 -- if the variable is a confidential field.
412 ----------------------------------------------------------------------
413
414
415 FUNCTION addInput(inputs IN JTF_DIAG_INPUTTBL,
416 var IN VARCHAR2,
417 val IN VARCHAR2,
418 showValue IN BOOLEAN) RETURN JTF_DIAG_INPUTTBL IS
419 tempInput JTF_DIAG_INPUTS;
420 tempInputTable JTF_DIAG_INPUTTBL;
421 valueStrTestInfo VARCHAR2(100);
422 hidval INTEGER;
423 BEGIN
424 tempInputTable := inputs;
425 IF showValue THEN
426 hidVal := VISIBLE;
427 ELSE
428 hidVal := HIDDEN;
429 END IF;
430 tempInput := JTF_DIAG_INPUTS(hidVal,var,val);
431 tempInputTable.extend(1);
432 tempInputTable(tempInputTable.COUNT) := tempInput;
433 return tempInputTable;
434 EXCEPTION
435 WHEN others THEN
436 -- logging here...
437 return inputs;
438 END;
439
440
441 ----------------------------------------------------------------------
442 -- getInputValue takes the argument name that we want the associated
443 -- value for, and the JTF_DIAG_INPUTTBL of objects (table of JTF_DIAG_INPUTS)
444 -- the associated value is extracted from the JTF_DIAG_INPUTTBL and returned
445 -- for the passed in argument name.
446 ----------------------------------------------------------------------
447
448 FUNCTION getInputValue(argName IN VARCHAR2,
449 inputs IN JTF_DIAG_INPUTTBL) RETURN VARCHAR2 IS
450 input JTF_DIAG_INPUTS;
451 BEGIN
452 FOR v_counter IN 1..inputs.COUNT LOOP
453 input := inputs(v_counter);
454 IF UPPER(inputs(v_counter).name) = UPPER(argName) THEN
455 return inputs(v_counter).value;
456 END IF;
457 END LOOP;
458 return NULL;
459 END;
460
461 -------------------------------------------------------------
462 -- AddSafeStringToReport takes an input string containing
463 -- characters which are illegal in Xml.This function will
464 -- replace those characters with Xml complaint characters
465 -- , add <span> tags to make it Xml Parser complaint and then
466 -- add this string to report.
467 -------------------------------------------------------------
468 Procedure AddSafeStringToReport(reportStr In Long)
469 Is
470 tempReportStr LONG;
471 tempClob CLOB;
472 strSize INTEGER;
473 BEGIN
474 IF reportStr is not null then
475 IF b_html_on then
476 tempReportStr := replace(reportStr,'&', '&');
477 tempReportStr := replace(replace(tempReportStr,'<','<'),'>','>');
478 tempReportStr := replace(replace(tempReportStr,'','''),'"','"');
479
480 tempReportStr := concat('<span>',tempReportStr);
481 tempReportStr := concat(tempReportStr, '</span>');
482
483 dbms_lob.createTemporary(tempClob,true,dbms_lob.call);
484 select vsize(tempReportStr) into strSize from dual;
485 dbms_lob.write(tempClob,strSize,1,tempReportStr);
486 dbms_lob.append(reportClob,tempClob);
487 END IF;
488 END IF;
489 EXCEPTION
490 WHEN others THEN
491 -- logging here...
492 null;
493 END AddSafeStringToReport;
494 ----------------------------------------------------------------------
495 -- addStringToReport takes the report CLOB and LONG representation
496 -- of the report string and appends the string onto the end of the
497 -- report CLOB - the report CLOB is a IN/OUT object and so the CLOB
498 -- object is accessable from the calling procedure after this procedure
499 -- has terminated
500 ----------------------------------------------------------------------
501
502 PROCEDURE addStringToReport(reportStr IN LONG) IS
503 tempClob CLOB;
504 strSize INTEGER;
505 tmpReportStr LONG;
506 BEGIN
507 IF reportStr IS NOT NULL THEN
508 IF (UPPER(reportStr) = UPPER('@HTML')) AND (DBMS_LOB.GETLENGTH(reportClob) = 0) THEN
509 b_html_on := TRUE;
510 END IF;
511 dbms_lob.createTemporary(tempClob,true,dbms_lob.call);
512 tmpReportStr := reportStr;
513 select vsize(tmpReportStr) into strSize from dual;
514 dbms_lob.write(tempClob,strSize,1,tmpReportStr);
515 dbms_lob.append(reportClob,tempClob);
516 END IF;
517 EXCEPTION
518 WHEN others THEN
519 -- logging here...
520 null;
521 END;
522 -----------------------------------------------------------
523 -- getTestMethodsForPkg tajes in a partial or complete
524 -- string of the plsql package that is to have its
525 -- unit tests executed. A list or more technicaly a
526 -- table of VARVCHAR(4000) is returned to the calling function
527 -- this list contains all the methods that are contained in the package
528 -- identified by the passed in String. The procedure names are
529 -- passed back in the format <packagename>.<procedurename>()
530 -----------------------------------------------------------
531
532 FUNCTION getTestMethodsForPkg(pkgName VARCHAR2) RETURN JTF_VARCHAR2_TABLE_4000 IS
533 testNameTable JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
534 testName VARCHAR2(4000);
535 v_size NUMBER := 0;
536 cursor testNameCursor IS
537
538 select distinct a.package_name, a.object_name
539 from all_arguments a, all_objects b
540 where
541 -- a.owner like 'APPS' and b.owner like 'APPS'
542 -- and a.object_id = b.object_id
543 a.object_id = b.object_id
544 and b.object_name like upper(pkgName)||'%'
545 and b.object_name like upper('%DIAGUNITTEST%')
546 and b.object_type like 'PACKAGE'
547 and upper(b.owner) in ('APPS', 'JTF', 'APPLSYS')
548 and upper(a.owner) in ('APPS', 'JTF', 'APPLSYS')
549 and a.object_name like 'TEST%'
550 and a.sequence = 0;
551
552 -- select distinct package_name,object_name
553 -- from all_arguments
554 -- where owner like 'APPS'
555 -- where owner like USER
556 -- and upper(package_name) like upper(pkgName)
557 -- and upper(package_name) like upper(pkgName)||'%'
558 -- and upper(package_name) like upper('%DIAGUNITTEST%')
559 -- and upper(object_name) like 'TEST%'
560 -- and sequence = 0;
561 BEGIN
562 for x in testNameCursor
563 loop
564 v_size := v_size +1;
565 testNameTable.extend;
566 testNameTable(v_size) := x.package_name||'.'||x.object_name||'()';
567 end loop;
568 return testNameTable;
569 END;
570
571 -----------------------------------------------------------
572 -- getTestPackages takes in a partial or complete
573 -- string of the plsql package that is to have its
574 -- unit tests executed. A list or more technicaly a
575 -- table of VARVCHAR(4000) is returned to the calling function
576 -- this list contains all the packages that begin or are denoted
577 -- by the passed in String
578 -----------------------------------------------------------
579
580 FUNCTION getTestPackages(pkgName VARCHAR2) RETURN JTF_VARCHAR2_TABLE_4000 IS
581 testNameTable JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
582 testName VARCHAR2(4000);
583 v_size NUMBER := 0;
584 cursor testNameCursor IS
585 select distinct package_name
586 from all_arguments
587 -- where owner like 'APPS'
588 where -- owner like USER
589 -- and upper(package_name) like upper(pkgName)||'%'
590 upper(package_name) like upper(pkgName)||'%'
591 and upper(owner) in ('APPS', 'JTF', 'APPLSYS')
592 and upper(object_name) like upper('RUNTEST');
593 BEGIN
594 for x in testNameCursor
595 loop
596 v_size := v_size +1;
597 testNameTable.extend;
598 testNameTable(v_size) := x.package_name;
599 end loop;
600 return testNameTable;
601 END;
602
603 -----------------------------------------------------------
604 -- getUnitTestPackages takes in a partial or complete
605 -- string of the plsql package that is to have its
606 -- unit tests executed. A list or more technicaly a
607 -- table of VARCHAR(4000) is returned to the calling function
608 -- this list contains all the packages that begin or are denoted
609 -- by the passed in String and also contain procedures that
610 -- have names beginning with "test..." and pass no parameters.
611 -- thus recognising packages with Unit tests within.
612 -----------------------------------------------------------
613
614 FUNCTION getUnitTestPackages(pkgName VARCHAR2) RETURN JTF_VARCHAR2_TABLE_4000 IS
615 testNameTable JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
616 testName VARCHAR2(4000);
617 v_size NUMBER := 0;
618 cursor testNameCursor IS
619 select distinct package_name
620 from all_arguments
621 -- where owner like 'APPS'
622 where -- owner like USER
623 -- and upper(package_name) like upper(pkgName)||'%'
624 upper(package_name) like upper(pkgName)||'%'
625 and upper(package_name) like upper('%DIAGUNITTEST%')
626 and upper(object_name) like 'TEST%'
627 and upper(owner) in ('APPS', 'JTF', 'APPLSYS')
628 and sequence = 0;
629 BEGIN
630 for x in testNameCursor
631 loop
632 v_size := v_size +1;
633 testNameTable.extend;
634 testNameTable(v_size) := x.package_name;
635 end loop;
636 return testNameTable;
637 END;
638
639 --------------------------------------------------------------------
640 -- assert takes in two objects, - the message to be be thrown in the
641 -- custom application exception. And a boolean value to indicate if
642 -- the exception should be thrown at all.
643 --------------------------------------------------------------------
644
645 PROCEDURE assert(message VARCHAR2,condition BOOLEAN) IS
646 BEGIN
647 IF (not condition) THEN
648 fail(message);
649 END IF;
650 END;
651
652 --------------------------------------------------------------------
653 -- fail throws a PL/SQL custom application error. The message
654 -- passed through forms part of the error String that will be displayed
655 -- as part of the plsql exception. The PLSQL exception is caught and
656 -- parsed by the diagnostic framework.
657 --------------------------------------------------------------------
658
659
660 PROCEDURE fail(message VARCHAR2) IS
661 BEGIN
662 RAISE_APPLICATION_ERROR(-20000,message);
663 END;
664
665 --------------------------------------------------------------------
666 -- assertTrue takes in a message to display to the user (if assertion fails) along
667 -- with a BOOLEAN value. If the value is False the assertion error is
668 -- thrown displaying the message in the diagnostic framework else the
669 -- message is ignored.
670 --------------------------------------------------------------------
671
672 PROCEDURE assertTrue(message VARCHAR2, condition BOOLEAN) IS
673 BEGIN
674 assert(message,condition);
675 END;
676
677 --------------------------------------------------------------------
678 -- assertTrue takes in a message to display to the user (if assertion fails) along
679 -- with two VARCHAR2 objects and a operator. Openator can be (equals =)
680 -- The assertion fail call is called if the
681 -- the following statement is false arg1 = arg2
682 --------------------------------------------------------------------
683
684 PROCEDURE assertTrue(message VARCHAR2, operand VARCHAR2,arg1 VARCHAR2,arg2 VARCHAR2) IS
685 BEGIN
686 IF (operand = '=') THEN
687 IF (UPPER(arg1) = UPPER(arg2)) THEN
688 assert(message,TRUE);
689 ELSE
690 assert(message,FALSE);
691 END IF;
692 ELSE
693 assert(message,FALSE);
694 END IF;
695 EXCEPTION
696 WHEN others THEN
697 assert(message,FALSE);
698 END;
699
700 --------------------------------------------------------------------
701 -- assertTrue takes in a message to display to the user (if assertion fails) along
702 -- with two NUMBER objects and a operator. Openator can be (greater than >) ,
703 -- (less than <) or (equals =) The assertion fail call is called if the
704 -- the following statement is false arg1 (operand) arg2
705 -- ie 10 > 20 is true, 20 = 21 is false etc...
706 --------------------------------------------------------------------
707
708 PROCEDURE assertTrue(message VARCHAR2, operand VARCHAR2,arg1 NUMBER,arg2 NUMBER) IS
709 BEGIN
710 IF (operand = '=') THEN
711 IF (arg1 = arg2) THEN
712 assert(message,TRUE);
713 ELSE
714 assert(message,FALSE);
715 END IF;
716 END IF;
717 IF (operand = '>') THEN
718 IF (arg1 > arg2) THEN
719 assert(message,TRUE);
720 ELSE
721 assert(message,FALSE);
722 END IF;
723 END IF;
724 IF (operand = '<') THEN
725 IF (arg1 < arg2) THEN
726 assert(message,TRUE);
727 ELSE
728 assert(message,FALSE);
729 END IF;
730 END IF;
731 EXCEPTION
732 WHEN others THEN
733 assert(message,FALSE);
734 END;
735
736 --------------------------------------------------------------------
737 -- assertEquals takes in a message to display to the user (if assertion fails) along
738 -- with two NUMBER objects. The assertion fail call is called if the
739 -- two numbers are not equal.
740 --------------------------------------------------------------------
741
742 PROCEDURE assertEquals(message VARCHAR2,arg1 NUMBER,arg2 NUMBER) IS
743 BEGIN
744 IF (arg1 = arg2) THEN
745 assert(message,TRUE);
746 ELSE
747 assert(message,FALSE);
748 END IF;
749 END;
750
751 --------------------------------------------------------------------
752 -- assertEquals takes in a message to display to the user (if assertion fails) along
753 -- with two VARCHAR2 objects. The assertion fail call is called if the
754 -- two Strings are not equal.
755 --------------------------------------------------------------------
756
757 PROCEDURE assertEquals(message VARCHAR2,arg1 VARCHAR2,arg2 VARCHAR2) IS
758 BEGIN
759 IF (arg1 = arg2) THEN
760 assert(message,TRUE);
761 ELSE
762 assert(message,FALSE);
763 END IF;
764 END;
765
766 --------------------------------------------------------------------
767 -- assertEquals takes in a message to display to the user (if assertion fails) along
768 -- with two clob objects. The assertion fail call is called if the
769 -- two Clobs are not equal.
770 --------------------------------------------------------------------
771
772 PROCEDURE assertEquals(message VARCHAR2,arg1 CLOB,arg2 CLOB) IS
773 retval INTEGER;
774 strSizeArg1 INTEGER;
775 strSizeArg2 INTEGER;
776 BEGIN
777 select dbms_lob.getlength(arg1) into strSizeArg1 from dual;
778 select dbms_lob.getlength(arg2) into strSizeArg2 from dual;
779 retval := dbms_lob.compare(arg1,arg2,strSizeArg1,1,1);
780 IF (retval = 0) THEN
781 assert(message,TRUE);
782 ELSE
783 assert(message,FALSE);
784 END IF;
785 END;
786
787 --------------------------------------------------------------------
788 -- assertNotNull takes in a message to display to the user (if assertion fails) along
789 -- with a VARCHAR2 parameter. The assert fail call is called if in
790 -- a VARCHAR2 object throws an assertion error if it is null
791 --------------------------------------------------------------------
792
793 PROCEDURE assertNotNull(message VARCHAR2,arg1 VARCHAR2) IS
794 BEGIN
795 IF (arg1 IS NOT NULL) THEN
796 assert(message,TRUE);
797 ELSE
798 assert(message,FALSE);
799 END IF;
800 END;
801
802 --------------------------------------------------------------------
803 -- assertNull takes in a message to display to the user (if assertion fails) along
804 -- with a VARCHAR2 parameter. The assert fail call is called if in
805 -- a VARCHAR2 object throws an assertion error - if it is not null
806 --------------------------------------------------------------------
807
808 PROCEDURE assertNull(message VARCHAR2,arg1 VARCHAR2) IS
809 BEGIN
810 IF (arg1 IS NULL) THEN
811 assert(message,TRUE);
812 ELSE
813 assert(message,FALSE);
814 END IF;
815 END;
816
817
818 --------------------------------------------------------------------
819 -- failNotEquals takes in a message to display to the user (if assertion fails) along
820 -- with two VARCHAR2 parameters. The assert fail call is called if the
821 -- two VARCHAR2 (strings) are not equal.
822 --------------------------------------------------------------------
823
824 PROCEDURE failNotEquals(message VARCHAR2,arg1 VARCHAR2,arg2 VARCHAR2) IS
825 BEGIN
826 IF (arg1 = arg2) THEN
827 assert(message,TRUE);
828 ELSE
829 assert(message,FALSE);
830 END IF;
831 END;
832
833 --------------------------------------------------------------------
834 -- failNotEquals takes in a message to display to the user (if assertion fails) along
835 -- with two NUMBER parameters. The assert fail call is called if the
836 -- two NUMBERS are not equal.
837 --------------------------------------------------------------------
838
839 PROCEDURE failNotEquals(message VARCHAR2,arg1 NUMBER,arg2 NUMBER) IS
840 BEGIN
841 IF (arg1 = arg2) THEN
842 assert(message,TRUE);
843 ELSE
844 assert(message,FALSE);
845 END IF;
846 END;
847
848 --------------------------------------------------------------------
849 -- failNotEquals takes in a message to display to the user (if assertion fails) along
850 -- with two clob objects. The assert fail call is called if the
851 -- two clobs are not identical.
852 --------------------------------------------------------------------
853
854 PROCEDURE failNotEquals(message VARCHAR2,arg1 CLOB,arg2 CLOB) IS
855 retval INTEGER;
856 strSizeArg1 INTEGER;
857 strSizeArg2 INTEGER;
858 BEGIN
859 select dbms_lob.getlength(arg1) into strSizeArg1 from dual;
860 select dbms_lob.getlength(arg2) into strSizeArg2 from dual;
861 retval := dbms_lob.compare(arg1,arg2,strSizeArg1,1,1);
862 IF (retval = 0) THEN
863 assert(message,TRUE);
864 ELSE
865 assert(message,FALSE);
866 END IF;
867 END;
868
869 --------------------------------------------------------------------
870 -- failFormatted takes in a message to display to the user (if assertion fails) along
871 -- with the value that was expected (VARCHAR2) and the actual value (VARCHAR2) recieved
872 --------------------------------------------------------------------
873
874 PROCEDURE failFormatted(message VARCHAR2,expected VARCHAR2,actual VARCHAR2) IS
875 tempStr VARCHAR2(4000);
876 BEGIN
877 IF (expected is not null) or (actual is not null) THEN
878 IF (message is not null) THEN
879 tempStr := message||' Expected:['||expected||'] but was: ['||actual||']';
880 assert(tempStr,FALSE);
881 ELSE
882 tempStr := 'No Error Available - Expected:['||expected||'] but was: ['||actual||']';
883 assert(tempStr,FALSE);
884 END IF;
885 ELSE
886 IF (message is not null) THEN
887 assert(message,FALSE);
888 ELSE
889 assert('No Error Available',FALSE);
890 END IF;
891 END IF;
892 END;
893
894 --------------------------------------------------------------------
895 -- failFormatted takes in a message to display to the user (if assertion fails) along
896 -- with the value that was expected (NUMBER) and the actual value (NUMBER) recieved
897 --------------------------------------------------------------------
898
899 PROCEDURE failFormatted(message VARCHAR2,expected NUMBER,actual NUMBER) IS
900 tempStr VARCHAR2(4000);
901 BEGIN
902 IF (message is not null) THEN
903 tempStr := message||' Expected:['||expected||'] but was: ['||actual||']';
904 assert(tempStr,FALSE);
905 ELSE
906 assert('No Error Available',FALSE);
907 END IF;
908 END;
909
910
911 ----------------------------
912 --- PipeLining APIs
913 ----------------------------
914
915 ----------------------------------------------------------------------
916 -- addOutput takes a table of JTF_DIAG_OUTPUTS in the form JTF_DIAG_OUTPUTTBL
917 -- a varchar2 representing the variable to add and a varchar2 representing
918 -- the value to add and this function will add create a new JTF_DIAG_OUTPUTS
919 -- object from the variable and value and add this JTF_DIAG_OUTPUTS to
920 -- a table of JTF_DIAG_OUTPUTS (JTF_DIAG_OUTPUTTBL) and return this.
921 ----------------------------------------------------------------------
922
923 FUNCTION addOutput(outputs IN JTF_DIAG_OUTPUTTBL,
924 var IN VARCHAR2,
925 val IN VARCHAR2) RETURN JTF_DIAG_OUTPUTTBL IS
926 tempOutput JTF_DIAG_OUTPUTS;
927 tempOutputTable JTF_DIAG_OUTPUTTBL;
928 BEGIN
929 tempOutputTable := outputs;
930 tempOutput := JTF_DIAG_OUTPUTS(VISIBLE,var,val);
931 tempOutputTable.extend(1);
932 tempOutputTable(tempOutputTable.COUNT) := tempOutput;
933 return tempOutputTable;
934 EXCEPTION
935 WHEN others THEN
936 -- logging here...
937 return outputs;
938 END;
939
940
941 ---------------------------------------------------------------------
942 -- initialise a outputTable object and return it to the
943 -- caller method. A table of a single empty JTF_DIAG_OUTPUTS
944 -- is created and and then removed with the call to trim -
945 -- this initializes the collection
946 ---------------------------------------------------------------------
947
948 FUNCTION initOutputTable RETURN JTF_DIAG_OUTPUTTBL IS
949 temp JTF_DIAG_OUTPUTTBL;
950 BEGIN
951 temp := JTF_DIAG_OUTPUTTBL(JTF_DIAG_OUTPUTS(-1,'',''));
952 temp.trim;
953 return temp;
954 EXCEPTION
955 WHEN others THEN
956 return null;
957 END;
958
959
960 ----------------------------------------------------------------------
961 -- addDependency takes a table of VARCHAR2(4000) in the form
962 -- JTF_DIAG_DEPENDTBL, and a varchar2 representing the value to add
963 -- and this function will add this value to
964 -- a table of VARCHAR2(4000) (JTF_DIAG_DEPENDTBL) and return this.
965 ----------------------------------------------------------------------
966
967 FUNCTION addDependency(dependencies IN JTF_DIAG_DEPENDTBL,
968 val IN VARCHAR2) RETURN JTF_DIAG_DEPENDTBL IS
969 tempDependency VARCHAR2(4000);
970 tempDependencyTable JTF_DIAG_DEPENDTBL;
971 BEGIN
972 tempDependencyTable := dependencies;
973 tempDependency := val;
974 tempDependencyTable.extend(1);
975 tempDependencyTable(tempDependencyTable.COUNT) := tempDependency;
976 return tempDependencyTable;
977 EXCEPTION
978 WHEN others THEN
979 -- logging here...
980 return dependencies;
981 END;
982
983
984 ---------------------------------------------------------------------
985 -- initialise a dependencyTable object and return it to the
986 -- caller method. A empty table of VARCHAR2(4000) is created
987 ---------------------------------------------------------------------
988 FUNCTION initDependencyTable RETURN JTF_DIAG_DEPENDTBL IS
989 temp JTF_DIAG_DEPENDTBL;
990 BEGIN
991 temp := JTF_DIAG_DEPENDTBL();
992 -- temp.trim;
993 return temp;
994 EXCEPTION
995 WHEN others THEN
996 return null;
997 END;
998
999
1000 ----------------------------
1001 --- Deprecated APIs
1002 ----------------------------
1003
1004 PROCEDURE setUpVars(reportClob OUT NOCOPY CLOB) IS
1005 BEGIN
1006 setUpVars;
1007 END;
1008
1009 PROCEDURE addStringToReport(reportClob IN OUT NOCOPY CLOB,reportStr IN LONG) IS
1010 BEGIN
1011 addStringToReport(reportStr);
1012 END;
1013
1014 ---------------------------------------------------------------------
1015 -- initialise a inputTable object and return it to the
1016 -- caller method. A table of a single empty JTF_DIAG_TEST_INPUTS
1017 -- is created and then removed with the call to trim -
1018 -- this initialises the collection
1019 ---------------------------------------------------------------------
1020
1021 FUNCTION initialiseInput RETURN JTF_DIAG_TEST_INPUTS IS
1022 BEGIN
1023 return JTF_DIAG_HELPER_UTILS.initialise_Input_Collection;
1024 END;
1025
1026 ----------------------------------------------------------------------
1027 -- addInput takes a table of JTF_DIAG_TEST_INPUT in the form JTF_DIAG_TEST_INPUTS
1028 -- name, value has tobe passed.Others will take default values.If values provided
1029 -- JTF_DIAG_TEST_INPUT will be created with the values and JTF_DIAG_TEST_INPUTS
1030 -- table will be returned.
1031 ----------------------------------------------------------------------
1032
1033 FUNCTION addInput(inputs IN JTF_DIAG_TEST_INPUTS,
1034 name IN VARCHAR2,
1035 value IN VARCHAR2,
1036 isConfidential IN VARCHAR2 default 'FALSE',
1037 defaultValue IN VARCHAR2 default null,
1038 tip IN VARCHAR2 default null,
1039 isMandatory IN VARCHAR2 default 'FALSE',
1040 isDate IN VARCHAR2 default 'FALSE',
1041 isNumber IN VARCHAR2 default 'FALSE') RETURN JTF_DIAG_TEST_INPUTS IS
1042 BEGIN
1043 return JTF_DIAG_HELPER_UTILS.addInput(inputs,name, value, isConfidential, defaultValue, tip, isMandatory, isDate, isNumber);
1044 END;
1045
1046
1047 ------------------------------------
1048 -- new APIs for Execution Engine and
1049 -- reporting library
1050 -------------------------------------
1051 FUNCTION GET_SITE_DATE_FORMAT RETURN VARCHAR2 IS
1052 date_format varchar2(100);
1053 BEGIN
1054 APPS.FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',date_format);
1055 return date_format;
1056 END GET_SITE_DATE_FORMAT;
1057
1058 FUNCTION GET_DATE_FORMAT(user_id IN number) RETURN VARCHAR2 IS
1059 date_format varchar2(50);
1060 BEGIN
1061 date_format:=APPS.FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',user_id);
1062 return date_format;
1063 END GET_DATE_FORMAT;
1064
1065 -- mls
1066 FUNCTION GET_NUMERIC_CHARACTERS(user_id IN number) RETURN VARCHAR2 IS
1067 numeric_chars varchar2(10);
1068 BEGIN
1069 numeric_chars:=FND_PROFILE.VALUE_SPECIFIC('ICX_NUMERIC_CHARACTERS',user_id);
1070 return numeric_chars;
1071 END GET_NUMERIC_CHARACTERS;
1072
1073
1074 END jtf_diagnostic_adaptutil;