Overcoming the ORA-01440

I had to test the effect of changing a column type from NUMBER(1) to NUMBER. Luckily our client code did not choke. I guess they translate into integers in the programming language on the client. After the test was done, I tried to alter the table back to its original state. Unfortunately I ran into an ORA-01440.

The ORA-01440 happened because I was trying to change the type to a type with decreased precision. Apparently you can only do that if the column you are changing is empty. Well I figured I could get around that by making all the values in the column to be NULL. I could clean the data up later.

There was one additional problem in my plan. The column I was dealing with could not be NULL. I was in a bind. That's when Tech on the Net provided the idea to just DELETE the records. That way there was no data to hold me back. Actually I exported the data and reimported it after the type was changed. This is something you won't learn in the books. You got to experience this specific type of problem to be able to work around it.