Is there any way to apply TDD techniques for dev in PL/SQL

Question

As I have a lot of program logics in DB-server-side in PL/SQL I would like to find out is TDD applicable to PL/SQL. Even more it would be very god if you point me at some information resources that has samples and detailed description. Thanks in advance!

UPD: I need tests to be stored and runned only on my machine, doesn't affecting other team members. Is that possible?

Answer 1

Yes it is - just apply normal TDD cycle (see eg. wikipedia) as in any other programming language:

1. Add a test
2. Run all tests and see if the new one fails
3. Write some code
4. Run the automated tests and see them succeed
5. Refactor code
6. Repeat

You want to have a unit testing framework of course. Unsuprisingly e.g. StackOverflow has questions like Unit testing for PL/SQL or Unit Testing Framework for Oracle PL/SQL?. You've already checked those - haven't you ?

Running tests in isolation is very possible. You'll probably want to have either a dedicated Oracle DB instance or a schema that is 100% under your control.

The only tricky part I've experienced is to test a code that uses database very extensively or when database schema is changing a lot. Populating or faking a database might be tricky and time consuming if the database is complex or if a lot of data is required for test execution. I those cases I will usually fake the database by removing constraints and triggers that makes running tests cumbersome. That's fine because the focus is on a PL/SQL code, not on a database structure and unit testing is not the final verification step.