Re: Mysteriously Inconsistent Behaviour of ORACLE 7.3.3

I have encountered this about 2 years ago when my client switched to one of version 7 database (7.1.3 ?).

After experimentation, I figured out that ORACLE somehow liked to convert things to number instead of to character if one of the value to be compared is a number. So, my guess is: ORACLE was converting your "AField" to number. Since the column contained alpha characters, it failed!

Since my experimentation was 2 years ago, what you encountered might be an entirely new "feature". Please let me know if I am right.

Good luck.

Kim Ng
(kimmng_at_eskimo.com)

Graham Miller (lgmiller_at_elmrd.u-net.com) wrote:
: Hello,
: Have a close look at the data in that column. It may be that a
: letter has been entered ( an 'I' ( capital i) or 'l' (ell - lowercase
: L ) instead of a '1' (one) ). Or there may be a leading space
: character (this shouldn't bother it).
 

: Remember that Oracle does automatic type conversion (*spit* - i know
: it is more friendly!) and has to decide which one to convert.
 

: Other than that, i have no idea.
 

: graham (aka grumpy)
 

: Nnoor_at_cris.com (NNOOR) wrote...
 

: |
: | I experienced following in Oracale 7.3.3 WGS NT which I don't have
: | any explanation for.
: |
: | I have a view defined on ATable as following...
: |
: | Select...[snip]
: | Where....
: | ..... AField = 1
: |
: | NOTE: AField used to be of type Number when this view was written.
: | Field type was changed to be VARCHAR2(2) some time ago. But this
: | view kep working fine. Yesterday, all of a sudden, I started to get
: | an Oracle error message "Invalid Number" during a process which is
: | run hundreds of times a day and uses the above view every single time.
: | Once this error message showed up, it was persistent in appearing every
: | single time. After being totally clueless for some time, I tried the
: | following change in the query:
: | .... AField = '1' (i.e. enclose 1 in quotes)
: |
: | It made the problem go away. I am toally baffled. Why would Oracle
: | suddenly decide to cough up on an issue which it simply ignored for
: | quite a long while?
: |
: | (No, the data in the ATable had not changed in days).
: |
: | Any clues?
: |
: | Regards,
: | Nasir (nnoor_at_cris.com)
 

: Graham Miller ...
: Opinions expressed are mine, they are free, and worth exactly what they cost.
  Received on Sun Jun 08 1997 - 00:00:00 CDT