SQL generation vs. Oracle stored procedures

"stevewaltz" asked:
We are attempting to adopt programming standards that would mandate the use of stored procedures in our Oracle 8i and 9i databases for data access. Application developers prefer to take advantage of the SQL generation capabilities of IBM's WebSphere and Compuware's Uniface. DBAs feel the security, performance, maintainability, visibility and sharability of procedures stored in the databases outweigh the ease of development with generated SQL. Any related experiences and advice would be appreciated.

"GEESTER" responded:
This is a classic developmental question, and any contribution is always just a small view of the ongoing debate; external factors often play a larger part. As a general rule, it is true that the closer you are to the database, the easier it is to enforce business rules. It is easier to have a constraint go unviolated if it is defined at the table level rather than at the application level. Stored procedures also afford you centralized security. There are lost of resources you can look up that describe this approach. I think it really boils down to answering a few questions:

1. Do you have the DBA resources do carry out this work now, and will those resources be there once the project goes live? My guess is you'll have a production DBA but not a development one, whilst you'll always need the J2EE developer. You also be tied to Oracle -- not a bad choice, but tied nonetheless.

2. Do your tools provide adequate and well-optimized code? If WebSphere generates good SQL and doesn't parse 100 times per page, you can use it, but I've found that lots of tuning time is necessary to remove issues that stem from having 100 SQL for 100 lines of display.

3. Are your developers as good at writing SQL as your DBAs are? Some are; some aren't. Experienced RDBSM developers are often better than DBAs, but again this goes back to the resource ratio you have and how many developers versus DBAs you have. It's pointless putting loads of logic in the DB if you have to teach PLSQL to 20 J2EE staff.

"FERENCMANTFELD" responded:
I concur 100% with the previous reply.

To add, PL/SQL is much more powerful than anything WebSphere or CA tools can churn out.

Yes, adopt a standard, but don't get fanatical about it; there are times to make valid exceptions to the rule.

Questions to ask:

1. Does WebSphere have the ability to use the database-specific functionality? Oracle has Analytic functions, implementation of TRANSLATE or subquery factoring using WITH clause, Ref Cursors, custom types, DBMS_% packages. The short answer is: in IBM's dreams!

2. What will you do when you need some specific functionality to do something special in Oracle? An insurance company client of mine wanted a function that accepted an adviser's ID, and the function was to return the last appointment for the current month, with date and type, and the first appointment for the next month. This would have involved very complex SQL, but with PL/SQL, the function I wrote executed in 10 milliseconds, and it took me 10 minutes to write and test the function. My other option was a SQL-centric approach, which may have been rather expensive and involved many joins and possible performance concerns; I would have also had to worry about the skills of the production support staff to maintain the complex SQL once I was offsite.

3. Can WebSphere generate the SQL required for hierarchical queries? For instance, consider CONNECT BY PRIOR, START WITH, ORDER SIBLINGS BY, NOCYCLE (10g). I'd offer the same answer.

4. Can WebSphere/CA generate Oracle hints when hints are to be used? Perhaps, but probably not.

• Read all the answers to this question.
• Not on ITKnowledge Exchange yet? Register today.