+ Reply to Thread
Results 1 to 1 of 1

ORA-01722 : invalid number

  1. ORA-01722 : invalid number

    ORA-01722 error occurs when you try to convert a character string into a number, but the character given cannot be converted to a valid number using that operation. The numeric column can either be in a select, insert, update using the to_number function or with an implicit conversion operation. The ORA-01722 error can also occur due to a implicit or explicit value conversion in a where statement.

    A valid number in Oracle can be one the following combinations

    1. digits 0 to 9 combined
    2. a decimal point character to indicate a decimal value ( . )
    3. a E or e character denoting that the number is a floating point per scientific notation
    4. a sign character + or - , to show the positive or negative value of the number

    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

    ORA-01722 Examples



    1. ORA-01722 can occur when you try to add character or date and numeric values. In the case below if test is a character column containing random characters you will get an invalid number error.

    select test+123 from dual;
    ERROR:ORA-01722: invalid number
    no rows selected

    2. ORA-01722 can occur when you attempt to compare 2 columns in a where clause implicitly. In the case below the name column is a character but you are trying to run a query against a numeric range

    select title from employees
    where name between 1 and 4;
    ERROR:ORA-01722: invalid number
    no rows selected
    Last edited by oracle-dba; 05-14-2011 at 01:23 PM.

+ Reply to Thread