"Best Practices" for Application SQL coding

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Thu, 30 Oct 2008 09:11:57 -0500
Message-ID: <7b8774110810300711x5fb91ef7x6ef5844571421b11@mail.gmail.com>


Good day, list,

We have had several sql issues come up that I am sure a pure Application DBA could tackle readily. In general, what are the recognized "best ways" to code a part of an application that has a dynamic number of predicates? For example, a Form that allows users to choose different search criterias for an Employee search. Having SQL that attempts to generically allow for all parameters can be quite ungainly, especially since Oracle attempts to find a common explain plan that will work for all cases. However on the flip side, trying to code the application/Form to produce an individual sql statement for each case can also be unweildly; the number of permutations would quickly present a formidable obstacle. Where is the sweet spot between these two extremes? I lean towards the latter approach, but the developers obviously want the former. So instead of re-inventing the wheel, what have others found?

For the record, we are using an ERP from SunGardHE (Banner), but this question also has surfaced with our in-house developement group.

--

Charles Schultz

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 30 2008 - 09:11:57 CDT

Original text of this message