We have a java application that interfaces to our oracle database via pl/sql packaged procedures…the application only ever calls a single API package that sits in front of all our other underlying packages. The java application connects to oracle via a single oracle user that just has execute privileges on the single API package.
The API generally just directly calls the sub package / procedure that the app wants to run…although sometimes there is a small amount of logic in the API package itself and it is also usually the place where i tend to do the input parameter validation.
My question is this:
I have started using Code Tester to create unit tests for all my underlying procedures and functions in all my packages. This has meant that in order to satisfy the unit tests that i have written in code tester, my procedures have grown slightly …eg in order to pass all my CodeTester unit test i have written extra code to validate correctly all their input parameters…which has expanded the original procedure.
So now I am wondering …should i use CodeTester to write unit tests for all my API packages that are calling all these underlying procedures?? well the fact that sometimes the API has a bit of logic in it and also I tend to do some validation in the API package, would imply YES I should really write unit tests for them. However this would mean that I am validating my input parameters again…once in the API and also again in the underlying procedure that the API is calling.
This doesn’t’t seem like a good idea in terms of performance. I can see that you would want to validate all your inputs when you write modular functions and procedures that can be re-used …but many of these procedures are very application specific and would not be re-used elsewhere.
What is your “Best Practice” methodology for writing unit tests? Generally speaking would you ALWAYS validate every input parameter for every discrete procedure …even though you knew the inputs were already validated higher up in a parent procedure?? or would you pick and choose and eg do ALL the input parameter validation in the API procedure?
How would CodeTester fit into these methods?? eg strictly speaking in order to pass thorough unit tests of your procedures then do should you validate parameters in every procedure that you write…whether API type or not??