Oracle: SQL Error: ORA-01722: invalid number when copying NUMBER to VARCHAR2

Question

I have an Oracle database, and I need to change a NUMBER(38,0) column to a VARCHAR2 column in one of the tables. At the advice of this post, I'm doing the following:

1. Add a temp column of type VARCHAR2
2. Copy the data from the original NUMBER column to the temp column
3. Drop the original NUMBER column
4. Rename the temp column to original column name

However, step 2 causes the subject error, even though the copy is from NUMBER to VARCHAR2.

As a safeguard, I'm running my code below on a copy of the table to be altered, which I created using:

create table TPA_SPEC_COPY as select * from TPA_SPEC;
COMMIT WORK;

Here is my SQL code trying to perform the steps above:

ALTER TABLE TPA_SPEC_COPY
ADD (PA_SPEC_VALUE_NUM_TEMP  VARCHAR2(40 CHAR));

UPDATE TPA_SPEC_COPY set PA_SPEC_VALUE_NUM_TEMP = TO_CHAR(PA_SPEC_VALUE_NUM);  -- This is Line 4 where I get the error

ALTER TABLE TPA_SPEC_COPY DROP COLUMN PA_SPEC_VALUE_NUM_TEMP;

ALTER TABLE TPA_SPEC_COPY 
RENAME COLUMN PA_SPEC_VALUE_NUM_TEMP TO PA_SPEC_VALUE_NUM;

This is the error I am receiving in SQL Developer:

Error starting at line : 4 in command -
UPDATE TPA_SPEC_COPY set PA_SPEC_VALUE_NUM_TEMP = TO_CHAR(PA_SPEC_VALUE_NUM)
Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

EDIT: Adding the table structure (using DESCRIBE TPA_SPEC_COPY)

DESCRIBE TPA_SPEC_COPY
Name                     Null     Type               
------------------------ -------- ------------------ 
PA_ID                    NOT NULL NUMBER(10)         
PA_VERSION_NUM           NOT NULL NUMBER(10)         
PA_SPEC_VERSION_NUM      NOT NULL NUMBER(10)         
SPEC_TYPE_CD             NOT NULL CHAR(10 CHAR)      
PA_SPEC_VALUE_NUM                 NUMBER(38)         
UOM_CD                            CHAR(10 CHAR)      
PA_SPEC_FORMULA_TXT               VARCHAR2(100 CHAR) 
PA_SPEC_COMMENT_TXT               VARCHAR2(250 CHAR) 
DISPLAY_FORMAT_NM                 VARCHAR2(30 CHAR)  
ROW_PROG_UPDT_ID         NOT NULL CHAR(12 CHAR)      
ROW_STATUS_CD            NOT NULL CHAR(1 CHAR)       
ROW_UPDATE_TIME          NOT NULL TIMESTAMP(9)       
ROW_LASTUPD_USERID       NOT NULL VARCHAR2(80 CHAR)  
ROW_CREATE_TIME          NOT NULL TIMESTAMP(9)       
ROW_CREATE_USERID        NOT NULL VARCHAR2(80 CHAR)  
VARIABLE_ID                       VARCHAR2(100 CHAR) 
SPEC_FORMULA_NM                   VARCHAR2(80 CHAR)  
SPEC_FORMULA_VERSION_NUM          NUMBER(10)         
DR_SENSITIVITY_NUM                NUMBER(38)         
PA_SPEC_RISKTODR_CD               CHAR(4 CHAR)       
PA_SPEC_VALUE_NUM_TEMP            VARCHAR2(40 CHAR)  

I ran the same script on a small test table I created with fake numbers, and it ran successfully. Perhaps this suggests that this is a data issue? Regardless, I don't know how else I can tackle his. Any suggestions will be greatly appreciated.

Answer 1

I can only suspect some kind of data corruption, I'd suggest this plsql code to try find it, adding a commit after the update would help at least moving the good values.

I know this is not the best way to do it, it is code that is gonna be used once.

begin
  for i in (select pa_id,PA_SPEC_VALUE_NUM from TPA_SPEC_COPY)
loop
   begin
    update TPA_SPEC_COPY set PA_SPEC_VALUE_NUM_TEMP=to_char(PA_SPEC_VALUE_NUM)

     where pa_id=i.pa_id;
   exception when others
    then 
       dbms_output.put_line('Problem with ' || i.pa_id);
 end;
end loop;
end;