ThatJeffSmith

Unit Testing Your PL/SQL with Oracle SQL Developer

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.

'Select' Current Repository' will allow you to specify a connection to deploy the repository to.

‘Select’ Current Repository’ will allow you to specify a connection to deploy the repository to.

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.

Do this, then do this, then...you get the idea.

Do this, then do this, then…you get the idea.

To build a test, the easiest way to start is to just right-click on your program:

This will launch the unit test wizard

This will launch the unit test wizard

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:

Pick a table, some or all of the rows, give it a name...

Pick a table, some or all of the rows, give it a name…

And then after your test is done, decide what you want to do with it:

I need to get that ONE row back to where it was before I ran this test...

I need to get that ONE row back to where it was before I ran this test…

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:

Yes or no, do you want it?

Yes or no, do you want it?

If yes, then:

What ran, what didn't, how many times, how long did it take...

What ran, what didn’t, how many times, how long did it take…

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.

Where is your repository, what test do you want to run, what database do you want to run it on?

Where is your repository, what test do you want to run, what database do you want to run it on?

Test Re-Use
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 :)

You can save what you're doing to the library, or pull stuff from the library.

You can save what you’re doing to the library, or pull stuff from the library.

There’s so, so, so much more to talk about. Like how to actually do the tests :) And I’ll talk about that next.