Re: ANSI SQL-92 --> Is it practical??

In article <390DEA00.BA7DEB77_at_0800-einwahl.de>,   Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
> If you really insist on just using ANY standard you are trading
 migration time
> against execution and development time.
>
> So ask yourself: How long may any migration take? How important is
 migration
> time compared to execution and development time?
>
> This should give you a fair decision.
>
> Martin
>
> coakleyj_at_hotmail.com wrote:
> >
> > Hi Everyone.
> >
> > Most RDBMS's support ANSI SQL92.
> > For example, Oracle does support ANSI SQL92, and allows you to set a
> > FIPS flagger to prevent you from using any Oracle specific
 extensions.
> >
> > Sounds great - but is it really?
> >
> > For example, I am currently working on a project where this
 portability
> > is crucial. So, take for example a pretty simple searching
 requirement
> > - the user enters the customer name [ first name and lastname] in a
> > single name field, and hits the Find" button. Typically, we may
 want to
> > run a criteria such as :
> >
> > select * from customer
> > where upper(firstname || lastname) = replace(Upper('sEarCh
 Ctieria'),
> > ' ', '');
> >
> > i.e. I'm removing spaces from the user input, and converting
 everything
> > to uppercase. Ignore wild cards.
> > Works fine, and Oracle8i will allow functional indexing so that I
 can
> > safely get performance even though I am using functions.
> >
> > But, if you turn on FIPs flagger - this is the error:
> >
> > ORA-00097: use of Oracle SQL feature not in SQL92 Entry Level
> > PLS-01452: This function is not part of the ANSI standard
> >
> > So my questions:
> >
> > In this very simple case, how could I achieve the same results using
> > one or multiple statements that satisfy SQL92?
> >
> > Would I have to change my design to faciliate this?
> >
> > Has anyone developed a full commercial system that actually complied
> > with SQL-92?
> >
> > If so, what environment and what were the issues?
> >
> > Did it increase the development effort substantially?
> >
> > Any good articles on the practicalities of doing this? web sites?
 etc
> >
> > I look forward to your responses.
> >
> > Thanks
> >
> > Coakleyj
> >

Oracle only supports entry level ANSI-92 and most other rdbms vendors also only offer limited support for the 92 standard. I won't start on what I think of the inner join syntax.

You would be better off to make every SQL statement a routine. For each database port you would provide a complete set of routines that contain the appropriate SQL for the selected rdbms. You want to be able to use the tuning tools, like hints in Oracle, that each vendor provides. Indeed for an Oracle application I suggest that every SQL statement be hinted to run the way you want it to run and to prevent bad choices by the CBO. Either that or you provide the user the ability to compile hints into the application because with Oracle it is very possible for differences in init.ora parameters multi_block_read_count, hash_area_size, and sort_area_size to make a difference in how the CBO solves a query for two identically sized, designed, and populated databases. The queries need to be locked in or the site DBA needs a way to ajust the SQL as needed.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.