Dreamer Dreamer - 9 months ago 93
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)

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


I thought table
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.


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 Source

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)