Error At Line 1 Ora-01722 Invalid Number
Contents |
UPDATE 3.3 Other Rare Situations What causes this error?[edit] An ORA-01722 ("invalid number") error 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 ora 01722 invalid number oracle contain the digits '0' through '9', with possibly one decimal point, a sign (+ or -) ora 01722 invalid number informatica 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 ora-01722 invalid number select 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 01722. 00000 - "invalid number" possible for this error to appear when there are no numeric columns appearing explicitly in the statement! Examples[edit] 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;
Ora-01722 Invalid Number Solution
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 it[edit] 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. When doing an INSERT INTO ... VALUES (...)[edit] 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
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-01722:Ora-01722 Invalid Number To_char
invalid number tips Oracle Error Tips by Burleson Consulting Oracle docs offer this information ora 01722 invalid number oracle decode regarding Oracle ORA-01722: ORA-01722 invalid number Cause: The attempted conversion of a character string to a number failed because the character convert string to number in oracle 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 http://www.orafaq.com/wiki/ORA-01722 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. Jonathan Gennick provides information regarding Oracle ORA-01722 in conjunction with subqueries and Oracle Optimizer. To exhibit how Oracle ORA-01722 is often thrown, this query is given as an example: SELECT * FROM ( SELECT FLAG, TO_NUMBER ( NUM ) NUM FROM SUBTEST WHERE FLAG = 'N' ) WHERE NUM http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm > 0 ; Here, from in the FROM clause of a query, the user is attempting to have a subquery of the original query, which is causing Oracle ORA-01722 to be thrown. The Oracle ORA-01722 error is thrown with the failure because of the outer query. This is because it is trying to test the NUM > 0 condition first because it is assumed it might be more useful. Gennick goes on to show that Oracle ORA-01722 is thrown because the Oracle optimizer has re-written the query as: SELECT FLAG, TO_NUMBER ( NUM ) NUM FROM SUBTEST WHERE TO_NUMBER ( NUM ) > 0 AND FLAG = 'N' ; This throws Oracle ORA-01722 because the re-written query causes the system to convert a non-numeric NUM value of the WHERE clause into numbers. OraFaq also has notes on Oracle ORA-01722. Here, it is explained that Oracle ORA-01722 is thrown because a particular string was not able o be converted into a specific valid number when a user attempted to convert a character string. There are several possible resolutions to Oracle ORA-01722 in this context: If you are attempting an " INSERT INTO ... VALUES (...) " you need to find out which data item is invalid If you are trying to supply the values in a sub query which is intended to INSERT or UPDATE, you have to dig a
17, 2012 - 9:21 am UTC Category: – Version: Latest Followup You Asked What is the error ORA-01722 and we said... ORA-1722 is Invalid number. We've attempted to either explicity or implicity convert a character string to a number and it is failing. This can happen for a number https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45012348053 of reasons. It generally happens in SQL only (during a query) not in plsql (plsql throws a different exception for this error). You can see this error easily by: ops$tkyte@8i> select to_number('abc') from dual; select to_number('abc') from dual * ERROR at line 1: ORA-01722: invalid number This error seems to creep into queries in the strangest ways. A change in the order of a predicate can make it come and go -- depending on the order of evaluation in the predicate. Consider this example: invalid number ops$tkyte@8i> create table t ( x int, y varchar2(25) ); Table created. ops$tkyte@8i> ops$tkyte@8i> insert into t values ( 1, 'abc' ); 1 row created. ops$tkyte@8i> insert into t values ( 2, '123' ); 1 row created. ops$tkyte@8i> ops$tkyte@8i> ops$tkyte@8i> select * from t where y > 100 and x = 2; X Y ---------- ------------------------- 2 123 ops$tkyte@8i> select * from t where x = 2 and y > 100; select * from t where x = 2 and y > 100 * ERROR 01722 invalid number at line 1: ORA-01722: invalid number The first query worked since we *tend* to evaluate queries from the bottom up. We evaluated the x=2 part first and never tried to do 'abc' > 100. In the second query, the y>100 was evaluated first. Y was promoted to a number and then compared to 100. 'abc' could not be converted so ORA-1722. The only general purpose solution is to always compare like types to like types. You should either convert the column Y entirely to numbers (clean the data) or use a character string comparision (which changes the meaning of the predicate -- y > 100 is very different from y > '100' ) Reviews Write a Review ORa-01722 March 27, 2001 - 2:30 pm UTC Reviewer: Tom Petrella from Melville, NY I was getting this error and it was driving me nuts because I know everything was syntactically correct and there were no invalid numbers. Be rearranging the order of the where clause i got it to work. Thanks ! Is there a surefire way to avoid this? July 11, 2002 - 10:35 am UTC Reviewer: Adrian from Exeter England Apart from the obvious method, (i.e. Always compare like data-types), is there a sure-fire way to avoid this sort of problem happening? If I have a domain table cg_ref_codes with fields domain, low_value, high_value, abbreviation, meaning (all varchar2). If further some of my domains contain purely numeric values. Is there a way that I can not have to worry about whi