to_number question 2004-07-15 - By Dan Tow
response inline, below:
Dan Tow
650-858-1557
www.singingsql.com
Quoting Wolfgang Breitling <breitliw@(protected) >:
> Quoting Jonathan Gennick <jonathan@(protected) >:
>
> > 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.
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
