There is a need to manipulate relational data from object oriented code. ORMs are tools that facilitate that. The Object/Relational impedance problem doesn't go away if you hand-carve the code, it just makes you work hard on all the points of contact instead of just the problematic ones.
The real "problem" with ORM is when people use such tools as a way of avoiding having to understand databases (and specifically SQL). Fortunately that's becoming less common at least within the Enterprise Java world where I live and breath.
I think you've hit on something here. I came to start using ORMs after 10+ years of writing / optimising databases and SQL. When using an ORM (and most of my work is done with a probably not-well-known one, XPO from DevExpress) I'm aware of what is (or should!) be happening under the hood; my prior experience with 'bare metal' is extremely useful, nay essential to creating a performant system. On occasion, it's necessary to do a direct SQL query, but not often.
Sure, the apps I'm writing are for small-medium business, but XPO 'just works'. Context is important; if I was working on something with more users / tighter speed requirements, an ORM may or may not be the best choice. Still, this falls into the 'right tool for the job' category that good developers are already aware of.
The real "problem" with ORM is when people use such tools as a way of avoiding having to understand databases (and specifically SQL). Fortunately that's becoming less common at least within the Enterprise Java world where I live and breath.