CODEWITHSUNDEEP
sqloracle-databaseunit-testing
JsCoder
JsCoder

Reputation: 1733

Unit Test SQL Procedure

How do you unit test the following procedure:

procedure some_prc as
begin
    select t.a, t.b, t.c, t.d, count(*) from t inner join t1 on t.f1 = t1.f2
    group t.a, t.b, t.c, t.d;
end;

the query is run by sqlplus.

Upvotes: 0

Views: 401

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

To perform an automated, repeatable unit test would require an environment where you have total control of the data - e.g. a local install of the database on your own PC, or a dedicated database or schema on the server that only you can access.

Only then can you do what you would need to do i.e.

  1. Run a pre-test script to set up the initial condition for the test, i.e. the exact data it will work on.
  2. Run your procedure.
  3. Run a script to inspect the data after running the procedure and check that it matches your expectations.

Even in a controlled environment you are going to have complications if your data involves dates, since the value of SYSDATE is probably not under your control!

There is a mechanism for Unit Tests along these lines built into SQL Developer. Also there is a tool call utPLSQL you could investigate. I can't recommend for or against either as I haven't really used them.

Upvotes: 2

Related Questions