number or varchar

[eastking]

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.

[mber]

It is really depends, but for sure you should use quote for the 000000001 --> '000000001'.

And the error means:
ORA-01722: invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Without detail, it is really hard to say

Take care

[eastking]

thanks for reply.

But in our application , there are all of them . Because in have no problem in our develop machine. but it failed in production environment.

I do not want to modify source, they are too many.

so, is there a good way ?

thanks a lot.

[chrisrlong]

The problem is in the data.

Since you do not have quotes around the constant (00000001), Oracle sees it as a number. Obviously the column is a string. Because Oracle is stupid, it tries to convert the column to a number (thereby nullifyin any index) as opposed to converting the constant to a string.

So....

In development, all the values in that column convert over to numbers fine.
In production, there must be a record that contains a non-numeric character in that field. When the compiler gets to that records, the statement breaks.

So what are your short-term options?

Fix the query: Put the quotes around the constant like they should be. Besides fixing your error, it will also use an index, if one exists, and your query will run much faster.

Fix the data: Either verify the values manually or:
- Take your statement and add an ORDER BY <primary key>
- Put it in a cursor
- do a DBMS_OUTPUT.PUT_LINE on the PK of the row inside the cursor.
- Eventually, the cursor will hit the bad row and error out
- Look at the output generated - your bad row will be very close (sequentially speaking) to the last PK in the output.


Mind you, there may be more than one bad row.

Also, you may want to determine how that value got there. Does your application allow bad user input? Is there a bug in the code? Is this converted legacy data? This will help to tell you if it can happen again. The long-term solution:

- Fix the bad data
- Plug the hole (how the data got there)
- Fix the qery

Hope this helps,

- Chris