If you want to unit test your PL/SQL, then Oracle SQL Developer can help make that easier for you. I’m not going to try to convince you to test your code, but if you’re already rearing to go, then read on.
So I have a long history with unit testing tools for PL/SQL, but I won’t bore you with that. What I will do, is give you a teaser, and show you a few features that I really appreciated as a person who doesn’t like to code to test code.
SQL Developer’s unit testing features REQUIRES a repository. However, it only requires ONE repository. So you do not have to set this up for every instance you want to test – you only set it up once.
To get started, open the Unit Test panel under the ‘View’ menu.
Then on the Tools Menu, access the Unit Test flyout.
Once the repository is setup, the Unit Test panel will ‘know’ where it is going forward.
Now you are ready to build and run your tests!
The Sample Test and Some Cool Stuff
The Help has a chapter on the Unit Testing, and an entire section that steps you through building a test, using AWARD_BONUS() in the HR schema. So you’ll need HR.
To build a test, the easiest way to start is to just right-click on your program:
Now, instead of taking you through the ins and outs, I thought I would just call out a couple of cool features I ‘discovered’ while playing with the feature. Things that made me go, oh the users will LOVE that – that kind of stuff.
Startup and Teardowns
Before you run a test, you might want to do some ‘stuff.’ And after you run a test, you might want to undo some ‘stuff.’ The tool makes it pretty easy to define these, and even gives you an interface for backing up an entire table or portion of a table so you can get ‘the data back’ after you run your tests.
So to save off some data:
And then after your test is done, decide what you want to do with it:
Code Coverage Reports
Testing is good, but if you have 100 test cases for your package but only ‘exercised’ 25% of your program, then you still have a lot of ground left to cover. In SQL Developer, you only need to check a single box to collect this data:
If yes, then:
Running Tests from the Command Line
So you define your tests until you’re blue in the face, and then what? You probably want to run your tests each time your program changes. So, what you want to do is build the unit testing into your automated build process. Our CLI makes that pretty straightforward.
Kinda like code-reuse, but with testing. So, if you define a tear down, or if you define a bit of code for a test, or a query for defining a list of inputs..you can take that and add it a library. Then you can use this over and over in your tests. It’s not only convenient, it encourages more testing, and it cuts down on errors – assuming your library items aren’t buggy 🙂
There’s so, so, so much more to talk about. Like how to actually do the tests 🙂 And I’ll talk about that next.