I'm new to UT PLSQL. We have a existing application which contains of lots of stored procedures. Most of the procedures at the end insert or update values to tables. Is there any way in utplsql to test these table values?? I can see lot of examples on functions alone than stored procedures.
Thank you
Test the data
In your unit test you can test more than just function results. After executing your stored procedure, you can just query your table and see if it inserted what you expected it would insert.
Depending on the stored proc it might be hard to find exactly which data it inserted, but in many cases you'll be able to do that, either because you can search for specific values, use a sequence to get the inserted ID, and so on.
To compare the data with your expectation, you select the data into variables and compare those against expected values (you could do that in a cursor loop if you need to compare multiple rows), but it may be easier to compare two cursors, one with expected data (you can construct this using select from dual), and one with the actual data.
The documentation, and especially the chapter Advanced data comparison, contains various examples on how to compare cursor data. I'm not gonna paste them here, because I don't know which one applies to your case, and both utPLSQL and its documentation are very much alive, so, it's best to check out the latest version when you need it.
Refactor your proc into a package
Nevertheless, you may find that it's hard to test big, complicated stored procs by the data they output. I've found that the easiest way to refactor this, is to create a package. In the package you can expose a procedure just like the one you have now, but it can call other procedures and functions in the package, which you can also expose. That way, it's easier to test those individual parts, and maybe you can test a big part of the logic without needing to write data, making the tests easier to write and faster to execute.
It's not completely elegant, since you're exposing parts, just for the purpose of testing, that you otherwise wouldn't expose. Nevertheless, I found it's typically really easy to refactor a stored proc into a package, especially if you already used sub-procedures in the stored proc, and this way you can quickly, and without much risk get to a structure that is easy to test.
It does't have to be in packages, you could split it up it separate smaller procedures as well, but I like packages, because they keeps all the logic of the stored proc together, and it allows you to call the proc in roughly the same way as you would before. A package is little more than a grouped set of stored procedures, functions and types. If your application would require it, you can even keep the original stored proc, but let it call its counterpart in the package, that way you got your refactoring without needing to change any of the clients.
Refactor parts of your proc into an object type
If you go a step further, you can make object types. There are various advantages to that, but they work quite different from packages, so if you're not familiar with them, this might be a big step.
Again a client may not be ready to pass objects like this, so I tend to create two (or more) package procedures. One is the official entry point for the application. It won't do much, except create an object of type X and pass it to the second procedure, that contains the actual logic (optionally split up further). This way, my application can call a simple stored proc, while my tests can call the second stored procedure and pass it an instance of subtype Y if needed.
