Re: number and varchar

In article <919dj1$qvj$1_at_nnrp1.deja.com>,   eastking_at_my-deja.com wrote:
> hello, every one here.
>
> I have two schema in my database Oracle8.0.6/solaris.
>
> I run following sql in both of schemas.
>
> desc t_order
>
> orderCd varchar(10);
> ...
>
> select 1 from t_order where orderCd = 000000001;
>
> in first schema , it returned
>
> no record fetched.
>
> in another schema, it returned
>
> ORA-01722 ....
>
> Who can explain why it happend and how can I solute this problem.
>
> Thanks a lot.
>

Since the table column is of type varchar2 you should be inclosing your value in single quotes, '0000000001'. Failure to do so will require Oracle to convert either the table column from varchar2 to type number or to convert the numeric constant to character. My guess is that Oracle is converting the table value and in the one instance you have at least one row where the contents can not be converted to a valid numberic. An comparision of the explain plan for the query from each instance may well show you what is happening. Odds are also that you are full table scanning because of the conversion.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/