Re: different implicit casting behaviour

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0407120601.787d5138_at_posting.google.com>...
> rooty_hill2002_at_yahoo.com.au (Gary) wrote in message news:<171bd226.0407112252.6d16b42_at_posting.google.com>...
> > All,
> >
> > This was from one of my programmers. She claimed one of her statements
> > was working in system testing region, but after being promoted to user
> > acceptance testing region it stopped working, so it became mu problem.
> >
> > She uses the following statement to simply retrieve one field
> > "cat_type_name" from table "loan_product":
> >
> > SELECT cat_type_name FROM loan_product
> > WHERE acct_type = 1028
> > AND int_cat = 003
> >
> > Unfortunately, both acct_type and int_cat are defined as CHAR(4) and
> > NOT NULL. So apparently, this statement should not work at all.
> > However, in system testing region it DID work. I rejected to look any
> > further unless she change the statement to:
> > SELECT cat_type_name FROM loan_product
> > WHERE acct_type = '1028'
> > AND int_cat ='0003'
> >
> > She did and her application is working happily in user testing region
> > now. However, I still can't answer the question why it worked in
> > system testing region but doesn't work in user acceptance region. I
> > compared the table in two regions, couldn't find any difference. Looks
> > like the implicit casting behaves differently in these two databases.
> >
> > Any idea about what I should check to find out the reason.
> >
> > Sorry guys, I am a newbie.
> >
> > Cheers,
> >
> > Gary
>
> Gary, the answer to why the statement worked in one test environment
> and did not work in the other is most like strongly tied to the Oracle
> ERROR message returned by Oracle, which you did not post. I suspect
> the answer is due to the difference in data and/or statistics on the
> tables and that a numeric or value error was being returned because of
> the direction of the implicit conversion from character to number that
> would have resulted from the missing sinle quotes around the character
> fields. The most likely answer based on the data given is "bad code":
> never compare data of different type without explicitly performing a
> data conversion via to_char, to_number, to_date, etc....
>
> IMHO -- Mark D Powell --

Mark, thanks for your reply.

Of course if the statement was coded in more standard way, as you mentioned, it will work happily in both regions. However, this bad coding actually brought me this interesting topic: Why it worked in one region but doesn't work in another. In two regions, the databases are identical and are running in different instance names. I believe there must be some difference there that I don't know of. I am keen to find it out so I can learn from it.

In the user acceptance region, in which the statement doesn't work, I got the error message ORA-01722: Invalid Number. Sounds like when Oracle does the implicit casting, it either can't compare to_number(int_cat) with 003, or can't compare int_cat with to_char(003). Why? Mark, I suspect there might be some data related reason for this. As you mentioned, it might be due to the difference in data and/or statistics on the tables. But how to find it out then?, i.e., what kind of difference in data or statistics can cause this difference in implicit casting?

I will try to find some data difference. I will keep you posted.

Cheers,

Gary Received on Mon Jul 12 2004 - 18:43:35 CDT