ORA-01440 trying to convert FLOATs to NUMBERs

I recently came across a legacy database that was using Oracle FLOAT datatype for Primary and Foreign Keys. As the database needed some attention I was asked to get data types in order and get rid of the FLOATs in favor of NUMBER data types.
My first attempt was not pretty. I used exp/imp to make a copy of the schema of interest and hit my head on ORA-01440 "column to be modified must be empty to decrease precision or scale" when trying to alter the columns directly from FLOAT(126) to NUMBER(38). Even though the data I found in the FLOAT columns could have fit inside of a NUMBER(6) Oracle was taking the safe approach and not allowing me to proceed.
Survey the data
To be safe I eyeballed the data in the FLOAT columns. I found only integers and the values were all less than 1,000,000 so I felt safe proceeding with the conversion. If I found real FLOATs I would need to evaluate for any potential rounding issues.

-- Survey the data
-- This SQL statement will generate SELECT statements on data in each FLOAT column in a schema
SELECT 'select '||column_name||' from '||OWNER||'.'||TABLE_NAME||' where '||COLUMN_NAME||' is not null;' 
  FROM DBA_TAB_COLS 
 WHERE OWNER='MYSCHEMA' 
       AND data_type='FLOAT';

Export the data
As we don't want to mess with this data in place, export it.

export NLS_LANG=.UTF8

exp dba/password
userid=dba/pass
owner=MYSCHEMA
file=out.dmp
log=out.log
feedback=100000
buffer=10000000
statistics=none

Import tables and data - no constraints or indexes

export NLS_LANG=.UTF8

userid=dba/password
file=out.dmp
log=import1.log
fromuser=MYSCHEMA
touser=MYSCHEMA
feedback=100000
constraints=N
indexes=N

Change FLOAT to NUMBER on target schema
First attempt to convert any columns that will convert without errors.

SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' modify ('||column_name||' NUMBER(38));' 
FROM DBA_TAB_COLS
WHERE owner='MYSCHEMA' and data_type='FLOAT';

Second, create a new column, copy the values, then drop original column.

-- Add new temp column
SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' add ('||column_name||'X NUMBER(38));' 
  FROM dba_tab_cols 
 WHERE owner='MYSCHEMA' and data_type='FLOAT';

-- Update the temp column with original column value
SELECT 'update '||OWNER||'.'||TABLE_NAME||' set '||column_name||'X = '||column_name||';' 
  FROM dba_tab_cols 
 WHERE owner='MYSCHEMA' and data_type='FLOAT';

Now generate these two sets of statements and save the text before running. I usually execute in SQL Developer so I can copy out columns of text results and paste in another SQL Worksheet window or text editor window if I want to save the results:

SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' drop column '||column_name||';'  as stmt1,
       'alter table '||OWNER||'.'||TABLE_NAME||' rename column '||column_name||'X to '||COLUMN_NAME||';' as stmt2
FROM DBA_TAB_COLS
WHERE OWNER='MYSCHEMA' AND DATA_TYPE='NUMBER';

Import index and constraint definitions

imp dba/password
file=out.dmp
log=import2.log
fromuser=MYSCHEMA
touser=MYSCHEMA
feedback=100000
rows=n
indexes=Y
constraints=Y
ignore=y

After this import you should have all indexes and constraints available again but no more FLOAT columns!

Add new comment