Testing the contents of tables, views, etc. is tricky due to several issues:
These are persistent data structures defined in a namespace in Oracle. We generally don’t want to or cannot change the names of those objects in our code, so we have to very carefully manage them.
The structures we will be testing must be populated with the good test data. And the datasets against which these structures will be tested must have the expected data in them.
It is usually the case that you cannot compare all columns of a row, because some values like primary keys and audit columns will be generated as unique values for new rows.
For small tables and queries with a small number of rows returned, a reasonable approach to take is to make a local copy of the structure before the program is run, make changes to that copy and then use it as a control against which the table/query can be compared after the program is run.
We will generate the code to do this for you in the Expected Results edit window.
Over time I would like to add generation support for a variety of approaches. What would you like to see us do?