PL/SQL ref cursors vs. plain SQL

  • From: Thomas Aregger <thomas.aregger@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 16 Sep 2015 20:08:11 +0200

Hi all

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.
--
//www.freelists.org/webpage/oracle-l


Other related posts: