ORA-01722 Invalid Number
来源:互联网 发布:软件属于什么资产 编辑:程序博客网 时间:2024/05/18 01:33
ORA-01722 Invalid NumberTopics21-June-2001Author: Phil Singer |
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;ERROR: 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;ERROR: 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;ERROR: ORA-01722: invalid number no rows selected How to fix itThe 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. Assuming that the errant datum is an alphabetic character, one can use the following query: SELECT ... WHERE UPPER(col) != LOWER(col) where col is the column with the bad data. You are doing a SELECT, rather than an INSERT or UPDATE. 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 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. About the AuthorThis FAQ is based on material posted on the orafaq.net message boards edited by Phil SingerDid this help you solve the problem?Did this help you solve the problem? Please let us know if we need to expand or change this FAQ. Just email us you comment to Kevin |
- ORA-01722 Invalid Number
- ORA-01722 Invalid Number
- ORA-01722: invalid number
- ORA-01722 invalid number
- ora-01722:invalid number
- ORA-01722: invalid number
- ORA-01722:invalid number
- ORA-01722: invalid number
- Oracle-ORA-01722 invalid number错误
- ORA-01722: invalid number" During Upgrade/Downgrade
- Oracle-ORA-01722 invalid number 错误
- ORA-01722 invalid number错误原理解
- Oracle-ORA-01722 invalid number错误
- Oracle-ORA-01722 invalid number错误
- ORA-01722 invalid number错误
- ORA-01722: invalid number 一例
- java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
- ORA-01465: invalid hex number
- 关于时间管理的一个哲理
- NDoc使用简要手册
- Sybase PowerDesigher 的文档
- Windows 操作系统进程描述(收藏)
- 多层结构中必不可少件TClientDataSet的全面剖析
- ORA-01722 Invalid Number
- (原创)二进制文件操作类
- “JAVA编程思想” 课程进展 一
- Begin to sell
- 使用system.web.mail.SMTPmail发送邮件,处理smtp验证问题
- 密码学领域重大发现:山东大学王小云教授成功破解MD5
- 今天激活了自己的blog,可以写点自己的东西了。
- 自动产生存储过程的sql 脚本
- 随机调用记录[SQL Server]