RE: PL/SQL ref cursors vs. plain SQL

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <thomas.aregger@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 16 Sep 2015 14:25:38 -0400

Without a clear specification of your requirements pointing you at good
candidate solutions is difficult, but I *suspect* you may have chosen a false
dichotomy.

Of the two solution spaces you have stated you are considering, shipping SQL
query text from a Java client to a database engine is probably the least
secure, chattiest, and least efficient mechanism I can think of, so I'm almost
certain you would do better with PL/SQL packages.

If nothing else, defining the queries in a centralized location will reduce the
churn rate to "n" clients when you need to update or repair functionality.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Thomas Aregger
Sent: Wednesday, September 16, 2015 2:08 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: PL/SQL ref cursors vs. plain SQL

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


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


Other related posts: