SQL error ORA-01722 invalid number

Question

A very easy one for someone   The following insert is giving me the     ORA-01722  invalid number   why   INSERT INTO CUSTOMER VALUES  1  MALADY   Claire   27 Smith St Caulfield   0419 853 694    INSERT INTO CUSTOMER VALUES  2  GIBSON   Jake   27 Smith St Caulfield   0415 713 598    INSERT INTO CUSTOMER VALUES  3  LUU   Barry   5  Jones St Malvern   0413 591 341    INSERT INTO CUSTOMER VALUES  4  JONES   Michael   7  Smith St Caulfield   0419 853 694    INSERT INTO CUSTOMER VALUES  5  MALADY   Betty   27 Smith St Knox   0418 418 347

User · Answer

This is because      You executed an SQL statement that tried to convert a string to a   number  but it was unsuccessful    As explained in    Oracle PLSQL  ORA-01722 Error    To resolve this error      Only numeric fields or character fields that contain numeric values   can be used in arithmetic operations  Make sure that all expressions   evaluate to numbers

User · Answer

An ORA-01722 error occurs when an attempt is made to convert a character string into a number  and the string cannot be converted into a number   Without seeing your table definition  it looks like you re trying to convert the numeric sequence at the end of your values list to a number  and the spaces that delimit it are throwing this error   But based on the information you ve given us  it could be happening on any field  other than the first one

User · Answer

In my case the conversion error was in functional based index  that I had created for the table   The data being inserted was OK  It took me a while to figure out that the actual error came from the buggy index    Would be nice  if Oracle could have gave more precise error message in this case

User · Answer

Here s one way to solve it  Remove non-numeric characters then cast it as a number    cast regexp replace  0419 853 694      0-9         as number

User · Answer

You can always use TO NUMBER   function in order to remove this error This can be included as INSERT INTO employees phone number values TO NUMBER  0419 853 694

User · Answer

As this error comes when you are trying to insert non-numeric value into a numeric column in db it seems that your last field might be numeric and you are trying to send it as a string in database  check your last value

User · Answer

Suppose telephone number is defined as NUMBER then the blanks cannot be converted into a number   create table telephone number  tel number number   insert into telephone number values   0419 853 694      The above gives you a      ORA-01722  invalid number

User · Answer

Oracle does automatic String2number conversion  for String column values  However  for the textual comparisons in SQL  the input must be delimited as a String explicitly  The opposite conversion number2String is not performed automatically  not on the SQL-query level  I had this query   select max acc num  from ACCOUNTS where acc num between 1001000 and 1001999   That one presented a problem  Error  ORA-01722  invalid number I have just surrounded the  quot numerical quot  values  to make them  Strings   just making them explicitly delimited   select max acc num  from ACCOUNTS where acc num between  1001000  and  1001999       and voil    It returns the expected result  edit  And indeed  the col acc num in my table is defined as String  Although not numerical  the invalid number was reported  And the explicit delimiting of the string-numbers resolved the problem  On the other hand  Oracle can treat Strings as numbers  So the numerical operations functions can be applied on the Strings  and these queries work   select max string column  from TABLE  select string column from TABLE where string column between  2  and  z   select string column from TABLE where string column  gt   1   select string column from TABLE where string column  lt    b

User · Answer

try this as well  when you have a invalid number error  In this  a emplid is number and b emplid is an varchar2 so if you got to convert one of the sides   where to char a emplid  b emplid

User · Answer

Well it also can be     SELECT t col1  t col2    test    t col3  as test col3  FROM table t    where for concatenation in oracle is used the operator    not      In this case you get   ORA-01722  invalid number

User · Answer

If you do an insert into   select   from   statement  it s easy to get the  Invalid Number  error as well    Let s say you have a table called FUND ACCOUNT that has two columns   AID YEAR  char 4  OFFICE ID char 5    And let s say that you want to modify the OFFICE ID to be numeric  but that there are existing rows in the table  and even worse  some of those rows have an OFFICE ID value of      blank    In Oracle  you can t modify the datatype of a column if the table has data  and it requires a little trickery to convert a     to a 0   So here s how to do it    Create a duplicate table  CREATE TABLE FUND ACCOUNT2 AS SELECT   FROM FUND ACCOUNT  Delete all the rows from the original table  DELETE FROM FUND ACCOUNT  Once there s no data in the original table  alter the data type of its OFFICE ID column  ALTER TABLE FUND ACCOUNT MODIFY  OFFICE ID  number   But then here s the tricky part   Because some rows contain blank OFFICE ID values  if you do a simple INSERT INTO FUND ACCOUNT SELECT   FROM FUND ACCOUNT2  you ll get the  ORA-01722 Invalid Number  error   In order to convert the      blank  OFFICE IDs into 0 s  your insert statement will have to look like this     INSERT INTO FUND ACCOUNT  AID YEAR  OFFICE ID  SELECT AID YEAR  decode OFFICE ID     0 OFFICE ID  FROM FUND ACCOUNT2

User · Answer

This happened to me too  but the problem was actually different  file encoding   The file was correct  but the file encoding was wrong  It was generated by the export utility of SQL Server and I saved it as Unicode   The file itself looked good in the text editor  but when I opened the   bad file that the SQL loader generated with the rejected lines  I saw it had bad characters between every original character  Then I though about the encoding   I opened the original file with Notepad   and converted it to ANSI  and everything loaded properly

User · Answer

The ORA-01722 error is pretty straightforward   According to Tom Kyte      We ve attempted to either explicity or implicity convert a character string to a number and it is failing    However  where the problem is is often not apparent at first   This page helped me to troubleshoot  find  and fix my problem   Hint  look for places where you are explicitly or implicitly converting a string to a number   I had NVL number field   string   in my code

[sql] SQL error "ORA-01722: invalid number"

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to casting

Examples related to type-conversion