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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
srikoteshAuthor Commented:
hi

what is the difference in my first and second alter statements?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.