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.

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.

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

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…

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…

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?

If yes, then:

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?

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.

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

Shouldn’t need SYS – but you don’t even have to do that. Install the Unit Test Repos on your local instance. You can run the tests anywhere then – there’s no requirement to have the repository on the database you want to test.

I am following this article trying to figure out how unit test in SQL Developer works, but I am stuck at the very first step, which is when I go to Tools->Unit Tests-> all options under unit tests grayed out, meaning none of them is available. Apparently I can’t go anywhere from here as I can’t even create a new test repository.

I tested SQL Developer 4.0.2.15 on both Windows and Mac and received the same issue. Did I do anything wrong? Actually I barely started.

Jeff, this blog led me to follow through the on-line tutorial, Thanks!

1) I did not see complex data types such as XMLTYPE, SDO_GEOMETRY in the list of supported datatypes for Lookups. Is there a plan to support it?
2) I recommend moving “Lookup Catagory” as a column in the Implementation grid so each parameter can be associated its own Lookup.
3) I could not figure out how to validate my proc or function that returns a CLOB. Any suggestion?

I am told by our AD Technology to create a suite of “automated” unit tests in SQL*Developer (we have 3.0.04).

I followed the tutorial and created a unit test, ran it, but I don’t understand some of the concepts.

My biggest issue is, how does SQL*Developer “validate” the results.

So, taking the award_bonus as an example.

I give the inputs as emp_id = 1 and gross_sales as 10000.
Now, emp_id 1’s comm_pct is 0.1%. So, for this sales, he should get a comm of 1000. His current salary is 5000. So, the award_bonus should increase his salary to 6000, right.

(1.) Now, my question is, when I create a unit test in SD, do I “HAVE TO KNOW” that it will increase the salary of emp_id = 1 from 5000, to 6000 for gross_sales 10000 and comm pct 0.1%? Do I have to know this?

(2.) Ok, in the SD unit test creation wizard, I give emp_id = 1 and gross_sales 10000 as inputs. So, when I go to the next screen, it is asking me how to test the result. Here, I type a query: SELECT COUNT(*) FROM emp where emp_id = 1 and salary = 6000. i.e. When this test is run, the employees salary will be increased to 6000.

I don’t give any teardown. I run the unit test. It says success. Now, I run the test again. In my table the salary has increased to 7000. And my test says SUCCESS. But, the query I gave to validate, will give count(*) = 0, since his salary is not 6000 anymore. So, how does this work? How, does SQL*Developer know it was success.

Thanks for your reply. But that approach just adds another layer I have to install on my target database before running the tests stored in my repository and therefore does not seem very handy to me.

I would prefer a solution where I can test anonymous blocks with binds. So I could assign (in)binds and check the values of the (out)binds afterwards. I would get the flexibility to test types or whatever I want without the need to install anything on the target database. Everything belongig to the test would be stored in the repository.

I mostly write about working with Oracle databases, mostly. I currently work for Oracle, but I'm not an official spokesman, nor am I authorized to speak on behalf of Oracle Corp. In other words, what you read here are my words and ramblings.

If you've detected a bit of snark, that means you've been paying attention. +1 bonus credits for you!