Changing precision of a column in the Oracle

Sometimes, we want to change the format of a column in database, say from NUMBER(12,2) to NUMBER(15,5). The first number is precision (number of digits) and second is scale (number of decimal places), e.g 15,5 means 10 whole numbers and 5 decimal places. For more info, see Oracle documentation.

The precision is limited to 38 (or use * as synonym) and in our db I have seen disturbing number of declarations like WORKLOAD(38,2). Oracle makes it easy to increase scale, but only if you also can increase precision:

-- The column is NUMBER(12,2)
alter table T_TEST modify (WORKLOAD NUMBER(15,5))

If you use 38 or * as precision, it gets more complicated and you have to do the heavy lifting on your own, because there are data that could potentially be lost. Most common way is to create a second column, copy the data, drop original column and rename former column. The other way is to set the original column to null and then you can use the alter table from above (see Stack Overflow for more details).

That works, but there may be constraints and you can lose stuff, not to mention it is a lot of work, prone to error… it would be nice to create a procedure that allows user to change format of the column.

Limitations:

  • Expects that column is a NUMBER
  • Expects that PK is named id

Requirements:

  • Inplace change, won’t change column ordering
  • Must works even if column has some constraints
  • Must not lose data, e.g. changing from NUMBER(7,3) to NUMBER(5,2) will work, but only if there is no row that has a column with number like 12.456 or 1234.
  • Must work across schema

set serveroutput on
declare
  p_owner varchar2(64 char) := 'APPLICATION';
  p_table varchar2(64 char) := 'T_TEST';
  p_column varchar2(64 char) := 'NUM';
  p_format varchar2(64 char) := 'NUMBER(15,5)';
  l_sql varchar2(512 char);
  l_temp_col varchar2(64 char);
  l_diff_ids clob; -- there can be quite a lot of different ids
  
  procedure disable_constraints(p_owner varchar2, p_table varchar2, p_column varchar2) is
    l_sql varchar2(512 char);
  begin
    for l_constraint in (select owner, table_name, constraint_name from ALL_CONS_COLUMNS 
                                where owner = p_owner
                                  and table_name = p_table
                                  and column_name = p_column)
    loop
      -- disable constraints 
      l_sql := 'alter table ' || l_constraint.owner || '.' || l_constraint.table_name || ' disable constraint ' || l_constraint.constraint_name;
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
    end loop;
  end;

  procedure enable_constraints(p_owner varchar2, p_table varchar2, p_column varchar2) is
    l_sql varchar2(512 char);
  begin
    for l_constraint in (select owner, table_name, constraint_name from ALL_CONS_COLUMNS 
                                where owner = p_owner
                                  and table_name = p_table
                                  and column_name = p_column)
    loop
      -- disable constraints 
      l_sql := 'alter table ' || l_constraint.owner || '.' || l_constraint.table_name || ' enable constraint ' || l_constraint.constraint_name;
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
    end loop;
  end;
  
begin
  dbms_output.put_line('Create temp column');
  
  -- beware oracle 30 char naming limit
  select 'TEMP_COLUMN_' || dbms_random.string('U', 10) into l_temp_col from dual;
  l_sql := 'alter table ' || p_owner || '.' || p_table || ' add ' || l_temp_col || ' ' || p_format;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  -- copy values to the new column, if values don't fit into new column, but only for upper bound (e.g. the whole number), I get ORA-01438
  -- for fractions, it doesn't work that way
  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || l_temp_col || ' = ' || p_column;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  -- validate that values are same in new format as were in old format
  l_sql := 'select listagg(id, '','') within group (order by id) from ' || p_owner || '.' || p_table || ' where ' || p_column || ' - ' || l_temp_col || ' != 0';
  execute immediate l_sql into l_diff_ids;
  if l_diff_ids is not null
  then
    -- drop temp column
    execute immediate 'alter table ' || p_owner || '.' || p_table || ' drop column ' || l_temp_col;
    raise_application_error(-20010, 'Values are different in the new format ' || l_diff_ids);
  end if;

  dbms_output.put_line('Disable constraints');
  disable_constraints(p_owner, p_table, p_column);
  
  -- set old to null
  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || p_column || ' = null';
  dbms_output.put_line('Change format of the column ' || p_column);
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  -- change format 
  l_sql := 'alter table ' || p_owner || '.' || p_table  || ' modify (' || p_column || ' ' || p_format || ')';
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || p_column || ' = ' || l_temp_col;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  -- drop temp column
  l_sql := 'alter table ' || p_owner || '.' || p_table || ' drop  column ' || l_temp_col;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  dbms_output.put_line('Enable constraints');
  enable_constraints(p_owner, p_table, p_column);
end;
/

It is not the prettiest code in the world, but it works.