Some time ago I wrote about what it means to do database testing.. more I think about this and having had some strange situations recently I want to add more to the list of things we should be testing.
Persistence Layer. We should persist the objects to the database using the applications persistence layer and retrieve the objects using the same mechanism and test that we get the same object back. If we have a lot of business logic in out persistence layer we may also want to retrieve the object using Direct SQL and test that the correct values got persisted.
Database Structure. The application assumes a certain database structure and objects to be around for it to function properly and we want to make sure that all the database objects we depend on are there and are defined as we expect them to be, for example if we need a Auto Incrementing column on a table, then we should test that the database has such a column using the database metadata. In other situations you are expecting the Customer.Name column to be 128 characters wide and the database does not have the Customer.Name column at 128 characters, these kinds of errors can be found out using the database metadata.
Database Code Stored procedures need to be tested, using unit testing frameworks like utPlSql or whatever is equivalent to your situation
Database Objects Views are database objects that the application will usually depend on and the views have business logic in them. We need to test these views and you can use your application code to test them or you can use the database unit testing frame work. One I have found is easy to do is to test them using JUnit (or whatever that works for). Setup the correct data in your tables and use the view to retrieve the data and test of the view returns the correct rows for the data in the database. These kinds of test could get really complicated if you have no control over what data is present in the database. DBUnit can help here by providing re-loadable data.