RE: PL/SQL ref cursors vs. plain SQL

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 16 Sep 2015 14:25:38 -0400
Message-ID: <04c701d0f0ad$17afa5a0$470ef0e0$_at_rsiz.com>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Aregger Sent: Wednesday, September 16, 2015 2:08 PM To: oracle-l_at_freelists.org
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.

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


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 16 2015 - 20:25:38 CEST

Original text of this message