[Home] [Help]
PACKAGE BODY: APPS.JTF_DECLARATIVE_DIAGNOSTIC
Source
1 PACKAGE BODY JTF_DECLARATIVE_DIAGNOSTIC AS
2 /* $Header: jtfdecl_diag_b.pls 120.2 2005/08/13 01:05:33 minxu noship $ */
3 ------------------------------------------------------------
4 -- procedure to initialize test datastructures
5 -- executeds prior to test run (not currently being called)
6 ------------------------------------------------------------
7 PROCEDURE init IS
8 BEGIN
9 -- test writer could insert special setup code here
10 null;
11 END init;
12
13 PROCEDURE INSERT_COL_STEP_DATA(
14 P_APPID IN VARCHAR2,
15 P_GROUPNAME IN VARCHAR2,
16 P_TESTCLASSNAME IN VARCHAR2,
17 P_TESTSTEPNAME IN VARCHAR2,
18 P_COLNAMES_ARRAY IN JTF_VARCHAR2_TABLE_4000,
19 P_LOGOP_ARRAY IN JTF_VARCHAR2_TABLE_4000,
20 P_VAL1_ARRAY IN JTF_VARCHAR2_TABLE_4000,
21 P_VAL2_ARRAY IN JTF_VARCHAR2_TABLE_4000,
22 ISUPDATE IN VARCHAR2,
23 P_LUBID IN NUMBER) IS
24
25 V_INDEX NUMBER;
26
27 BEGIN
28
29 if isupdate = 'TRUE' then
30 delete from jtf_diagnostic_decl_step_cols
31 where
32 appid = P_APPID
33 and groupname = p_groupname
34 and testclassname = p_testclassname
35 and teststepname = p_teststepname;
36 end if;
37
38
39 V_INDEX := 1;
40
41 LOOP
42 IF P_COLNAMES_ARRAY.EXISTS(V_INDEX) THEN
43
44 insert into jtf_diagnostic_decl_step_cols
45 (
46 APPID,
47 GROUPNAME,
48 TESTCLASSNAME,
49 TESTSTEPNAME,
50 COLUMN_NAME,
51 LOGICAL_OPERATOR,
52 VALIDATION_VAL1,
53 VALIDATION_VAL2,
54 OBJECT_VERSION_NUMBER,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN,
59 CREATION_DATE,
60 SECURITY_GROUP_ID
61 )
62 values
63 (
64 P_APPID,
65 P_GROUPNAME,
66 P_TESTCLASSNAME,
67 P_TESTSTEPNAME,
68 P_COLNAMES_ARRAY(V_INDEX),
69 P_LOGOP_ARRAY(V_INDEX),
70 P_VAL1_ARRAY(V_INDEX),
71 P_VAL2_ARRAY(V_INDEX),
72 1,
73 P_LUBID,
74 SYSDATE,
75 P_LUBID,
76 NULL,
77 SYSDATE,
78 NULL
79 );
80 V_INDEX := V_INDEX + 1;
81
82 ELSE
83 EXIT;
84 END IF;
85
86 END LOOP;
87 END INSERT_COL_STEP_DATA;
88
89
90 ------------------------------------------------------------
91 -- procedure to cleanup any test datastructures that were setup in the init
92 -- procedure call executes after test run (not currently being called)
93 ------------------------------------------------------------
94 PROCEDURE cleanup IS
95 BEGIN
96 -- test writer could insert special cleanup code here
97 NULL;
98 END cleanup;
99
100 ------------------------------------------------------------
101 -- procedure to execute the PLSQL test
102 -- the inputs needed for the test are passed in and a report object and CLOB are -- returned.
103 ------------------------------------------------------------
104 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
105 report OUT NOCOPY JTF_DIAG_REPORT,
106 reportClob OUT NOCOPY CLOB,
107 appshortname IN VARCHAR2,
108 groupname IN VARCHAR2,
109 testclassname IN VARCHAR2,
110 p_teststepname IN VARCHAR2,
111 p_teststeptype IN VARCHAR2,
112 p_stepDescription IN VARCHAR2,
113 p_errorType IN VARCHAR2,
114 p_errorMessage IN VARCHAR2,
115 p_fixInfo IN VARCHAR2,
116 p_tableViewName IN VARCHAR2,
117 p_logicalOperator IN VARCHAR2,
118 p_validationVal1 IN VARCHAR2,
119 p_validationVal2 IN VARCHAR2,
120 p_whereClauseOrSQL IN VARCHAR2,
121 sysParamNames IN JTF_VARCHAR2_TABLE_4000,
122 sysParamValues IN JTF_VARCHAR2_TABLE_4000,
123 p_ordernumber IN NUMBER) IS
124
125 reportStr LONG;
126 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
127 errStr VARCHAR2(4000);
128 fixInfo VARCHAR2(4000);
129 isFatal VARCHAR2(50); -- TRUE or FALSE
130 v_counter INTEGER;
131 v_step_failed BOOLEAN;
132 v_overall_failed BOOLEAN;
133 v_terminate_exec BOOLEAN;
134
135 v_summaryString VARCHAR2(32767);
136
137 -- this should be a CLOB instead since this is
138 -- limiting the size of the report that we can generate
139 -- THIS IS TEMPORARY -----
140
141 v_detailsString VARCHAR2(32767);
142 v_detailsClob CLOB;
143
144 BEGIN
145
146 -- default value for terminating execution
147 v_terminate_exec := FALSE;
148
149 v_counter := p_ordernumber;
150
151 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
152 dbms_lob.createTemporary(v_detailsClob,true,dbms_lob.call);
153 v_summaryString := '';
154
155 -- addStringToClob('<table border=0>', v_detailsClob);
156
157 v_detailsString := '';
158
159 if p_teststeptype = 'COUNT' and not v_terminate_exec then
160
161 run_or_validate_count(
162 appshortname, groupname, testclassname, report,
163 p_teststepname,
164 p_stepDescription,
165 p_errorType,
166 p_errorMessage,
167 p_fixInfo,
168 p_tableViewName,
169 p_logicalOperator,
170 p_validationVal1,
171 p_validationVal2,
172 p_whereClauseOrSQL,
173 v_step_failed,
174 v_summaryString,
175 v_detailsString,
176 v_counter);
177
178 -- add that information to the CLOB
179 -- and empty out the v_detailsString
180
181 addStringToClob(v_detailsString, v_detailsClob);
182 v_detailsString := '';
183
184 elsif (p_teststeptype = 'RECORD' OR p_teststeptype = 'NO RECORD') and not v_terminate_exec then
185
186 run_or_validate_rec_norec(
187 appshortname, groupname, testclassname, report,
188 p_teststepname,
189 p_stepDescription,
190 p_errorType,
191 p_errorMessage,
192 p_fixInfo,
193 p_whereClauseOrSQL,
194 v_step_failed,
195 v_summaryString,
196 v_detailsString,
197 v_counter,
198 p_teststeptype);
199
200 -- add that information to the CLOB
201 -- and empty out the v_detailsString
202
203 addStringToClob(v_detailsString, v_detailsClob);
204 v_detailsString := '';
205
206
207 elsif p_teststeptype = 'SYSTEM PARAMETER' and not v_terminate_exec then
208
209 run_system_parameter_step(
210 appshortname,
211 groupname,
212 testclassname,
213 report,
214 p_teststepname,
215 p_stepDescription,
216 p_errorType,
217 p_errorMessage,
218 p_fixInfo, p_tableViewName,
219 p_logicalOperator,
220 p_validationVal1,
221 v_step_failed,
222 v_summaryString,
223 v_detailsString,
224 v_counter,
225 sysParamNames,
226 sysParamValues);
227
228 -- add that information to the CLOB
229 -- and empty out the v_detailsString
230
231 addStringToClob(v_detailsString, v_detailsClob);
232 v_detailsString := '';
233
234
235 elsif p_teststeptype = 'COLUMN' and not v_terminate_exec then
236
237 run_or_validate_column(
238 appshortname, groupname, testclassname, report,
239 p_teststepname,
240 p_stepDescription,
241 p_errorType,
242 p_errorMessage,
243 p_fixInfo,
244 p_tableViewName,
245 p_logicalOperator,
246 p_validationVal1,
247 p_validationVal2,
248 p_whereClauseOrSQL,
249 v_step_failed,
250 v_summaryString,
251 v_detailsClob,
252 v_counter);
253
254 -- add that information to the CLOB
255 -- and empty out the v_detailsString
256
257 -- addStringToClob(v_detailsString, v_detailsClob);
258 -- v_detailsString := '';
259
260 end if;
261
262
263 if v_step_failed = TRUE and not v_terminate_exec then
264 v_overall_failed := TRUE;
265 if p_errorType = 'NORMALERROR' OR p_errorType = 'FATALERROR' then
266
267 statusStr := 'FAILURE'; -- SUCCESS or FAILURE
268 errStr := 'One of the test steps failed';
269 fixInfo := 'Please check the failure details in the report';
270
271 if p_errorType = 'FATALERROR' then
272 isFatal := 'TRUE';
273 else
274 isFatal := 'FALSE';
275 end if;
276 end if;
277
278 if p_errorType = 'FATALERROR' then
279 -- v_counter := v_counter + 1;
280 -- exit;
281 v_terminate_exec := TRUE;
282
283 end if;
284 end if;
285
286 v_counter := v_counter + 1;
287
288 -- v_detailsString := v_detailsString || '</table>';
289
290 -- add that information to the CLOB
291 -- and empty out the v_detailsString
292
293 addStringToClob(v_detailsString, v_detailsClob);
294 v_detailsString := '';
295
296 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
297 dbms_lob.append(reportClob, v_detailsClob);
298
299 -- overall status of the testcase
300 -- captured by the following boolean
301
302 if v_overall_failed = FALSE then
303 statusStr := 'SUCCESS'; -- SUCCESS or FAILURE
304 errStr := '';
305 fixInfo := '';
306 isFatal := '';
307 end if;
308
309 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
310
311 EXCEPTION
312 when others then
313 -- this should never happen
314 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('Exception Occurred In RUNTEST');
315 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
316 raise;
317
318 END runTest;
319
320
321 PROCEDURE run_or_validate_column(
322 appshtname in varchar2,
323 grpname in varchar2,
324 testclsname in varchar2,
325 report out NOCOPY JTF_DIAG_REPORT,
326 teststpname IN VARCHAR2,
327 step_description IN VARCHAR2,
328 error_type IN VARCHAR2,
329 error_message IN VARCHAR2,
330 fix_info IN VARCHAR2,
331 table_view_name IN VARCHAR2,
332 logical_operator IN VARCHAR2,
333 validation_val1 IN VARCHAR2,
334 VALIDATION_VAL2 IN VARCHAR2,
335 WHERE_CLAUSE_OR_SQL IN VARCHAR2,
336 STEP_FAILED IN OUT NOCOPY BOOLEAN,
337 SUMMARY_STRING IN OUT NOCOPY VARCHAR2,
338 DETAILS_CLOB IN OUT NOCOPY CLOB,
339 ORDERNUMBER IN OUT NOCOPY NUMBER) IS
340
341 v_sqlstr VARCHAR2(32767);
342 v_header VARCHAR2(10000);
343 v_count number;
344 v_count2 number;
345 v_count3 number;
346 v_cursorID integer;
347 v_dummy INTEGER;
348
349 v_selectValue JTF_VARCHAR2_TABLE_4000;
350 v_columnNames JTF_VARCHAR2_TABLE_4000;
351 v_logicalOp JTF_VARCHAR2_TABLE_4000;
352 v_validVal1 JTF_VARCHAR2_TABLE_4000;
353 v_validVal2 JTF_VARCHAR2_TABLE_4000;
354
355 cursor datalist is
356 select COLUMN_NAME, LOGICAL_OPERATOR,
357 VALIDATION_VAL1, VALIDATION_VAL2
358 from jtf_diagnostic_decl_step_cols
359 where APPID = appshtname
360 and GROUPNAME = grpname
361 and TESTCLASSNAME = testclsname
362 and TESTSTEPNAME = teststpname;
363
364 BEGIN
365 v_sqlstr := '';
366 v_count := 0;
367
368 -- set step failed to false to begin with. let the comparison
369 -- process decide if its a failure or success
370
371 step_failed := FALSE;
372
373 addStringToClob('<tr><td colspan=5 class=reportDataCell>', DETAILS_CLOB);
374
375 -- DETAILS_STRING:= DETAILS_STRING || '<tr><td class=reportDataCell>';
376 -- instantiate the data structures for
377 -- loading data, etc
378
379 v_columnNames := JTF_VARCHAR2_TABLE_4000();
380 v_logicalOp := JTF_VARCHAR2_TABLE_4000();
381 v_validVal1 := JTF_VARCHAR2_TABLE_4000();
382 v_validVal2 := JTF_VARCHAR2_TABLE_4000();
383
384 FOR x in datalist
385 LOOP
386 v_count := v_count + 1;
387 v_columnNames.extend;
388 v_logicalOp.extend;
389 v_validVal1.extend;
390 v_validVal2.extend;
391
392 v_columnNames(v_count) := x.column_name;
393 v_logicalOp(v_count) := x.logical_operator;
394 v_validVal1(v_count) := x.validation_val1;
395 v_validVal2(v_count) := x.validation_val2;
396
397 END LOOP;
398
399 v_sqlstr := 'select ';
400
401 -- now construct the SQL statement
402 -- to read up the values for comparison
403 v_count2 := 1;
404 loop
405 if v_count2 > v_count then
406 exit;
407 end if;
408
409 -- if v_count2 is less than the number of columns read up
410 -- then add a comma for the SQL string to be formed
411
412 if v_count2 < v_count then
413 v_sqlstr := v_sqlstr || 'to_char(' || v_columnNames(v_count2) || ')' || ', ';
414 else
415 v_sqlstr := v_sqlstr || 'to_char(' || v_columnNames(v_count2) || ')';
416 end if;
417
418 v_count2 := v_count2 + 1;
419 end loop;
420
421 -- add the table name to the
422 -- SQL string being constructed
423 v_sqlstr := v_sqlstr || ' from ' || table_view_name;
424
425 -- add the where clause to the
426 -- SQL string being constructed
427
428 if WHERE_CLAUSE_OR_SQL is null or length(WHERE_CLAUSE_OR_SQL) = 0 then
429 null;
430 else
431 v_sqlstr := v_sqlstr || ' where ' || WHERE_CLAUSE_OR_SQL;
432 end if;
433
434 -- at this point the SQL statement is complete
435 -- for execution. Now construct the HTML to be displayed
436 -- on the UI
437
438 v_header := '<p><hr><b><a name="' || (ORDERNUMBER + 1)
439 ||'">STEP NAME: </a></b>' || '<br><hr>' || (ORDERNUMBER + 1)|| ': ' || teststpname;
440
441 addStringToClob(v_header || '<p><b>STEP DESCRIPTION: </b>' || '<br>' || step_description
442 || '<p>'
443 || '<b>SQL QUERY CONSTRUCTED: </b>' || '<br>' || v_sqlstr
444 || '<p>', DETAILS_CLOB);
445
446 -- addStringToClob('<b>ERROR TYPE: </b>' || '<br>' || error_type, DETAILS_CLOB);
447
448 v_cursorID := DBMS_SQL.OPEN_CURSOR;
449 DBMS_SQL.PARSE(v_cursorID, v_sqlstr, DBMS_SQL.V7);
450
451 -- now bind the values for the column
452 -- values to be retrieved
453
454 -- again loop and bind the column values
455 -- to the datastructure for retrieval
456
457 v_selectValue := JTF_VARCHAR2_TABLE_4000();
458 for v_count2 in 1 .. v_count loop
459 v_selectValue.extend;
460 DBMS_SQL.DEFINE_COLUMN(v_cursorID, v_count2, v_selectValue(v_count2), 3000);
461 end loop;
462
463 -- execute the dynamically created SQL statement
464 v_dummy := dbms_sql.execute(v_cursorID);
465
466 -- at this time display the desired values
467 -- for the columns that should be matches
468 -- with the actual values
469
470 -- start the table
471
472 addStringToClob('<p><b>VALIDATION RULES: </b><br><table>', DETAILS_CLOB);
473
474 for v_count2 in 1 .. v_count loop
475 addStringToClob('<tr><td CLASS=reportDataCell>' || v_columnNames(v_count2) || ' SHOULD BE ', DETAILS_CLOB);
476 addStringToClob(v_logicalOp(v_count2) || ' ' || v_validVal1(v_count2), DETAILS_CLOB);
477
478 if v_logicalOp(v_count2) = 'BETWEEN' then
482
479 addStringToClob(' AND ' || v_validVal2(v_count2), DETAILS_CLOB);
480 end if;
481 addStringToClob('</td></tr>', DETAILS_CLOB);
483 end loop;
484
485 -- end the table
486 addStringToClob('</table>', DETAILS_CLOB);
487 addStringToClob('<p>', DETAILS_CLOB);
488
489
490 -- start the table again to construct the result set
491 addStringToClob('<p><b>APPLYING VALIDATION RULES TO RESULT SET: </b><table border=1><tr>', DETAILS_CLOB);
492
493 for v_count2 in 1 .. v_count loop
494 addStringToClob('<td CLASS=reportDataCell><b>' || upper(v_columnNames(v_count2)), DETAILS_CLOB);
495 addStringToClob('</b></td>', DETAILS_CLOB);
496
497 end loop;
498 -- dont end the table yet but end the row
499 addStringToClob('</tr>', DETAILS_CLOB);
500
501 v_count3 := 0;
502 loop
503 -- fetch the rows into the buffer
504 if dbms_sql.fetch_rows(v_cursorID) = 0 then
505 exit;
506 end if;
507
508 v_count3 := v_count3 + 1;
509
510 -- fetch the values in the datastructure for rendering
511 -- and comparison...
512 for v_count2 in 1 .. v_count loop
513 dbms_sql.column_value(v_cursorID, v_count2, v_selectValue(v_count2));
514 end loop;
515
516
517 -- for each row received compare the values
518 -- with the desired values and construct the
519 -- report at the same time
520
521 v_count2 := 1;
522
523 addStringToClob('<tr>', DETAILS_CLOB);
524
525 for v_count2 in 1 .. v_count loop
526
527 addStringToClob('<td CLASS=reportDataCell>', DETAILS_CLOB);
528
529 if v_logicalOp(v_count2) = '<>' then
530
531 if v_selectValue(v_count2) <> v_validVal1(v_count2) then
532 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
533 else
534 addStringToClob('<font color=red><b>'
535 || v_selectValue(v_count2) || ' (**FAILED**) '
536 || '</b></font>', DETAILS_CLOB);
537
538 step_failed := TRUE;
539 end if;
540
541 elsif v_logicalOp(v_count2) = '=' then
542
543 if v_selectValue(v_count2) = v_validVal1(v_count2) then
544 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
545 else
546 addStringToClob('<font color=red><b>'
547 || v_selectValue(v_count2) || ' (**FAILED**) '
548 || '</b></font>', DETAILS_CLOB);
549
550 step_failed := TRUE;
551 end if;
552
553 elsif v_logicalOp(v_count2) = '<' then
554
555 if to_number(v_selectValue(v_count2)) < to_number(v_validVal1(v_count2)) then
556 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
557 else
558 addStringToClob('<font color=red><b>'
559 || v_selectValue(v_count2) || ' (**FAILED**) '
560 || '</b></font>', DETAILS_CLOB);
561 step_failed := TRUE;
562 end if;
563
564 elsif v_logicalOp(v_count2) = '>' then
565
566 if to_number(v_selectValue(v_count2)) > to_number(v_validVal1(v_count2)) then
567 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
568 else
569 addStringToClob('<font color=red><b>'
570 || v_selectValue(v_count2) || ' (**FAILED**) '
571 || '</b></font>', DETAILS_CLOB);
572 step_failed := TRUE;
573 end if;
574
575 elsif v_logicalOp(v_count2) = '>=' then
576
577 if to_number(v_selectValue(v_count2)) >= to_number(v_validVal1(v_count2)) then
578 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
579 else
580 addStringToClob('<font color=red><b>'
581 || v_selectValue(v_count2) || ' (**FAILED**) '
582 || '</b></font>', DETAILS_CLOB);
583 step_failed := TRUE;
584 end if;
585
586 elsif v_logicalOp(v_count2) = '<=' then
587
588 if to_number(v_selectValue(v_count2)) <= to_number(v_validVal1(v_count2)) then
589 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
590 else
591 addStringToClob('<font color=red><b>'
592 || v_selectValue(v_count2) || ' (**FAILED**) '
593 || '</b></font>', DETAILS_CLOB);
594 step_failed := TRUE;
595 end if;
596
597 elsif v_logicalOp(v_count2) = 'BETWEEN' then
598
599 if to_number(v_validVal1(v_count2)) <= to_number(v_selectValue(v_count2))
600 AND to_number(v_selectValue(v_count2)) <= to_number(v_validVal2(v_count2)) then
601
602 addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
603 else
604 addStringToClob('<font color=red><b>'
605 || v_selectValue(v_count2) || ' (**FAILED**) '
606 || '</b></font>', DETAILS_CLOB);
607 step_failed := TRUE;
608 end if;
609
610 end if;
611
612 addStringToClob('</td>', DETAILS_CLOB);
613 end loop;
614
615 addStringToClob('</tr>', DETAILS_CLOB);
616
617 end loop;
618
619 -- now close the table
620 addStringToClob('</table>', DETAILS_CLOB);
621
622 SUMMARY_STRING := SUMMARY_STRING || '<tr>';
623 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || (ORDERNUMBER + 1) || '</td>';
627 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>';
624 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || teststpname || '</td>';
625 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || 'COLUMN' || '</td>';
626 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || error_type || '</td>';
628
629 if step_failed then
630 SUMMARY_STRING := SUMMARY_STRING
631 || '<a href="#'
632 || (ORDERNUMBER + 1)
633 ||'">FAILED</a>';
634
635 addStringToClob('<p><font color=red><b>STATUS: </b>Failed</font><p>',
636 DETAILS_CLOB);
637
638 else
639 SUMMARY_STRING := SUMMARY_STRING
640 || '<a href="#'
641 || (ORDERNUMBER + 1)
642 ||'">PASSED</a>';
643
644 addStringToClob('<p><font color=green><b>STATUS: </b>Succeeded</font><p>',
645 DETAILS_CLOB);
646
647 end if;
648
649 addStringToClob('</td></tr>', DETAILS_CLOB);
650
651 SUMMARY_STRING := SUMMARY_STRING || '</td>';
652 SUMMARY_STRING := SUMMARY_STRING || '</tr>';
653 dbms_sql.close_cursor(v_cursorID);
654
655 EXCEPTION
656 when others then
657 -- close the cursor
658 dbms_sql.close_cursor(v_cursorID);
659
660 SUMMARY_STRING := SUMMARY_STRING || '<tr>';
661 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || (ORDERNUMBER + 1) || '</td>';
662 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || teststpname || '</td>';
663 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || 'COLUMN' || '</td>';
664 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || error_type || '</td>';
665 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>';
666
667 SUMMARY_STRING := SUMMARY_STRING
668 || '<a href="#'
669 || (ORDERNUMBER + 1)
670 ||'">EXCEPTION OCCURRED</a>';
671
672 addStringToClob('<p><b>EXCEPTION: </b>' || '<br>' || SQLERRM, DETAILS_CLOB);
673
674 addStringToClob('<p><font color=red><b>STATUS: </b>Failed</font><p>',
675 DETAILS_CLOB);
676
677 addStringToClob('</td></tr>', DETAILS_CLOB);
678
679 step_failed := TRUE;
680
681 END run_or_validate_column;
682
683
684 PROCEDURE run_system_parameter_step(
685 appshortname in varchar2,
686 groupname in varchar2,
687 testclassname in varchar2,
688 report OUT NOCOPY JTF_DIAG_REPORT,
689 teststpname in varchar2,
690 step_description in varchar2,
691 error_type in varchar2,
692 error_message in varchar2,
693 fix_info in varchar2,
694 table_view_name in varchar2,
695 logical_operator in varchar2,
696 validation_val1 in varchar2,
697 step_failed IN OUT NOCOPY BOOLEAN,
698 summary_String IN OUT NOCOPY VARCHAR2,
699 DETAILS_STRING IN OUT NOCOPY VARCHAR2,
700 ORDERNUMBER IN OUT NOCOPY NUMBER,
701 sysParamNames IN JTF_VARCHAR2_TABLE_4000,
702 sysParamValues IN JTF_VARCHAR2_TABLE_4000) IS
703
704 v_detailsstr VARCHAR2(4000);
705 v_header VARCHAR2(300);
706 v_count integer;
707 v_recFound BOOLEAN;
708
709 BEGIN
710 v_detailsstr := '';
711
712 -- reportDataCell
713
714 DETAILS_STRING := DETAILS_STRING || '<tr><td colspan=5 class=reportDataCell>';
715
716 v_header := '<p><hr><b><a name="' || (ORDERNUMBER + 1)
717 ||'">STEP NAME: </a></b>' || '<br><hr>' || (ORDERNUMBER + 1)|| ': ' || teststpname;
718
719 v_detailsstr := v_header || '<p><b>STEP DESCRIPTION: </b>' || '<br>' || step_description
720 || '<p>' ;
721 -- v_detailsstr := v_detailsstr || '<b>SQL QUERY EXECUTED: </b>' || '<br>' || v_detailsstr;
722
723 v_detailsstr := v_detailsstr || '<p>';
724 -- v_detailsstr := v_detailsstr || '<b>ERROR TYPE: </b>' || '<br>' || error_type;
725
726 v_count := 1;
727
728 v_detailsstr := v_detailsstr || '<b>SYSTEM PARAM ANALYSIS: </b>' || '<br>';
729
730 LOOP
731
732 IF sysParamNames.EXISTS(v_count) and sysParamValues.EXISTS(v_count) THEN
733
734 if sysParamNames(v_count) = table_view_name then
735
736 if sysParamValues(v_count) = validation_val1 then
737 v_detailsstr := v_detailsstr || 'The value for system parameter: '
738 || sysParamNames(v_count) || ' is '
739 || sysParamValues(v_count) || ', which is the desired value.';
740 v_detailsstr := v_detailsstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
741 step_failed := FALSE;
742 v_recFound := TRUE;
743 EXIT;
744 else
745 v_detailsstr := v_detailsstr || 'Value for system parameter: '
746 || sysParamNames(v_count) || ' is '
747 || sysParamValues(v_count) || '. <br>The expected value was: '
748 || validation_val1;
749
750 v_detailsstr := v_detailsstr || '<p><b>ERROR MESSAGE: </b>'
751 || '<br>' || error_message;
752 v_detailsstr := v_detailsstr || '<p>' || '<b>FIX INFO: </b>';
753 v_detailsstr := v_detailsstr || '<br>' || fix_info;
754 v_detailsstr := v_detailsstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
755 step_failed := TRUE;
756 v_recFound := TRUE;
757 EXIT;
758 end if;
762 else
759
760 end if;
761
763 v_recFound := FALSE;
764 EXIT;
765 end if;
766
767 v_count := v_count + 1;
768
769 END LOOP;
770
771 -- if record was not found then provide the
772 -- appropriate error message that the system param was not found
773
774 if not v_recFound then
775 v_detailsstr := v_detailsstr || '<br>' || 'The system parameter: '
776 || table_view_name || ' was not found';
777
778 v_detailsstr := v_detailsstr || '<p><b>ERROR MESSAGE: </b>'
779 || '<br>' || error_message;
780 v_detailsstr := v_detailsstr || '<p>' || '<b>FIX INFO: </b>';
781 v_detailsstr := v_detailsstr || '<br>' || fix_info;
782 v_detailsstr := v_detailsstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
783 step_failed := TRUE;
784 end if;
785
786
787 SUMMARY_STRING := SUMMARY_STRING || '<tr>';
788 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || (ORDERNUMBER + 1) || '</td>';
789 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || teststpname || '</td>';
790 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || 'SYSTEM PARAMETER' || '</td>';
791 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || error_type || '</td>';
792 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>';
793
794 if step_failed then
795 SUMMARY_STRING := SUMMARY_STRING
796 || '<a href="#'
797 || (ORDERNUMBER + 1)
798 ||'">FAILED</a>';
799 else
800 SUMMARY_STRING := SUMMARY_STRING
801 || '<a href="#'
802 || (ORDERNUMBER + 1)
803 ||'">PASSED</a>';
804 end if;
805
806 SUMMARY_STRING := SUMMARY_STRING || '</td>';
807 SUMMARY_STRING := SUMMARY_STRING || '</tr>';
808
809 DETAILS_STRING := DETAILS_STRING || v_detailsstr;
810 DETAILS_STRING := DETAILS_STRING || '</td></tr>';
811
812 END run_system_parameter_step;
813
814
815 ------------------------------------------------------------
816 -- procedure to run_or_validate_count
817 ------------------------------------------------------------
818 PROCEDURE run_or_validate_count(
819 appshtname in varchar2,
820 grpname in varchar2,
821 testclsname in varchar2, report OUT NOCOPY JTF_DIAG_REPORT,
822 teststpname IN VARCHAR2,
823 step_description IN VARCHAR2,
824 error_type IN VARCHAR2,
825 error_message IN VARCHAR2,
826 fix_info IN VARCHAR2,
827 table_view_name IN VARCHAR2,
828 logical_operator IN VARCHAR2,
829 validation_val1 IN VARCHAR2,
830 VALIDATION_VAL2 IN VARCHAR2,
831 WHERE_CLAUSE_OR_SQL IN VARCHAR2,
832 STEP_FAILED IN OUT NOCOPY BOOLEAN,
833 SUMMARY_STRING IN OUT NOCOPY VARCHAR2,
834 DETAILS_STRING IN OUT NOCOPY VARCHAR2,
835 ORDERNUMBER IN OUT NOCOPY NUMBER) IS
836
837 v_sqlstr VARCHAR2(32767);
838 v_header VARCHAR2(300);
839 v_count integer;
840 v_cursorID integer;
841
842 BEGIN
843 v_sqlstr := '';
844
845 -- reportDataCell
846
847 DETAILS_STRING:= DETAILS_STRING || '<tr><td colspan=5 class=reportDataCell>';
848
849 if table_view_name is null or length(table_view_name) = 0 then
850 v_sqlstr := WHERE_CLAUSE_OR_SQL;
851 else
852 v_sqlstr := 'select count(*) from ' || table_view_name;
853 if WHERE_CLAUSE_OR_SQL is null or length(WHERE_CLAUSE_OR_SQL) = 0 then
854 null;
855 else
856 v_sqlstr := v_sqlstr || ' where ' || WHERE_CLAUSE_OR_SQL;
857 end if;
858 end if;
859
860 v_cursorID := DBMS_SQL.OPEN_CURSOR;
861 DBMS_SQL.PARSE(v_cursorID, v_sqlstr, DBMS_SQL.V7);
862 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, v_count);
863 v_count := dbms_sql.execute(v_cursorID);
864
865 loop
866 -- fetch the rows into the buffer
867 if dbms_sql.fetch_rows(v_cursorID) = 0 then
868 exit;
869 end if;
870 dbms_sql.column_value(v_cursorID, 1, v_count);
871 end loop;
872
873
874 v_header := '<p><hr><b><a name="' || (ORDERNUMBER + 1)
875 ||'">STEP NAME: </a></b>' || '<br><hr>' || (ORDERNUMBER + 1)|| ': ' || teststpname;
876
877 v_sqlstr := v_header || '<p><b>STEP DESCRIPTION: </b>' || '<br>' || step_description
878 || '<p>'
879 || '<b>SQL QUERY EXECUTED: </b>' || '<br>' || v_sqlstr
880 || '<p>'
881 || '<b>RETURNED VALUE: </b>' || '<br>' || v_count;
882 -- || '<b>COUNT VALUE: </b>' || '<br>' || v_count;
883
884 v_sqlstr := v_sqlstr || '<p>';
885 -- v_sqlstr := v_sqlstr || '<b>ERROR TYPE: </b>' || '<br>' || error_type;
886 -- v_sqlstr := v_sqlstr || '<p><b>VALIDATION VALUE: </b>' || '<br>' || validation_val1;
887 v_sqlstr := v_sqlstr || '<p><b>EXPECTED RESULT: </b>' || '<br>';
888
889 -- || validation_val1
890 -- v_sqlstr := v_sqlstr || '<p><b>LOGICAL OPERATOR: </b>' || '<br>' || logical_operator || ' AND ';
891
892 if logical_operator = '<>' then
893
894 v_sqlstr := v_sqlstr || 'Returned value must not equal ' || validation_val1;
895
896 if to_char(v_count) <> validation_val1 then
900 else
897 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' <> ' || validation_val1;
898 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
899 step_failed := FALSE;
901 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS = ' || validation_val1;
902 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
903 || '<br>' || error_message;
904 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
905 v_sqlstr := v_sqlstr || '<br>' || fix_info;
906 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
907 step_failed := TRUE;
908 end if;
909
910 elsif logical_operator = '=' then
911
912 v_sqlstr := v_sqlstr || 'Returned value must equal ' || validation_val1;
913
914 if to_char(v_count) = validation_val1 then
915 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' = ' || validation_val1;
916 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
917 step_failed := FALSE;
918 else
919 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT = ' || validation_val1;
920 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
921 || '<br>' || error_message;
922 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
923 v_sqlstr := v_sqlstr || '<br>' || fix_info;
924 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
925 step_failed := TRUE;
926 end if;
927
928 elsif logical_operator = '<' then
929
930 v_sqlstr := v_sqlstr || 'Returned value must be less than ' || validation_val1;
931
932 if v_count < to_number(validation_val1) then
933 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' < ' || validation_val1;
934 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
935 step_failed := FALSE;
936 else
937 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT < ' || validation_val1;
938 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
939 || '<br>' || error_message;
940 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
941 v_sqlstr := v_sqlstr || '<br>' || fix_info;
942 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
943 step_failed := TRUE;
944 end if;
945
946 elsif logical_operator = '>' then
947
948 v_sqlstr := v_sqlstr || 'Returned value must be greater than ' || validation_val1;
949
950 if v_count > to_number(validation_val1) then
951 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' > ' || validation_val1;
952 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
953 step_failed := FALSE;
954 else
955 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT > ' || validation_val1;
956 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
957 || '<br>' || error_message;
958 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
959 v_sqlstr := v_sqlstr || '<br>' || fix_info;
960 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
961 step_failed := TRUE;
962 end if;
963
964 elsif logical_operator = '>=' then
965
966 v_sqlstr := v_sqlstr || 'Returned value must be greater than or equal to ' || validation_val1;
967
968 if v_count >= to_number(validation_val1) then
969 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' >= ' || validation_val1;
970 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
971 step_failed := FALSE;
972 else
973 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT >= ' || validation_val1;
974 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
975 || '<br>' || error_message;
976 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
977 v_sqlstr := v_sqlstr || '<br>' || fix_info;
978 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
979 step_failed := TRUE;
980 end if;
981 elsif logical_operator = '<=' then
982
983 v_sqlstr := v_sqlstr || 'Returned value must be less than or equal to ' || validation_val1;
984
985 if v_count <= to_number(validation_val1) then
986 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' <= ' || validation_val1;
987 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
988 step_failed := FALSE;
989 else
990 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT <= ' || validation_val1;
991 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
992 || '<br>' || error_message;
993 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
994 v_sqlstr := v_sqlstr || '<br>' || fix_info;
995 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
996 step_failed := TRUE;
997 end if;
998 elsif logical_operator = 'BETWEEN' then
999
1000 v_sqlstr := v_sqlstr || 'Returned value must be between '
1001 || validation_val1
1002 || ' and '
1003 || validation_val2
1004 || ', both values inclusive' ;
1005
1006 if to_number(validation_val1) <= v_count and v_count <= to_number(validation_val2) then
1010 step_failed := FALSE;
1007 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS BETWEEN ' || validation_val1 || ' AND ';
1008 -- v_sqlstr := v_sqlstr || validation_val2;
1009 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
1011 else
1012 -- v_sqlstr := v_sqlstr || to_char(v_count) || ' IS NOT BETWEEN '
1013 -- || validation_val1 || ' AND ' || validation_val2;
1014 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
1015 || '<br>' || error_message;
1016 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
1017 v_sqlstr := v_sqlstr || '<br>' || fix_info;
1018 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
1019 step_failed := TRUE;
1020 end if;
1021
1022 end if;
1023
1024 SUMMARY_STRING := SUMMARY_STRING || '<tr>';
1025 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || (ORDERNUMBER + 1) || '</td>';
1026 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || teststpname || '</td>';
1027 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || 'COUNT' || '</td>';
1028 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || error_type || '</td>';
1029 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>';
1030
1031 if step_failed then
1032 SUMMARY_STRING := SUMMARY_STRING
1033 || '<a href="#'
1034 || (ORDERNUMBER + 1)
1035 ||'">FAILED</a>';
1036 else
1037 SUMMARY_STRING := SUMMARY_STRING
1038 || '<a href="#'
1039 || (ORDERNUMBER + 1)
1040 ||'">PASSED</a>';
1041 end if;
1042
1043 SUMMARY_STRING := SUMMARY_STRING || '</td>';
1044 SUMMARY_STRING := SUMMARY_STRING || '</tr>';
1045
1046 DETAILS_STRING := DETAILS_STRING || v_sqlstr;
1047 DETAILS_STRING := DETAILS_STRING || '</td></tr>';
1048
1049 dbms_sql.close_cursor(v_cursorID);
1050
1051 END run_or_validate_count;
1052
1053
1054 ------------------------------------------------------------
1055 -- procedure to run_or_validate_record or no record
1056 ------------------------------------------------------------
1057 PROCEDURE run_or_validate_rec_norec(
1058 appshtname in varchar2,
1059 grpname in varchar2,
1060 testclsname in varchar2, report OUT NOCOPY JTF_DIAG_REPORT,
1061 teststpname IN VARCHAR2,
1062 step_description IN VARCHAR2,
1063 error_type IN VARCHAR2,
1064 error_message IN VARCHAR2,
1065 fix_info IN VARCHAR2,
1066 WHERE_CLAUSE_OR_SQL IN VARCHAR2,
1067 STEP_FAILED IN OUT NOCOPY BOOLEAN,
1068 SUMMARY_STRING IN OUT NOCOPY VARCHAR2,
1069 DETAILS_STRING IN OUT NOCOPY VARCHAR2,
1070 ORDERNUMBER IN OUT NOCOPY NUMBER,
1071 STEPTYPE in VARCHAR2) IS
1072
1073 v_sqlstr VARCHAR2(32767);
1074 v_header VARCHAR2(300);
1075 v_count integer;
1076 v_cursorID integer;
1077
1078 BEGIN
1079 v_sqlstr := '';
1080
1081 -- reportDataCell
1082
1083 DETAILS_STRING:= DETAILS_STRING || '<tr><td colspan=5 class=reportDataCell>';
1084
1085 v_sqlstr := WHERE_CLAUSE_OR_SQL;
1086 v_cursorID := DBMS_SQL.OPEN_CURSOR;
1087
1088 DBMS_SQL.PARSE(v_cursorID, v_sqlstr, DBMS_SQL.V7);
1089 v_count := DBMS_SQL.EXECUTE_AND_FETCH(v_cursorID);
1090
1091 v_header := '<p><hr><b><a name="' || (ORDERNUMBER + 1)
1092 ||'">STEP NAME: </a></b>' || '<br><hr>' || (ORDERNUMBER + 1)|| ': ' || teststpname;
1093
1094 v_sqlstr := v_header || '<p><b>STEP DESCRIPTION: </b>' || '<br>' || step_description
1095 || '<p>'
1096 || '<b>SQL QUERY EXECUTED: </b>' || '<br>' || v_sqlstr;
1097
1098 v_sqlstr := v_sqlstr || '<p>';
1099 -- v_sqlstr := v_sqlstr || '<b>ERROR TYPE: </b>' || '<br>' || error_type;
1100
1101 -- v_sqlstr := v_sqlstr || ' <br>value of v_count: ' || v_count || STEPTYPE;
1102
1103 if STEPTYPE = 'RECORD' then
1104
1105 v_sqlstr := v_sqlstr || '<b>EXECUTION STATUS: </b>'
1106 || '<br>' || 'Number of rows that the query generated: ' || to_char(v_count) || '. <br>The intent of the query was to generate greater than 0 rows.';
1107
1108 if v_count >= 1 then
1109 -- v_sqlstr := v_sqlstr || '<p>' || to_char(v_count) || ' >= 1. SQL returned records';
1110 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
1111 step_failed := FALSE;
1112 else
1113 -- v_sqlstr := v_sqlstr || '<p>' || to_char(v_count) || ' IS NOT >= 1. SQL returned no records';
1114 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
1115 || '<br>' || error_message;
1116 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
1117 v_sqlstr := v_sqlstr || '<br>' || fix_info;
1118 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
1119 step_failed := TRUE;
1120 end if;
1121
1122 elsif STEPTYPE = 'NO RECORD' then
1123
1124 v_sqlstr := v_sqlstr || '<b>EXECUTION STATUS: </b>'
1125 || '<br>' || 'Number of rows that the query generated: ' || to_char(v_count) || '. <br>The intent of the query was to generate no rows.';
1126
1127 if v_count < 1 then
1128 -- v_sqlstr := v_sqlstr || '<p>' || to_char(v_count) || ' < 1. SQL returned no records';
1129 v_sqlstr := v_sqlstr || '<p><font color=green><b>STATUS: </b>Succeeded</font><p>';
1130 step_failed := FALSE;
1131 else
1135 v_sqlstr := v_sqlstr || '<p>' || '<b>FIX INFO: </b>';
1132 -- v_sqlstr := v_sqlstr || '<p>' || to_char(v_count) || ' IS NOT < 1. SQL returned records';
1133 v_sqlstr := v_sqlstr || '<p><b>ERROR MESSAGE: </b>'
1134 || '<br>' || error_message;
1136 v_sqlstr := v_sqlstr || '<br>' || fix_info;
1137 v_sqlstr := v_sqlstr || '<p><font color=red><b>STATUS: </b>Failed</font><p>';
1138 step_failed := TRUE;
1139 end if;
1140 end if;
1141
1142 SUMMARY_STRING := SUMMARY_STRING || '<tr>';
1143 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || (ORDERNUMBER + 1) || '</td>';
1144 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || teststpname || '</td>';
1145 SUMMARY_STRING := SUMMARY_STRING || '<td CLASS=tableDataCell>' || STEPTYPE || '</td>';
1146 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>' || error_type || '</td>';
1147 SUMMARY_STRING := SUMMARY_STRING || '<td nowrap CLASS=tableDataCell>';
1148
1149 if step_failed then
1150 SUMMARY_STRING := SUMMARY_STRING
1151 || '<a href="#'
1152 || (ORDERNUMBER + 1)
1153 ||'">FAILED</a>';
1154 else
1155 SUMMARY_STRING := SUMMARY_STRING
1156 || '<a href="#'
1157 || (ORDERNUMBER + 1)
1158 ||'">PASSED</a>';
1159 end if;
1160
1161 SUMMARY_STRING := SUMMARY_STRING || '</td>';
1162 SUMMARY_STRING := SUMMARY_STRING || '</tr>';
1163
1164 DETAILS_STRING := DETAILS_STRING || v_sqlstr;
1165 DETAILS_STRING := DETAILS_STRING || '</td></tr>';
1166
1167 dbms_sql.close_cursor(v_cursorID);
1168
1169 END run_or_validate_rec_norec;
1170
1171
1172
1173 PROCEDURE UPDATE_STEP_SEQ(
1174 P_APPID IN VARCHAR2,
1175 P_GROUPNAME IN VARCHAR2,
1176 P_TESTCLASSNAME IN VARCHAR2,
1177 P_STEPSEQARRAY IN JTF_VARCHAR2_TABLE_4000,
1178 P_LUBID IN NUMBER) IS
1179
1180 v_numofrows NUMBER;
1181 v_index BINARY_INTEGER := 1;
1182
1183 BEGIN
1184 SELECT COUNT(*)
1185 INTO v_numofrows
1186 FROM jtf_diagnostic_decl_test_steps
1187 WHERE APPID = P_APPID
1188 and GROUPNAME = P_GROUPNAME
1189 and TESTCLASSNAME = P_TESTCLASSNAME;
1190
1191 IF P_STEPSEQARRAY.COUNT <> v_numofrows THEN
1192 RAISE_APPLICATION_ERROR(-20000, 'Cant Update Step Sequences - Mismatch in number of sequences received');
1193 END IF;
1194
1195 LOOP
1196 IF P_STEPSEQARRAY.EXISTS(v_index) THEN
1197
1198 UPDATE jtf_diagnostic_decl_test_steps
1199 SET EXECUTION_SEQUENCE = v_index,
1200 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1201 LAST_UPDATE_DATE = SYSDATE,
1202 LAST_UPDATED_BY = P_LUBID
1203 WHERE appid = P_APPID
1204 and GROUPNAME = P_GROUPNAME
1205 and TESTCLASSNAME = P_TESTCLASSNAME
1206 and TESTSTEPNAME = P_STEPSEQARRAY(v_index);
1207
1208 IF SQL%NOTFOUND THEN
1209 RAISE_APPLICATION_ERROR(-20000,
1210 'Cant Update Step Sequence, Record Not Found'
1211 || P_APPID || ' '
1212 || P_GROUPNAME || ' '
1213 || P_TESTCLASSNAME || ' ' || 'OLD EXEC SEQ: ' || v_index);
1214 END IF;
1215 v_index := v_index + 1;
1216
1217 ELSE
1218 EXIT;
1219 END IF;
1220
1221 END LOOP;
1222
1223 END UPDATE_STEP_SEQ;
1224
1225
1226
1227 ------------------------------------------------------------
1228 -- procedure to report name back to framework
1229 ------------------------------------------------------------
1230 PROCEDURE getComponentName(str OUT NOCOPY VARCHAR2) IS
1231 BEGIN
1232 str := 'Declarative Diagnostic Test';
1233 END getComponentName;
1234
1235 ------------------------------------------------------------
1236 -- procedure to report test description back to framework
1237 ------------------------------------------------------------
1238 PROCEDURE getTestDesc(str OUT NOCOPY VARCHAR2) IS
1239 BEGIN
1240 str := 'This is a declaratively constructed diagnostic test for DB setups';
1241 END getTestDesc;
1242
1243 ------------------------------------------------------------
1244 -- procedure to report test name back to framework
1245 ------------------------------------------------------------
1246 PROCEDURE getTestName(str OUT NOCOPY VARCHAR2) IS
1247 BEGIN
1248 str := 'QAPackage Test';
1249 END getTestName;
1250
1251
1252 ------------------------------------------------------------
1253 -- FUNCTION to report test name back to framework
1254 ------------------------------------------------------------
1255 FUNCTION getTestMode return INTEGER IS
1256 BEGIN
1257 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
1258 END getTestMode;
1259
1260
1261
1262
1263 ------------------------------------------------------------
1264 -- procedure to provide/populate the default parameters for the test case.
1265 ------------------------------------------------------------
1266 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
1267 tempInput JTF_DIAG_INPUTTBL;
1268 BEGIN
1269 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1270 -- tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'USERNAME','SYSADMIN');
1271 -- tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'APPID','690');
1275 defaultInputValues := tempInput;
1272 -- tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'RESPID','21841');
1273 -- tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ORGID','');
1274 -- tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'PROFILE_LEVEL','');
1276 EXCEPTION
1277 when others then
1278 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1279 END getDefaultTestParams;
1280
1281
1282 ------------------------------------------------------------
1283 -- procedure to insert the count step type into the
1284 -- jtf_diagnostic_decl_test_steps table
1285 ------------------------------------------------------------
1286 PROCEDURE insert_core_steps(
1287 qAppID IN VARCHAR2,
1288 newTestName IN VARCHAR2,
1289 addToGroupName IN VARCHAR2,
1290 stepType IN VARCHAR2,
1291 newStepName IN VARCHAR2,
1292 newStepDesc IN VARCHAR2,
1293 errorType IN VARCHAR2,
1294 newStepErrMsg IN VARCHAR2,
1295 newStepFixInfo IN VARCHAR2,
1296 newStepTableName IN VARCHAR2,
1297 newStepQuery IN VARCHAR2,
1298 logicalOperator IN VARCHAR2,
1299 val1 IN VARCHAR2,
1300 val2 IN VARCHAR2,
1301 isUpdate IN VARCHAR2,
1302 P_LUBID IN NUMBER) IS
1303
1304 v_ordernumber jtf_diagnostic_decl_test_steps.EXECUTION_SEQUENCE%TYPE;
1305 v_temp number;
1306 v_temp_char varchar(1000);
1307
1308 BEGIN
1309
1310
1311 -- making sure that the record is unique
1312 -- by checking if it already exists
1313
1314 select count(*) into v_temp
1315 from jtf_diagnostic_decl_test_steps
1316 where appid = qAppID
1317 and groupname = addToGroupName
1318 and testclassname = newTestName
1319 and TESTSTEPNAME = newStepName;
1320
1321 if v_temp > 0 and not isupdate = 'TRUE' then
1322 raise_application_error(-20000, 'Step name already exists in testcase');
1323 elsif isupdate = 'TRUE' and v_temp > 0 then
1324
1325 -- first cleanup all information from the
1326 -- jtf_diagnostic_arg and jtf_diagnostic_decl_step_cols
1327
1328 v_temp_char := newTestName || '/' || newStepName || '{-STEP/CLASS-}%';
1329
1330 delete from jtf_diagnostic_arg where
1331 APPID = qAppID
1332 and GROUPNAME = addToGroupName
1333 and TESTCLASSNAME like v_temp_char;
1334
1335 delete from jtf_diagnostic_decl_step_cols
1336 where appid = qAppID
1337 and groupname = addToGroupName
1338 and testclassname = newTestName
1339 and TESTSTEPNAME = newStepName;
1340
1341 update jtf_diagnostic_decl_test_steps
1342 set
1343 STEP_TYPE = stepType,
1344 STEP_DESCRIPTION = newStepDesc,
1345 ERROR_TYPE = errorType,
1346 ERROR_MESSAGE = newStepErrMsg,
1347 FIX_INFO = newStepFixInfo,
1348 TABLE_VIEW_NAME = newStepTableName,
1349 WHERE_CLAUSE_OR_SQL = newStepQuery,
1350 LOGICAL_OPERATOR = logicalOperator,
1351 VALIDATION_VAL1 = val1,
1352 VALIDATION_VAL2 = val2,
1353 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1354 LAST_UPDATE_DATE = SYSDATE,
1355 LAST_UPDATED_BY = P_LUBID
1356 where
1357 APPID = qAppID and
1358 GROUPNAME = addToGroupName and
1359 TESTCLASSNAME = newTestName and
1360 TESTSTEPNAME = newStepName;
1361
1362 elsif not isupdate = 'TRUE' and v_temp = 0 then
1363 -- insert the record to the database
1364 -- getting the next sequence number for
1365 -- inserting into the DB
1366
1367 select count(*) into v_ordernumber
1368 from jtf_diagnostic_decl_test_steps
1369 where appid = qAppID
1370 and groupname = addToGroupName
1371 and testclassname = newTestName;
1372
1373 if sql%notfound or v_ordernumber = 0 then
1374 v_ordernumber := 1;
1375 else v_ordernumber := v_ordernumber + 1;
1376 end if;
1377
1378 insert into jtf_diagnostic_decl_test_steps
1379 (
1380 APPID,
1381 GROUPNAME,
1382 TESTCLASSNAME,
1383 TESTSTEPNAME,
1384 EXECUTION_SEQUENCE,
1385 STEP_TYPE,
1386 STEP_DESCRIPTION,
1387 ERROR_TYPE,
1388 ERROR_MESSAGE,
1389 FIX_INFO,
1390 MULTI_ORG,
1391 TABLE_VIEW_NAME,
1392 WHERE_CLAUSE_OR_SQL,
1393 LOGICAL_OPERATOR,
1394 VALIDATION_VAL1,
1395 VALIDATION_VAL2,
1396 OBJECT_VERSION_NUMBER,
1397 CREATED_BY,
1398 LAST_UPDATE_DATE,
1399 LAST_UPDATED_BY,
1400 LAST_UPDATE_LOGIN,
1401 CREATION_DATE
1402 )
1403 values
1404 (
1405 qAppID,
1406 addToGroupName,
1407 newTestName,
1408 newStepName,
1409 v_ordernumber,
1410 stepType,
1411 newStepDesc,
1412 errorType,
1413 newStepErrMsg,
1414 newStepFixInfo,
1415 'N',
1416 newStepTableName,
1417 newStepQuery,
1418 logicalOperator,
1419 val1,
1420 val2,
1421 1,
1422 P_LUBID,
1423 SYSDATE,
1424 P_LUBID,
1425 NULL,
1426 SYSDATE
1427 );
1428 end if;
1429
1430 END insert_core_steps;
1431
1435 p_testclassname IN VARCHAR2,
1432 PROCEDURE GET_TEST_STEPS(
1433 p_appid IN VARCHAR2,
1434 p_groupName IN VARCHAR2,
1436 p_teststepnames OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1437 p_teststepdesc OUT NOCOPY JTF_VARCHAR2_TABLE_4000) IS
1438
1439 V_SIZE NUMBER;
1440
1441 cursor testlist is
1442
1443 select TESTSTEPNAME, STEP_DESCRIPTION
1444 from jtf_diagnostic_decl_test_steps
1445 where APPID like p_appid
1446 and GROUPNAME = p_GROUPNAME
1447 and TESTCLASSNAME = p_TESTCLASSNAME
1448 order by EXECUTION_SEQUENCE;
1449
1450 BEGIN
1451 p_teststepnames := JTF_VARCHAR2_TABLE_4000();
1452 p_teststepdesc := JTF_VARCHAR2_TABLE_4000();
1453
1454 V_SIZE := 0;
1455
1456 FOR x in testlist
1457
1458 LOOP
1459 V_SIZE := V_SIZE + 1;
1460 p_teststepnames.extend;
1461 p_teststepdesc.extend;
1462
1463 p_teststepnames(V_SIZE) := x.TESTSTEPNAME;
1464 p_teststepdesc(V_SIZE) := x.STEP_DESCRIPTION;
1465 END LOOP;
1466
1467 END GET_TEST_STEPS;
1468
1469
1470 PROCEDURE DELETE_STEPS(
1471 P_APPID IN VARCHAR2,
1472 P_GROUPNAME IN VARCHAR2,
1473 P_TESTCLASSNAME IN VARCHAR2,
1474 P_DELSTEPARRAY IN JTF_VARCHAR2_TABLE_4000) IS
1475
1476 v_index BINARY_INTEGER := 1;
1477 v_execution_sequence number;
1478 v_step_type varchar2(100);
1479 v_diagnostic_testname varchar2(250) := '';
1480
1481 BEGIN
1482 LOOP
1483 IF P_DELSTEPARRAY.EXISTS(v_index) THEN
1484
1485 select distinct EXECUTION_SEQUENCE into v_execution_sequence
1486 from jtf_diagnostic_decl_test_steps
1487 where APPID = P_APPID and groupname = P_GROUPNAME
1488 and TESTCLASSNAME = P_TESTCLASSNAME and
1489 TESTSTEPNAME = P_DELSTEPARRAY(v_index);
1490
1491 select step_type into v_step_type
1492 from jtf_diagnostic_decl_test_steps
1493 where APPID = P_APPID and groupname = P_GROUPNAME
1494 and TESTCLASSNAME = P_TESTCLASSNAME and
1495 TESTSTEPNAME = P_DELSTEPARRAY(v_index);
1496
1497
1498 -- make sure that incase this was a DIAGNOSTICTEST
1499 -- step type then the arguments table is also cleaned up.
1500
1501 IF v_step_type = 'DIAGNOSTICTEST' THEN
1502
1503 select table_view_name into v_diagnostic_testname
1504 from jtf_diagnostic_decl_test_steps
1505 where APPID = P_APPID and groupname = P_GROUPNAME
1506 and TESTCLASSNAME = P_TESTCLASSNAME and
1507 TESTSTEPNAME = P_DELSTEPARRAY(v_index);
1508
1509 v_diagnostic_testname := P_TESTCLASSNAME
1510 || '/' || P_DELSTEPARRAY(v_index)
1511 || '{-STEP/CLASS-}' || v_diagnostic_testname;
1512
1513 delete from jtf_diagnostic_arg where
1514 APPID = P_APPID
1515 and GROUPNAME = P_GROUPNAME
1516 and TESTCLASSNAME = v_diagnostic_testname;
1517
1518 END IF;
1519
1520 delete from jtf_diagnostic_decl_test_steps where
1521 APPID = P_APPID
1522 and GROUPNAME = P_GROUPNAME
1523 and TESTCLASSNAME = P_TESTCLASSNAME
1524 and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
1525
1526 IF SQL%NOTFOUND THEN
1527 RAISE_APPLICATION_ERROR(-20000, 'Cant Delete Step, Record Not Found: '
1528 || P_APPID
1529 || ' '
1530 || P_GROUPNAME
1531 || ' '
1532 || P_TESTCLASSNAME
1533 || ' '
1534 || P_DELSTEPARRAY(v_index));
1535
1536 END IF;
1537
1538 update jtf_diagnostic_decl_test_steps
1539 set EXECUTION_SEQUENCE = (EXECUTION_SEQUENCE - 1),
1540 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1541 LAST_UPDATE_DATE = SYSDATE
1542 where EXECUTION_SEQUENCE > v_execution_sequence
1543 and APPID = P_APPID
1544 and GROUPNAME = P_GROUPNAME
1545 and TESTCLASSNAME = P_TESTCLASSNAME;
1546
1547
1548 -- make sure that incase this was a COLUMN
1549 -- step type then the column table is also
1550 -- cleaned up well.
1551
1552 IF v_step_type = 'COLUMN' THEN
1553 delete from jtf_diagnostic_decl_step_cols where
1554 APPID = P_APPID
1555 and GROUPNAME = P_GROUPNAME
1556 and TESTCLASSNAME = P_TESTCLASSNAME
1557 and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
1558 END IF;
1559
1560
1561 v_index := v_index + 1;
1562
1563 ELSE
1564 EXIT;
1565 END IF;
1566
1567 END LOOP;
1568
1569 END DELETE_STEPS;
1570
1571
1572
1573 PROCEDURE addStringToClob(reportStr IN LONG, detailsClob IN OUT NOCOPY CLOB) IS
1574 tempClob CLOB;
1575 strSize INTEGER;
1576 tmpReportStr LONG;
1577 BEGIN
1578
1579 IF reportStr IS NOT NULL THEN
1580 dbms_lob.createTemporary(tempClob,true,dbms_lob.call);
1581 tmpReportStr := reportStr;
1582 select vsize(tmpReportStr) into strSize from dual;
1583 dbms_lob.write(tempClob,strSize,1,tmpReportStr);
1584 dbms_lob.append(detailsClob, tempClob);
1585 END IF;
1586
1587 EXCEPTION
1588 WHEN others THEN
1589 -- logging here...
1590 null;
1591 END;
1595
1592
1593
1594 END JTF_DECLARATIVE_DIAGNOSTIC;