Create a simple PL/SQL unit test using Toad

Hello. My name is John Pocknell, Toad for Oracle Product Manager. And welcome to this multi-part series on enabling agile database development using Toad for Oracle. In Step 5, I'm going to show you how easy it is to create a simple PL/SQL unit test using Toad. So why is this important? Well, unit tests ensure that your PL/SQL code is functionally capable and covers all known use cases. Untested PL/SQL code can introduce bugs in production, which can lead to expensive rework and associated business cost. Testing also helps to document ongoing code development, given the amount of PL/SQL code that is legacy. Simplifying the unit test creation workflow makes it more likely that more developers will create tests for their code. Unit tests can be stored in a repository so they can be used in future regression testing. And unit testing also becomes an essential part of moving your traditional develop processes to agile through automation. So let's get into a demo to show you how easy it is to create unit tests in [INAUDIBLE]. What we're seeing here is a piece of code that we covered in some of the early demos around profiling and SQL optimization. So this is a stored procedure that has been recently modified. Now I want to create a unit test for it and make sure I have a range of unit tests that cover multiple use cases, just to make sure that any future changes to this code won't introduce any bugs. The process of creating unit test simply begins with executing the code in a normal way. So I come up to the editor tool bar and set Execute PL/SQL. I'm not going to compile any reference [INAUDIBLE] debug information, I just want to run the code. So in the Set Parameters window, just simply enter the input parameter that you want to use this particular code [? run. ?] I want to use a letter Y. So this is all department names beginning with Y. And when I run this, I'll get a particular output value. And it's that combination of inputs with expected outcomes that is necessary to create at unit test. So I'll go ahead and execute the code without input value. That's now complete, and you can see in the [? DBH ?] output window I now have a different output value. Now go to the PL/SQL results tab. If you don't see this tab, simply right click here and select it. In the PL/SQL results tab, I can see the input value that I entered and the resulting output value And that's the information I need, the minimum information I need, to create a unit test that reflects this particular use case. So I go to the little toolbar here and select Create Unit Test. Give your test a name. Notice it's going to take with it the object name. You can specify particular outcomes, you can say I'm expecting a particular type of Oracle exception to be part of this, that's normal behavior. I expect it to return that exception. You might want to test for a particular execution time. And so there's a test you can put in for that, too. Click OK, and we've created our unit test. So the [INAUDIBLE] unit test is being created and it reflects the conditions under which we executed this code. Gos to the Next button [? along, ?] which is Execute Code Tester for Oracle tests and display the results. That code test is a part of the Toad development suite, so you need the Toad development suite in order to have a repository built in which you store all your unit tests. So select this button. And now we can see that Demo 101, which is a test we just created, along with all the other tests that reflect different use cases for this program. So I'm going to select them all because I want to make sure that the change I just made didn't result in a bug being introduced. Click OK. This is going to now select all of the unit tests that are stored in the repository for this particular stored procedure, and run the associated unit tests with different values. So that's a really simple way of creating unit tests. Thanks very much for listening.