Today I read a post by Jeroen van Wilgenburg on the Amis Technology Blog: Why you should never ever copy code – An example of complete chaos. He states that copying code (in the same schema) is almost always a bad idea. I agree with this point of view. In my opinion you should always have an eye open for creating small utilities that can be used in different locations. Not only in PL/SQL, but in any programming language.
The trouble is most of the time, finding the generic bits in your code and if you found this, making this code generic by using parameters instead of the available variables. Sometimes it seems you are making things more complex, because of the large number of parameters, but I am confident, that in the long run things are less complex.
One thing about Single Point Of Definition is that you have only one place to fix issues with your code. You might think of it as Single Point Of Failure. It’s also easier to test your code. You only have to test one procedure or function instead of many.
At first sight it might seem easier to copy the code, but to make your code easier to support and to maintain, it’s better to use Single Point Of Definition.