Can anyone pls answer the following questions:
Is Hibernate useful for stored oracle procedures and functions?
Also, how will ORM come into picture if it is so?

Please be more specifc. If you want to call stored procedures from Hibernate, you can do so by accessing JDBC connection from the session object using Session.connection().

I was trying to evaluate Hibernate against stored procedures/functions. All my J2EE applications use calls to Oracle stored procedure/functions. In such a scenario, how should I use Hibernate to obtain the beauty of ORM framework?
If an oracle function returns a resultset to me, then can i have it in objects?
Also, if you see this article
http://www.hibernate.org/187.html
some nice things were said in the plan. So, i would like to know if they were implemented and a link to any document where i can use the functions in hbm mappings.

This is all done in Hibernate3 (read: not available in Hibernate2). Check out the tests for this functionality (in the test/org/hibernate/test/sql dist dir). The tests simply use native sql, but using stored procedures is as simple as specifying the callable string and setting the callable attribute to true.