Dealing With An ORA-01440 When Altering a Table in Oracle

When altering a table to an Oracle reduce a columns size, you’ll get a ORA-01440 error, indicating that you can’t make the adjustment because of the potential data loss.

I had to do this to recently to a bunch of columns, as I had incorrectly specified the size of numbers that would end up as integers in the application. Note that Oracle can treat any column as an integer by specifying a scale of 0. But for entity framework (via the Devart providers) to map to an long (Int64), you want the precision and scale to be Number(19,0).

Since the fields were left at the default size, they were too large. And being that many of these fields were a primary key or foreign key, constraints were also and issue. So the table had to be backed up, and restored. In order to safely do this, any triggers needed to be disabled.

Rather than do this repeatedly for many tables and risk missing something, or making typos, I created a script generator. You enter the table, and add any foreign key constraints, and then generate a script. All that is left to do is to add the columns to be re-sized to the alter statement in the middle of the script.

The script and generation code is all written in javascript, using jquery and jquery templates. If you’re interested in the code, I have it up on github.