PL/SQL ref cursors vs. plain SQL

We are currently building a Java application with some complex queries which we wanted to encapsulate in views to keep the client code for accessing the data as simple as possible. Unfortunately the queries do some aggregation/grouping and the view projection does not contain the columns we need to use as filter predicates from outside the view. Therefore an implementation with views is not possible.

The question is now if we should put our SQL queries directly in the Java code or if we should write PL/SQL packages and use ref cursors to access the data.

The queries run between 10 and 150ms and are executed in a concurrent fashion (potentially highly concurrent in the future)

Regarding the use of ref cursors, what are your experiences and opinions on the following topics:

• Performance/Scalability [1]
• Usability in Java [2]
• Others?

Thanks and Regards

Thomas Aregger

[1] I did a small test where I executed a single query about 100 times (serially) as plain SQL and 100 times as a PL/SQL package with a ref cursor. The average runtime of the PL/SQL package was 5ms longer compared to plain SQL (165 ms instead of 160 ms, which is acceptable). [2] I found it a bit cumbersome that I had to set the fetch size on the ResultSet object instead of the callable statement.

--
http://www.freelists.org/webpage/oracle-l