Oracle: SQL Error: ORA-01722: invalid number when copying NUMBER to VARCHAR2
<p>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 <a href="https://stackoverflow.com/a/34092102/3233629">this post</a>, I'm doing the following:</p>

<ol>
<li>Add a temp column of type VARCHAR2</li>
<li>Copy the data from the original NUMBER column to the temp column</li>
<li>Drop the original NUMBER column</li>
<li>Rename the temp column to original column name</li>
</ol>

<p>However, step 2 causes the subject error, even though the copy is from <em>NUMBER to VARCHAR2</em>. </p>

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

<pre><code>create table TPA_SPEC_COPY as select * from TPA_SPEC;
COMMIT WORK;
</code></pre>

<p>Here is my SQL code trying to perform the steps above:</p>

<pre><code>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;
</code></pre>

<p>This is the error I am receiving in SQL Developer:</p>

<pre><code>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.
</code></pre>

<p><strong>EDIT:</strong> Adding the table structure (using <code>DESCRIBE TPA_SPEC_COPY</code>)</p>

<pre><code>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)  
</code></pre>

<p>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. </p>