utPLSQL: Test using an existing table

Question

There's a table named summary_table which compares two tables and checks if those two table's columns, constraints and data are same or not. One of the table being checked(table1) is the first version and another(table2) is the second. The summary table has columns like columns_status(checks if the columns of both tables are same or not), data_status(checks if the data in both tables are same or not) and constraints_status(checks if the constraints of both tables are same or not) which shows PASS or FAIL. There are multiple tables(1000+) in the summary_table. How can i test each of them?

This is how the summary_table looks like:

TABLE1     TABLE1     COLUMN_STATUS     DATA_STATUS   CONSTRAINTS_STATUS
--------------------------------------------------------------------------       
CUST1      CUST1         PASS              PASS              PASS
SUPP1      SUPP1         PASS              FAIL              PASS

EDIT: I've got a table which consists of multiple rows. Each row has two table names which are already compared with status columns showing if it has passed or failed. I want to test each rows using the status column.

Use dynamic SQL to query the data, then write the appropriate tests to test the results. — Bob Jarvis - Слава Україні, Nov 21 '18 at 13:17
This seems related to [your other recent question](https://stackoverflow.com/q/53405858/146325). Perhaps you should have improved that question with more details rather than asking a very similar question? — APC, Nov 21 '18 at 13:39
In the previous question i had to compare two tables but in this I've got a table which consists of multiple rows. Each row has two table names which are already compared with status columns showing if it has passed or failed. I want to test each rows using the status column. — Success Shrestha, Nov 22 '18 at 04:41

GolezTrol · Answer 1 · 2019-09-15T19:53:23.513

It is possible to check specific cursor data, or to simply query the count of rows that contain a fail. That way, your check is really simple, but doesn't report very specific output.

If I understand correctly, you basically want to check the outcome of another test suite, and therefore, I think it's a good idea to keep this test generic in its setup (no hard coded list of tables, for instance), but at the same time let it report specific issues.

A possible way to do this, is to query all the rows in the table, and check each of the three columns. You can use the table name in a custom message, so the test result will contain specific information about which table failed the test. So for your test data, the test could look like this:

  -- %test(All bladiebla checks in summary table should be passed)
  procedure Check_Summary_Status;


  procedure Check_Summary_Status is
  begin
    for r in (select * from Summary_Status t) loop
      ut.expect(r.COLUMN_STATUS, 'Column status for table ' || r.Table1).to_equal('PASS');
      ut.expect(r.DATA_STATUS, 'Data status for table ' || r.Table1).to_equal('PASS');
      ut.expect(r.CONSTRAINTS_STATUS, 'Constraint status for table ' || r.Table1).to_equal('PASS');
    end loop;
  end;

And the output would look like this, clearly stating which check failed, and for which table.

1) check_summary_status
    "Data status for table SUPP1"
    Actual: 'FAIL' (varchar2) was expected to equal: 'PASS' (varchar2) 
    at "MYSCHEMA.TST_MY_DEMO", line 10 ut.expect(r.DATA_STATUS, 'Data status for table ' || r.Table1).to_equal('PASS');

utPLSQL: Test using an existing table

1 Answers1