Perform regression tests using PL/SQL Dev

[jellegaa]

Perform regression tests using PL/SQL Dev.

I am currently doing application regression test by comparing complete images of tables after a test run. I do this be either:
- Using

 

[Marco Kalter]

The next PL/SQL Developer version will have a "Compare Table Data" utility. You would have to save the reference tables in a separate schema, and compare them later with the new compare tool. You will get a script with the differences.

 

[jellegaa]

Well, it seems like you are all ahead of me!
To get it clear: We are talking about comparing schemas, right? Does it go any further than doing data comparing of tables with same name and definition?

In any case - I'm looking forward to see it!

 

[Marco Kalter]

Does it go any further than doing data comparing of tables with same name and definition?

No, that's basically it. You get the option to generate a script with the difference, or to directly update the target schema.

 

[Helene]

This sounds very interesting ! However we would need to be able to exclude some data from the comparison. All tables include what we call "system columns", among which are a column holding the date and time for last update of the row.

Will there be a way to select what tables and columns (maybe even rows) to include in the comparison?

With regards
Helene

 

[Marco Kalter]

You can select tables, but not columns. This is planned as an enhancement though.

 

[Stew Stryker]

jellegaa,

Have you looked at Steven Feuerstein's utPLSQL regression testing suite? It's free. It lets you write programs to do all sorts of regression tests, not just table comparisons. I used it for a couple years and even created my own "lite" version of it (a single package) to make redeploying to our test db instance easier.

With either one, you can build test sets like this:

aeo_tester.EqTable(msg_in => 'Compare Gift tables',
                   check_this_in => 'new_gift',
                   against_this_in => 'std_gift',
                   check_cols_in => 'id_num, xseq, comment',
                   against_cols_in => 'id_num, xseq, comment',
                   check_where_in => 'id_num > 55',
                   against_where_in => 'id_num < 99999');

Drop me an email if you'd like a copy of my lite version.

 

[ScottMattes]

One of the ways that I test from a Test Window is

- most of our tables have sequences used for PKs, so if I know rows are to be inserted, I save the max pk, call the code to test and then open a cursor after with a select where pk > max_pk

- when I expect certain rows to be updated I open a cursor and select those rows, then call the code to test and afterwards open another cursor on the same rows

- for rows that should disappear I do the same as for the updates

After I have finished checking the cursor result sets I ROLLBACK, so I can do it again if needed.

Another way that I have been thinking of would be to save the SCN and do flashback queries. I imagine that one could save the SCN, run the test, let it commit and then query the before/after results with something like this

select 'before', t1.*,
       'after', t2.*
  from table1 t1,
       table2 t2 as of scn 123456789
 where somecondition

dbms_wm might also be good for this, but I know less about it because ver 9.2 has a problem that as far as I know Oracle isn't going to fix.