首页 > Linux操作系统 > Linux操作系统 > ORA-01722 Invalid Number

ORA-01722 Invalid Number

原创 Linux操作系统 作者:susie0409 时间:2011-01-31 12:57:39 0 删除 编辑

用户反映系统中的一个克制的报表有问题,只出来了部分数据。检查SQL是没有问题的。但是察看log有:ORA-01722 Invalid Number

在重新跑SQL发现加了参数,SQL是没有问题的,去掉特定的参数就会出现那样的错误。网上查了查:( Invalid Number
What causes this error?

This problem occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.
There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement! Here are some examples:
SQL> select to_number('3434,3333.000') from dual;
ORA-01722: invalid number
no rows selected
The above statement throws the error message, because it has found a character, in this case, a comma and the default format for TO_NUMBER does not contain a comma.
The same error can occur when you use arithmetic functions on strings.
SQL> select 'abc' - 124 from dual;
ORA-01722: invalid number
no rows selected
The error can occur when you add dates with string values.
SQL> select '01-JUN-01' - 'abc' from dual;
ORA-01722: invalid number
no rows selected
·  Back to top of file


How to fix it
The fix depends upon the exact expression which caused the problem. The following guide lists the possible SQL expressions which can give this error, with their most likely cause. When addressing this error, keep in mind that it can indicate a simple keystroke problem with the query, or a deeper problem with the query logic, or even the presence of bad data in the database itself.
You are doing an INSERT INTO ... VALUES (...)
One of the data items you are trying to insert is an invalid number. Locate and correct it.
If all of the numbers appear to be valid, then you probably have your columns out of order, and an item in the VALUES clause is being inserted into a NUMBER column instead of the expected VARCHAR2 column. This can happen when a table has columns added or removed.
You are doing an INSERT or UPDATE, with a sub query supplying the values.
Obviously, the preceding considerations apply here as well. What makes this more complicated is that the offending character string is hidden as a row in a table. The fix is to identify the row (or rows) which has the non-numeric string, and either change the data (if it is in error) or add something to the sub query to avoid selecting it. The problem is in identifying the exact row.
Assuming that the errant datum is an alphabetic character, one can use the following query:
where col is the column with the bad data.
You are doing a SELECT, rather than an INSERT or UPDATE.
In this case, there is probably an implicit conversion happening between some predicate in the WHERE clause. Check for a numeric column being compared to a character column.
If you are using the to_number function, make sure the format mask fits all possible character strings in the table.
If you know that a column contains both valid numbers and character strings, make sure that all rows which do not contain valid numbers are being excluded in the WHERE clause.
Other Rare Situations
To expand on the previous comment, if you have a column in a table which contains both valid numbers and character strings, it is just barely possible to get an ORA-01722 even if no character strings are being returned by your query. Example: two tables must be joined. In table A, the column is VARCHAR2, and in table B it is NUMBER. Table A also has non-numeric data in that column in some rows, and has a type column to make it obvious which rows are which. It is possible for the optimizer to choose an access plan in which the join is attempted before the filtering, which will cause the ORA-01772. The fix is to add a hint which changes the plan enough to bypass the rows causing the error.
Doing an explicit conversion can sometimes make things worse. For example, '+17', '-17', & ' 17' all convert successfully implicitly. The last one will raise the error if the 'S99' mask is used in the to_number function.
A field containing only spaces will raise this error. One fix is to replace the spaces with nulls or zeroes.
If you are querying a view rather than a table, any of the above could apply, and be hidden from sight. The fix is to add a predicate to the WHERE clause which excludes the troublesome rows.

                                     FROM apps.WIP_OPERATIONS WOP,
                                          apps.WIP_REQUIREMENT_OPERATIONS WRO,
                                          apps.WSH_OSP_ALL OSP
                                     WHERE 1=1
                                     AND WOP.WIP_ENTITY_ID=OSP.WIP_ENTITY_ID
                                     AND (WOP.OPERATION_SEQ_NUM=OSP.OPERATION_SEQ OR
                                     AND OSP.CONFIRM_SHIP_REQ='Y'
                                     AND OSP.ORGANIZATION_ID=WOP.ORGANIZATION_ID
                                     AND WRO.WIP_ENTITY_ID=WOP.WIP_ENTITY_ID
                                     AND WRO.ORGANIZATION_ID=WOP.ORGANIZATION_ID
                                    AND  WRO.OPERATION_SEQ_NUM=WOP.OPERATION_SEQ_NUM 


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: 恋恋风尘
下一篇: 飘着找爱
请登录后发表评论 登录


  • 博文量
  • 访问量