| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[2]: to_number question
response inline, below:
Dan Tow
650-858-1557
www.singingsql.com
Quoting Wolfgang Breitling <breitliw_at_centrexcc.com>:
> Quoting Jonathan Gennick <jonathan_at_gennick.com>:
>
> > Astounding. It's also astounding I've never run into this
> > issue before. It's not like I never write subqueries.
> >
> It should not be too astounding. If you design your system properly and use
> the
> correct column types for the data ( number types for numbers, date types for
> dates ) then you do not run into this issue.
>
> Which is also why I am not in favour of Dan (Tow)'s suggestion that the
> database engine work overtime to work around those cases of poor design.
> There
> are other, better ways of dealing with them. You need to be aware of the
> issues
> and the solutions, however.
Hey, I'm all for good design, but finding bad design is not an excuse for database functional (not just performance) behavior that changes without warning just because the database decides to start taking a different path to the data, nor errors that only apply to rows the database shouldn't be returning, anyway. Yes, it *is* true (as in this case) that *some* of the otherwise-useless errors that result with the current Oracle behavior point to bad database design, and it's not altogether useless to have something point out that bad design. However, not *all* errors that result from failing to check the other predicates before deciding it's *really* an error point to database design errors. Even if they all did, *database design errors happen in the real world, and we can't always make them go away even when they are pointed out*. From a practical perspective, how often do you suppose the random person seeing an error like this is going to *know* it points to a database design error, and is going to do all that it takes to rectify that design error?!
BTW, just to show my sincerity about abhorring database design errors, I *do* think that SQL should have an optional mode (alter session set ..., or an init.ora parameter for the whole database) where implicit type conversions (e.g., a.CharTypeCol=b.NumberTypeCol) are simply not allowed, resulting in errors at *parse* time - this, used during development, would catch a great many design column-type errors very early in the development stage, *before* it is too late, rather than waiting for millions of rows of production data to finally result in a row that fails the conversion, when it is generally super-expensive at best to fix the error at the design level.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jul 15 2004 - 14:48:04 CDT
 |
 |