how to alter a column which is having the data

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);
LVL 2
srikoteshAsked:
Who is Participating?
 
SujithData ArchitectCommented:
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);
0
 
Russ SuterCommented:
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.
0
 
SujithData ArchitectCommented:
You need to retain the same precision scale difference -

Alter table test modify amount Number(20,10);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
srikoteshAuthor Commented:
hi

what is the difference in my first and second alter statements?
0
 
awking00Commented:
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;
0
 
Russ SuterCommented:
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?
0
 
srikoteshAuthor 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;
0
 
Russ SuterCommented:
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.
0
 
Russ SuterCommented:
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.
0
 
srikoteshAuthor Commented:
THANKS
0
 
SujithData ArchitectCommented:
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

0
 
srikoteshAuthor Commented:
Thank you, i understood the impact.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.