0

I haven't seen this exact issue... I apologize if I missed it.

I have a database I inherited from the company that created it when they lost the contract to my company. Now, the customer has an "urgent" issue to change the precision of a value. They want to change a number from 10.3 to 10.8; the field is defined in the database as 10,3. In my research of the issue, It seems I have to do quite a bit of manipulation to do this since the table needs to be empty to change the precision. I am assuming that even though the default is 10.38 (or whatever it is), since they defined it as 10,3 upon creation, if I just increase the field in the ColdFusion code, when it saves, it will truncate to 10,3? The change would involve me capturing the current data, deleting the data, changing the precision, then reloading the data, ensuring the existing data has been changed as well. Yes, I know that's not in detail, but hopefully gets the point across. Thank you.

Louie Warren
  • 41
  • 1
  • 6
  • 2
    Look here http://stackoverflow.com/questions/9233909/changing-precision-of-numeric-column-in-oracle . Same question. – pero Feb 22 '14 at 20:32
  • Pretty sure Dave Costa is right and you want to increase both the precision and scale of the column; you might need to review [how numbers are defined and restricted](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref119). – Alex Poole Feb 23 '14 at 08:38

2 Answers2

3

Do you really want to change from a NUMBER(10,3) to a NUMBER(10,8)? That would significantly restrict the range of numbers that could be stored in the field - which is precisely why you can't do it when there is data in the column.

Or do you mean that you want to increase the number of decimal places from 3 to 8, while still allowing the same overall range of values? If so, then I think you want to change NUMBER(10,3) to NUMBER(15,8) - and you should be able to do that using a simple ALTER even if the column contains data.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • Yes, the customer wants just 8 after the decimal. I tried to explain and was shot down. In the mean time, DBAs with more up to date skills than mine, updated the field to NUMBER with no size designation. That fixed it. – Louie Warren Feb 26 '14 at 17:01
  • Well... it allowed me to change the ColdFusion to have 8 after the decimal. However, when I try to save it, it rounds it up and adds zeros for the 5 after that. (i.e.- 123.12345678 becomes 123.12300000) what am I missing? – Louie Warren Feb 26 '14 at 17:31
  • I'm beginning to think the easy fix the other DBAs did is what is causing me problems. Do I need to explicitly declare the 10,8 or 15,8 to make this work? – Louie Warren Feb 27 '14 at 15:16
  • I fixed it... I forgot to change the 3 to an 8. `` – Louie Warren Feb 27 '14 at 16:07
  • Well, I thought I fixed it. I fixed the datatype in Oracle on the Test DB and changed the code to ensure I had the 8 places after the decimal. It shows up fine on my Development system. To the best of my knowledge, Development and Test are identical except for the actual data. It works on Development but does the round up thing and makes the 5 extra digits zero. Weirdest thing I've seen. – Louie Warren Feb 28 '14 at 16:33
  • What could cause the same exact code to appear differently on two different systems? On my Development system I get 123.87654321. I then load it up to Test (which I have no further control of) and it appears 123.87700000. I have done very little ColdFusion and could be missing something, but I would think it would have to be the environment and not the code. Baffled. – Louie Warren Mar 03 '14 at 18:18
2

The easiest way to handle this is to rename the column, copy the data over, then drop the original column:

alter table EVAPP_FEES rename column AMOUNT to AMOUNT_OLD;

alter table EVAPP_FEES add AMOUNT NUMBER(14,2);

update EVAPP_FEES set AMOUNT = AMOUNT_OLD;

alter table EVAPP_FEES drop column AMOUNT_OLD;

OR

alter table EVAPP_FEES add AMOUNT_TEMP NUMBER(14,2);

update EVAPP_FEES set AMOUNT_TEMP = AMOUNT;

update EVAPP_FEES set AMOUNT = null;

alter table EVAPP_FEES modify AMOUNT NUMBER(14,2);

update EVAPP_FEES set AMOUNT = AMOUNT_TEMP;

alter table EVAPP_FEES drop column AMOUNT_TEMP;
PHPnoob
  • 293
  • 1
  • 3
  • 12
  • [Answer from here](http://stackoverflow.com/questions/9233909/changing-precision-of-numeric-column-in-oracle) – PHPnoob Feb 22 '14 at 20:33