Avoiding "ORA-01722 - Invalid Number" Error When Inserting Data From One Table to Another

	If you do an insert into...select * from... statement to insert data from one table to another, it's easy to get the 'ORA-01722 - Invalid Number' error.  In this article, I'll explain why, as well as how to get around it.

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:

1. Create a duplicate table: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
2. Delete all the rows from the original table: DELETE FROM FUND_ACCOUNT;
3. 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);
4. 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;