Co-workers at Obtiva meet up every Tuesday for a weekly event called "GeekFest." Lunch is ordered for us for free and we use that time to enjoy discussing the latest technologies, giving presentations, demoing programs, and having heated debates about things like mock-based testing vs state-based testing and Ruby vs Python.
So anyways, I always like to think outside the box and ask some crazy questions, and yesterday I committed a crime by asking the following question after I saw a demo of a trigger and a stored procedure written to solve a specific problem that would have been harder to solve with writing application code (rare case): "Now that Oracle allows writing stored procedures in Java and MS SQL Server allows writing stored procedures in C#, isn't that the holy grail for developers that complain about how procedural and non-OO stored procedures are and how they produce difficult to maintain code? The Oracle Java version may be behind its time, but it's light years ahead as a language than that of stored procedures, isn't it?"
I got a backlash almost by everyone, even from the most OO-centric developers. Apparently, I forgot about one very important detail that one of our new co-workers reminded me of. Stored procedure code meshes well with SQL, which is a DSL that makes writing query code much easier than in Java. This ends up with simpler code that is easier to read than embedding SQL queries within another language with a different paradigm, such as Java.
Now, if we take that to the extreme, we would write all the application's query logic in stored procedures. So obviously, there is a point of diminishing returns. Stored procedures may be simple to write for small cases, but when used to write a whole application, they end up with the same issues that prompted the invention of OO methodologies, like lack of expressiveness for domain models due to no support for inheritance and polymorphism, and difficult maintenance as a developer is required to dig many levels into a procedure's code before understanding what it does due to no support for abstraction (assuming the code is well factored in many tiny procedures calling each other.) When a procedure is a method on an object, there is a lot more context to what it does, which saves developers from having to dig to understand what it does.
So, while the stored procedure language is easier than Java for writing code intermingled with SQL for simple cases, that does not necessarily mean it scales well for big projects with complex domains.
One last point that one of my co-workers mentioned that made a lot of sense is that you don't need an Object-Oriented language to write clean code. My response to that though is while that's absolutely true, you may still need an Object-Oriented language to write clean code that is easy to maintain when dealing with a complex domain. Just because the code is clean, it does not mean it's easy to maintain if the language is not expressive enough (no polymorphism, no inheritance, etc...)
What do you think?