Problem solve Get help with specific problems with your technologies, process and projects.

SQL generation vs. Oracle stored procedures

An ITKnowledge Exchange member had a question about which option to choose: SQL generation or stored procedures. Fellow Oracle techies helped Steve.

SQL generation vs. Oracle stored procedures An ITKnowledge Exchange member had a question about which option to...

choose: SQL generation or stored procedures. Fellow Oracle techies jumped in on the conversation and helped Steve. Here is a portion of the conversation. Read the rest of the thread.

"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.
The nicest approach I've come across is an instance in which all the SQL stored in XML files. That way both developers and DBAs can read them easily and spot issues quickly. I would then leave the stored procedure aspect to areas where large amounts of data are worked on and network traffic would be harmful. The RDBMS resources can then be used for constraints, indexes, tuning and monitoring SQL, efficient dataloads and that is usually enough to keep them busy. The important thing is that your database is designed well enough so that regardless of the datasource, data remains clean and as close to the business rules as possible.

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.
When you want a generic SQL generator (like Siebel, PeopleSoft and SAP do), you are dumbing down the database to the lowest common denominator, which means you are really using the database as a glorified dumb storage bin. The database is there to do powerful and meaningful processing, USE ITs!

This was last published in February 2005

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.