how to alter a column which is having the data

srikotesh
srikotesh used Ask the Experts™
on
acctname varchar2(25);
amount Number(18,8);

the above data types i have to increase size as below
test table having the data for the both columns
when i am doing alter acctname is suceeded.
second alter is failed to modify

what is the difference in the below two alter commands
how to Alter second statement of amount column if it is having data.

Alter test modify acctname varchar2(50);
Alter test modify amount Number(18,10);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer
Commented:
In this case you're going to have to go through a couple of hoops.

ALTER test ADD amount2 NUMBER(18,10);
UPDATE test SET amount2 = amount;
ALTER test DROP COLUMN amount;
ALTER test RENAME COLUMN amount2 to amount;

Open in new window


Basically, you need to create a new column, update the new column values with the old column values, drop the old column, then rename the new column to the old column name. Note, if your table has lots of data in it the UPDATE statement might take a little while to execute.
SujithData Architect

Commented:
You need to retain the same precision scale difference -

Alter table test modify amount Number(20,10);

Author

Commented:
hi

what is the difference in my first and second alter statements?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist
Commented:
To preserve the original table structure, you need to add a couple of steps. Adding the column will place the new column at the end which may cause issues elsewhere. Try this -
alter table test add temp_amount number(18,10);
update test set temp_amount = amount;
update test set amount = null;
alter table test modify amount(18,10);
update test set amount = temp_amount;
alter table test drop column temp_amount;
Russ SuterSenior Software Developer
Commented:
In your first statement the only thing you're doing is expanding the size of the string so there is no possibility of data loss. In the second example you're increasing scale without increasing precision so data loss is possible. As an example, let's take a column that has a smaller precision and scale so it's easier to see.

Let's say you have a NUMBER(5,2) column. In that case it's perfectly valid to have the number 123.45. If you now change it to NUMBER(5,4) then it's possible to have more decimal places to the right but at the cost of losing them to the left. If you increase scale by 2 then you need to also increase precision by the same amount to preserve all possible numeric values that might be stored.

@awking00 - Aren't you doing essentially the same thing that I suggested in an earlier post?

Author

Commented:
HI Experts,

in my second alter statement:
requirement is amount val should accept 10 decimal digits

Now which step do i need to follow

Alter table test modify amount Number(20,10);

or

ALTER test ADD amount2 NUMBER(18,10);
UPDATE test SET amount2 = amount;
ALTER test DROP COLUMN amount;
ALTER test RENAME COLUMN amount2 to amount;
Russ SuterSenior Software Developer

Commented:
Either of those solutions will work. The first one will increase both the scale and precision by 2 digits. The second example will preserve your precision but increase your scale. Depending on how large the decimals are that you're storing then one solution might be better than the other. If you absolutely need to be able to store 10 digits to the left of the decimal then you should use the first example. If storage size is the greater concern then the 2nd approach is preferred.
Data Architect
Commented:
You don't need to go through the steps of adding and dropping columns. It will change the order of columns in your table. I.e. your amount column will be at the end of the table after the operation.

All you need to do is to ensure the precision/scale difference is retained.

Alter table test modify amount Number(20,10);
Russ SuterSenior Software Developer

Commented:
Column and row order should be irrelevant in a table. If you're relying on either to be consistent then you have bad code.

That being said, Sujith's solution is the simpler one if storage size is a non-issue.

Author

Commented:
THANKS
SujithData Architect

Commented:
Be careful with the add/drop/rename column option. It will impact the largest number you can store in your table. Before the operation max number will be 9999999999 and after it will be 99999999. I.e. you can only store numbers 100 times lesser after the operation!!

so if you have whole numbers greater than 99999999 your update step will still fail.

That is a functional impact!!!

SQL> drop table tbl1;

Table dropped.

SQL>
SQL> create table tbl1(
  2  acctname varchar2(25),
  3  amount Number(18,8)
  4  );

Table created.

SQL>
SQL> insert into tbl1 values('test', 9999999999);

1 row created.

SQL>
SQL>
SQL> Alter table tbl1 add amount2 Number(18,10);

Table altered.

SQL>
SQL> update tbl1 set amount2 = amount;
update tbl1 set amount2 = amount
                          *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> update tbl1 set amount2 = amount/100;

1 row updated.

SQL>
SQL> alter table tbl1 drop column amount;

Table altered.

SQL>
SQL> alter table tbl1 rename column amount2 to amount;

Table altered.

SQL>
SQL> insert into tbl1 values('test', 9999999999);
insert into tbl1 values('test', 9999999999)
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> insert into tbl1 values('test', 999999999);
insert into tbl1 values('test', 999999999)
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> insert into tbl1 values('test', 100000000);
insert into tbl1 values('test', 100000000)
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> insert into tbl1 values('test', 99999999);

1 row created.

Open in new window

Author

Commented:
Thank you, i understood the impact.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial