Comprehensive PL/SQL unit testing using Code Tester

Hello. This is John Pocknell, Toad for Oracle product manager. And welcome to this series of webcasts entitled Enabling agile database development using Toad for Oracle. In Step 6, we're going to show you how to create a more comprehensive PL/SQL unit test using Toad. In Step 5, we showed you how to create a simple unit test using Toad's editor. This type of test creation will go into more detail about how you can create more comprehensive tests. So why is testing important? Unit tests ensure that PL/SQL code is functionally capable and covers all known use cases that the code is likely to encounter in a production environment when it's used by an application. Untested PL/SQL code can introduce bugs in production, which can lead to expensive rework and associated business costs. Testing also helps to document ongoing code development given the amount of PL/SQL code there is in legacy. Unit tests are stored in a repository so they can be used in future regression testing cycles. Unit testing becomes an essential part of moving traditional development processes to agile, especially given that we can now automate the unit testing execution process. So let's show you in a demo how we can create more comprehensive tests using Toad. So I'm going to show the Toad UI first of all, because we have the beginnings of some new code in here. And what this demo really demonstrates is as code evolves over time through iterative development, you can evolve your unit tests with it so that each time code changes happen, you can regression test all of those changes to make sure that you're not introducing bugs. So in this example, we have a really simple function. It has the declarations section specified. But it doesn't have any implementation details whatsoever. It's taken a number of input values. It's taken first name, last name, title, agenda. The other thing we'll be talking about is Code Tester itself. Code Tester for Oracle is part of code Development Suite. In here we have no tests yet. But we're going to create the test definition, and evolve that test information out to reflect the ongoing evolution of the code. So if I go down to my program here, what we should see is the same piece of code in here. And since it's being compiled in a database, then code testers are reflecting that code in the source code window. So if I go back to test again, I'm going to go ahead and create a new test definition. So in Create Test Definition I'm going to select my function. And I get the Test Builder. Now what I want to do is create a number of simple use cases to get started here. Select the New Test Codes button. And in here my first test is, I want to test for some null values in these four fields. So let's start off with Null First Name. There's the name of the test case. And across in the input values, I'm going to leave the first name as Null, and then just put some characters in here to populate the other fields. OK. So that's that one. And then we go to the outcome. So what do we expect to happen when we do that? Well, we're obviously going to get some sort of exception. And so in column A has three columns here-- A, B, and C. Data change by the program, test site, and expected results. OK. So in the first column I'll just simply select Exception. It's going to raise an exception. I don't want to raise any error, because we don't have enough details in there yet to specify what type of error it will be. But we know at this point in time it will raise an exception of some description. And that's it. So I can save that test. Once I've done that, I can then go ahead and right-click Duplicate This Test Case, and go through a similar process. In this particular case, I want to test for Null Surname. So let's rename this to Null Surname. And in this case, we'll have a value for first name. And surname will be Null. Outcome will be the same, of course. Raise any error. OK. And I go back and select this one, and right-click Duplicate. And in this case we're going to do a Null Title. So the idea of this is just simply to show you how building test cases is actually very fast. Let's go into Title here. Make that Null. Value in there for Surname. And so within a few seconds now created three test cases that test for three different conditions of this function. Test Builder comes with a Quick Build feature. And Quick Build allows you to incorporate other kinds of tests in here. So if you're testing Table Insert operations or Table Delete operations or Table Update operations, these are kind of pre-built. So you can just add these kind of tests, incorporate that into your test case, and you're ready to go. So the other thing you can do with Code Test is you can import test cases that somebody else has created via XML. And that's what I'm gonna do here is I've actually got a series of test cases defined already for this stored procedure. And I'm going to import a bunch of test cases. So here's the file here. It's called Q##SALUTATION.xml. And this is an exported unit test that's been created ahead of time. So I'm going to go ahead and open that. And I can merge that with an existing test if I want to. If there is any test results generated, I can import those. Doesn't apply in this particular case. And so I just go ahead and import that. So now if I expand out this, I can see I've got a test for salutation function. And then a bunch of