How to perform data-driven testing of PL/SQL code in Toad for Oracle

Hi, folks. My name is Denis O'Sullivan, and I'm a systems consultant with Quest. Today, I want to show you how you can perform data-driven testing on your PL/SQL objects. On screen, I have a simple procedure called remove room by name. When I pass in the name of a room, it gets deleted from the room's table. My room's table has six rows, and I also have a table called room contents, which has a foreign key constraint on the room key column. That means I won't be able to delete any rooms that have contents. In this example, rooms one, two, and three have contents, and rooms four, five, and six do not. So going back to my procedure, there are two scenarios I'd like to test here, first of all, when I pass in the name of a room without contents, and secondly, when I pass in the name of a room with contents. So I'm going to pop over to code test for Oracle and create a new test definition. A test definition is a group of related test cases. I'm going to select the object I'm working with. And now I can start creating test cases. So my first test case is to test for rooms without contents. So there's two things I need to provide code tester [INAUDIBLE]. First of all, the inputs for the object, and secondly, the expected outcomes based on those inputs. So I'm going to, first of all, add the inputs. I can insert a literal bedroom, for example. But there's no reason why bedroom can't have contents in the future. So in this particular example, an expression would be more appropriate. So I've inserted a select statement that will always return rooms without contents. So once I have the input sorted, I go to the Outcomes tab, and I tell the two my expected outcome. So my expected outcome in this scenario, that I expect a row to be deleted from the table. So I specify table then I select the table in question. You can also search for the table. And then I specify the outcome. In this case, I'm deleting rows, so I expect the count after to be left [INAUDIBLE]. And then I simply select Save and Close. And I can run my test definition. I can see here I have one test case call rooms without contents, and has passed, and I can look at the result of that test. Now I want to add my second test case. So I go to Add Unit Test, Create New Test, and this time, I'm testing rooms with contents. Again, I need to put in my inputs. I could put in a literal here, but I'm going to use an expression again because a room which has contents now may not have contents in the future. Once I've sorted my inputs, I go to my outcomes. This time, I don't expect any data to be deleted from the table, I expect an exception to be raised. I'm going to select Exception instead of Table. Now, I can insert the exact exception number, but I can also just select Raises Any Error. So as long as an exception is raised, I'm happy. I'll select Save and Close there, right click on my test definition, and Run. And now, I can see our two test cases, which ran successfully. I can see my second test case ran successfully because an exception was raised. So if I pop back to [INAUDIBLE] for Oracle, any changes I make to this procedure going forward, I can call these data-driven test while I'm developing my procedure. So if I click the Execute code tester for Oracle button, I can select Previously Created Tests, and execute them. So now I know any changes I made to this procedure haven't broken tests that we're previously working on. So that's how you can add data-driven testing to your PL/SQL objects. Thanks for tuning in.