SQL generated by 4GLs vs. Oracle stored procedures

Information risk management
We are attempting to adopt programming standards which 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 WenSphere 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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 to the database the more easy 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 procs also afford you centralized security, there are lost of resource you can look up which 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 that resource be there once the project goes live ?
My guess is you’ll have a Prod DBA but not a dev one, whilst you’ll always need the J2EE developer. You also be tied to Oracle, not a bad choice but tied nonetheless

2 – Does your tools provide adequate and well optimized code?
If WebSphere generates good SQL and doesn’t parse 100 times per page then you can use that but I’ve found that lots of tuning time is taken removing , 100 SQL for 100 lines of display, type issues.

3- Are your developers as good at writting SQL as your DBAs?
Some are some aren’t, experienced RDBSM developers are often better than DBAs, 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 sees 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 proc aspect to areas where large amounts of data are worked on and network traffic would be harmfull. 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.

That’s my 2 cents.


Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • FerencMantfeld
    concur 100% with reply from geester. To add, PL/SQL is much more pwoerful 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: Does Websphere have the ability to use the database specific functionality (for instance Oracle Analytic functions, or Oracle's implementation of TRANSLATE or subquery factoring using WITH clause, using Ref Cursors, custom types, DBMS_% packages, etc.) ? Short answer: in IBM's dreams ! What about 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, whereas a SQL centric approach may have been rather expensive, involving many joins and possibole performance concerns and having to worry about the skills of production support staff to maintain the complex SQL once I was off site). Can WebSpehere generate the SQL required for hierarchical queries (CONNECT BY PRIOR, START WITH, ORDER SIBLINGS BY, NOCYCLE (10g) ?) Same answer again. 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, PSOFT 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 IT ! Getting off my soapbox now. Hope that helped: Regards: Ferenc
    0 pointsBadges:
  • Welcome
    Hi to all, I answer just for giving 100 points to Ferenc's answer. I totally agree with it, in every line and every word ! In my opinion, the so-called "database independent" development leads unevitably to very bad results, at least in what concerns performance issues. The "most-common denominator" used for making an application database-independent takes it down to the lowest level, lower than any specific database's possibilities. Databases concure to offer better and better results and facilities, if they all were the same then a single one of them would suffice for everybody. It's a pity not to use Oracle's wonderful proprietary additions, in comparison to other databases. The standard code-generating tools usually do not create performant code. At least from my experience, it's strange to imagine an organization where the DBA-s SQL knowledge to be wider than the developers' ! Usually I think rather the opposite is true in most organizations. And this is somewhat natural, considering that the DBA performs all of the maintenance work, while the developers are developing both projects and their own development skills as well. Every organization should be supposed to have at least a small group of developers at the highest possible level of knowledge, even if the DBA team is also at a high level. I think no code-generating tool could ever compete a consciuous development done directly by the development team, based on both the database capabilities and the business requirements. My best regards, Iudith Mentzel
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: