Re: Peculiar results from a simple looking query.

Mark Bole <makbo_at_pacbell.net> wrote in message
> If you are using Oracle Migration Workbench then there is a documented
> workaround which your Oracle sales rep should be more than happy to provide.
>
> If SUBSTR() is an acceptable standard (ANSI or otherwise), then just
> export all your source to flat files and then it wouldn't be too hard to
> use Perl to globally change every occurence of CAST( ... as varchar...)
> to an equivalent SUBSTR.
>
> I suspect this bug has gone unaddressed for so long because most people
> using Oracle would never even think to use CAST to convert one char type
> to another where only the length was different.

Thanks for the reply... later messages on this thread seem to diverge from my concerns. I'm pleased to see that there appears to be a general consensus that this is a bug. In the absence of a post saying "works in version X" - I assume purchasing a particular version of Oracle will prove no benefit to this specific problem.

It now seems I had foolishly assumed that Oracle would trivially meet my ANSI '92 compliance expectations. Obviously, the example I posted is contrived (in order to clearly demonstrate the behaviour I'm questioning in a short Usenet post) - and I agree that there are many simple workarounds for this specific example. I was shocked because, while Oracle is seen as the leading SQL DBMS supplier, either I or Oracle misunderstood how the ANSI standardised CAST operator is intended to behave... and because no-one so far has pointed out any misunderstanding on my part.

I would still welcome any pointer to official "Oracle" documentation which states that the behaviour I?ve experienced from the Oracle DBMS is expected... or, indeed, any references which suggest that my own understanding of ANSI CAST is flawed.

Steve Received on Mon Feb 28 2005 - 04:48:27 CST