Re: PL/SQL ref cursors vs. plain SQL

At the moment I don't heard any real disadvantage of doing the job with PL/SQL procedures and ref cursors.
The pro is that all the SQL statements can be centralized and the client code is not cluttered with lengthy sql queries. On the contrary you're for example not able to detect mismatches between the Java code and the SQL queries during compile time, but that's not very important and we can detect such mismatches with unit tests.

2015-09-16 20:25 GMT+02:00 Mark W. Farnham <mwf_at_rsiz.com>:
> 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.

Why do you think it's the least efficient? Besides that a little bit more data has to be transmitted to send the whole sql text from the client to the database server,
the rest of the execution/fetch phase should be more or less the same.

> 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.

There's only one application server which executes the queries on the database and the connection between these two servers can be considered safe.
This would also allow easy deployment of "repaired" sql queries, even if they are included in the Java code.

2015-09-16 20:44 GMT+02:00 raza siddiqui <raza.siddiqui_at_oracle.com>:
> It would also be useful to know (to assess solution feasibilty), if the
> queries are being run against very dynamic OLTP-type data or against fairly
> static data (DWH), and hence how much data is being scanned and how much is
> expected to be returned.

The data is more or less static during the time the users are expected to access the database. The physical schema design and the queries are well optimized so
far and we are not accessing much more data than really returned to the user. We return between 20 and 100 rows per query. In what way does this influence
the decision between the usage of plain SQL and PL/SQL with ref cursors?

Thanks & Regards
Thomas Aregger

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