In large projects, sometimes dynamic sql is not acceptable. The DBA-people or the security policy forbids it. If I understand correctly, Hibernate 3.0 does offer the use of stored procedures, but it might take some time before official support arrives.
Still, dynamic sql for selects might be allowed. In that case nHibernate might still be an option.
In fact, I do believe that for the acceptance of nHibernate on bigger projects, the use of stored procs of updating, deleting and inserting is of the utmost importance.
I have read quite a few questions about this issue.
Here
http://nhibernate.sourceforge.net/forum/viewtopic.php?t=322&start=0&postdays=0&postorder=asc&highlight=stored+procedure member 'mnowosad' posts that he has succesfully implemented calls to stored procedures. I have followed the route he describes and can also report succes.
If there are people that need the code, I'd be happy to share. It's not clean code and I have hardcoded the OracleParameters-type. Also, I'm not quite sure if I used the most optimal route. However, feel free to email me at: r.boeke [at] sitechno . com if you are interested in the solution.
Also, if you yourself have code that accomplishes the same, I'd be very interested in it. :P