As I mentioned elsewhere, we have a good million lines of stored procedures here. There is a mix of direct DML in the code, and DML wrapped with stored procedures. Without exception the wrapped DML is easier to maintain. I recently had to recode a large subsystem that was rife with direct DML calls that was quickly becoming a maintenance nightmare, especially since it was competing with other portions of the code using direct DML on the same tables. Any change to the tables required changes to several different pieces of code.
Now, I'm not saying that things have to be wrapped in stored procedures, but they should be wrapped in some fashion. There should be only one access point to the underlying data, whether that's a stored procedure or some wrapper class in the external code. Using stored procedures, however, ensures that multiple external programs have a consistent interface. This is a big deal here, where we have PL/SQL, C++, Perl, and Java all accessing the same tables.