Dreamer Dreamer - 1 month ago 9
SQL Question

Get ORA 01722 invalid number when try to copy data from one table to another

I have to admin there is slight difference between the columns from both tables. But I thought that was minor.

The table Source has three attributes

Amount NUMBER(10,2)
SHORTNAME VARCHAR2(3)
NAME VARCHAR2(40)


The table Target has the same set of attributes but slightly different constraints on their type

Amount NUMBER
SHORTNAME VARCHAR2(255)
NAME VARCHAR2(255)


I thought table
Target
should have more tolerance on its capacity, but when I run the query

insert into DB_B.Target select * from DB_A.Source ;


Unfortunately I get the popular 01722 error:


SQL Error: ORA-01722: invalid number -- 01722. 00000 - "invalid number"


So when we say when copying data from two tables with the same schema, are we talking about 100% IDENTICAL even on the TYPE CONSTRAINT?

Please help.

Update



Before thunder storm hit the office, I have to mention that the column order on both tables are different. Thanks to @a_horse_with_no_name help me think out this issue and give the perfect answer.

Answer

Maybe the order of the columns is not identical in the two tables.

Try to explicitely list the columns to make sure they aren't mixed up for some reason:

insert into db_b.target 
    (amount, shortname, name) 
select amount, shortname, name 
from db_a.source;

Note that it's generally considered bad style to not list the columns in the insert clause and to use select * in that way (even if that isn't the cause of your problem)

Comments