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 test cases. Testing for similar to what I showed a second ago, which is testing for non-first name, testing for non-last name, testing for non-gender, et cetera. And then we're testing for other things as well. Now if I go ahead and run this test as it is with the program source as it is, bear in mind there is little implementation information at all. Then I think you'd expect a whole bunch of failures to happen, which it did. Because there's not enough information in the program yet to satisfy any of these use cases. So what we'll do now is go back to Toad. And we'll start moving the iteration of the code development forward in time, and then coming back in and running some of these unit tests again. All right. So let's go back to Toad. And we have this initial piece of code stop in the Editor. So I'm going to open up a new one. And these are all ongoing iterations of the code development. So let's go about halfway in and open this code here. And then we have some more implementation details here. And if I compile this and then go back to Code Tester and do a refresh, then what we should see now in the program source is that updated code that we see now. Now so if I go ahead and run these unit tests, I should get some successes now. OK. We can see here that we do. So although the test overall failed, some of the initial use cases like testing for null, first name, last name, et cetera, and some of these other ones have passed, we don't have enough implementation information to code yet to test these other cases from 4.1 forwards. But what is good is that as we've iteratively expanded the code, some of these early test cases have all passed. Now what can happen sometimes in code evolution is bugs get introduced into the code because the developer that's making the changes to the code wasn't involved in the history of that code. And so it's very easy to introduce bugs. So let me illustrate how that can happen, and what a developer needs to do in order to fix that problem by using a combination of unit testing and debugging. All right. So I'm going to load in a further iteration for this code, which is this one here. And I'll compile this. So we don't need this anymore. I'll compile this one. And if I go to Code Tester and do a refresh, and looking at the program source, you can see now we've got additional implementation information. Now what you should expect here is that because this has now moved forward in terms of development, all these prior test cases, of course, should have continued to work. And some of these ones that didn't succeed before should now pass. OK. So let's go ahead and run this test, see what happens. As expected, 4.1, 4.2, 4.3 passed, because they reflect the additional code that was written in. And so those test cases passed. But what we did not expect is that a test case that previously passed has now failed, which is 2.3. So we've done something in the code. If I go back to Toad here, we've introduced something into the evolution of this code which has caused earlier test cases to file. Now if we don't deal with that issue, that bug would effectively make its way into production and could cause a problem. So we clearly need to take some action here to eradicate that bug. So the simple way to do that is to right click this particular use case and Copy Debug Block to Clipboard. What we're going to do now is debug the code in the Toad Editor. So now I go back to Toad. So let's go ahead and debug this code. I have the debug toggle turned on. So let's go and execute the code with PL/SQL debugger. And I'll just leave the input values as they are, because we're copying. So I'm copying the debug block generated by code test into here. And it will use the values that came from that particular test. You can see them in here. That's the values that it's actually going to use. I'm also going to turn on the profiler, because I want to see what the code coverage is as this program works. So let me go ahead and execute. So the execution has now terminated. OK. So let's go to the profiler tab and actually see what's going on with this program. See if there's anything that is not running when it should be running. OK. So let's do a refresh and just sort your runs by their run date. Select one of the lines inside the source code. And we covered this in a previous video. You can see in the gutter here that we have some colored markers. Now the red markers indicate code that doesn't execute. And the green markers indicate code that does execute. Now for some reason, if we go back to the unit test, this 2.3, which is the wrong sex, invalid person, that piece of code is generated by this section here, this case statement. And we can see from this execution this block is not running at all. And so we need to take action on this. Now the reason that it's not working is actually because this is now in the wrong place. We entered some code. This case statement is now in a situation where it doesn't execute at all. And so we need to take it out and put it in front of the Select statement. And when we do that, that should eradicate the problem. I'm going to illustrate that with a modified version of this code. So if I go in and select-- so here is the modified store procedure with the case statement ahead of the select statement, as we mentioned before. So let's compile this. Now go back to code tester. And refresh. Let's check that we have the correct version of the program source. There it is there with the case statement ahead of the select statement. So hopefully that's corrected the error. So let's go ahead and run this. And we can see now that 2.3 has passed. OK. And then just to complete the picture, let's go to the final iteration of the code, which is this one here. So this is the fully completed code. It has all the implementation detail necessary. All of those test cases should pass. Let me compile this code. And go back to code tester and refresh. Let's look at the program source. Again, that's the completed code. And then hopefully, all of the remaining test cases should now pass. Run. And that's it. They all pass. So that's how to create a more comprehensive unit test, and how to use unit testing in conjunction with the debugger to eradicate bugs in your code. Thanks, so much, for listening.