Toad World® Forums

How to test tables and views?

Hi, I’m new to QCT. I was wondering how to test the behaviour of tables and views.
As far as I understand QCT so far, such tests are only possible within a wrapper procedure? Is that correct? Any suggestions, experiences testing tables and views are appreciated. Thank you.

You are right. You cannot directly “test” a table or view - though I am not even sure what that would mean.

You test the impact on a table or view of running a particular program. So you define a test on, say, procedure ABC. ABC itself modifies the table. You then create dataset tests for ABC.

SF

Wow, that was a very fast answer! Thank you. To give you more insight, here are some ideas that I have in mind regarding table and view testing:

  • Verify that table check constraints work as you expect to: allowing good data in and keeping bad data out.
  • Verify that triggers work as expected, that they i.e. write correctly into history tables.
  • Verify FK behaviour and making sure that allowed data constellations can be inserted/deleted while undesired data constellations are failing as expected.
  • Prepare tables with certain data and verify that a view returns an expected result.
  • Testing response times of queries on views and making sure they won’t exceed a defined limit.
  • Just test validity of views or objects in general (via data dictionary).
  • Test if certain required values are in a table.
  • Compare table, view and/or query result sets directly, maybe also between different schemas.
  • Verify that table check constraints work as you expect to: allowing good data in and keeping bad data out.

You will need a procedure to perform the desired DML. Then create dataset tests to validate correct behavior.

  • Verify that triggers work as expected, that they i.e. write correctly into history tables.

See above.

  • Verify FK behaviour and making sure that allowed data constellations can be inserted/deleted while undesired data constellations are failing as expected.

See above.

  • Prepare tables with certain data and verify that a view returns an expected result.

Use customization section(s) to create the setup steps. Then run procedure, create test on view.

  • Testing response times of queries on views and making sure they won’t exceed a defined limit.

Put query inside procedure. Create elapsed time test for procedure.

  • Just test validity of views or objects in general (via data dictionary).

You can test for object existence with a pre-defined test, but for status, you will need to test a function that returns the status for an object, such as:

CREATE OR REPLACE FUNCTION object_status (s IN VARCHAR2
, n IN VARCHAR2
, t IN VARCHAR2)
RETURN VARCHAR2
IS
l_status all_objects.status%TYPE;
BEGIN
SELECT status
INTO l_status
FROM all_objects
WHERE owner = s AND object_name = n AND object_type = t;

RETURN l_status;
END object_status;
/

  • Test if certain required values are in a table.

Use procedure to either change table or simply provide a “hook” in Code Tester, create test on query.

  • Compare table, view and/or query result sets directly, maybe also between different schemas.

You will still need a procedure to drive the testing, but then you set up the dataset tests. You can test/compare contents in other schemas, as long as you have authority from the schema into which you are connected in Code Tester.

Cheers, SF

Hi

I’ve just published a blog post on comparing datasets and I hope this can inspire you to solve the comparison part of your task.

http://toadworld.com/BLOGS/tabid/67/EntryId/680/Comparing-Datasets.aspx

Hope this helps.

Cheers

Finn


Finn Ellebaek Nielsen | Oracle Test Coach | http://oracletesting.com

Message was edited by: finn.ellebaek.nielsen

“Comparing Datasets” by Finn Ellebaek Nielsen can be found here now:

www.toadworld.com/…/comparing-datasets.aspx