Basic SQL question 1

[SmpTraining]

Hi all,

I need to change the size of a column. I'd normally do this using TOAD (or ask someone else!) but I'm using SQL Plus.

Can you tell me the syntax please?

I am making some columns bigger and some smaller. I do not need to maintain the data in the columns but I do not want to create new columns. I need to alter the existing ones.

Many thanks Steve Phillips, Crystal Consultant

 

[lewisp]

To make a column smaller you will have to set the contents to NULL before issuing the command.

You can use this:

ALTER TABLE <table> MODIFY (
<column_name> <data_type>(<value>));

eg ALTER TABLE table MODIFY (
first_name VARCHAR2(25));

 

[oracleraj]

but if the data in the column is bigger in length. this will happen when u reduce the size of the column so in such cases first make the columns blank or alteast those rows blanks which are having bigger data. by using update clause.

update <table_name>
set <field_name> = value (normally NULL)
where <cond1> = <valu1> .....


then use alter command
alter table table_name modify
(filed_name1 datatype(size),
(filed_name2 datatype(size),
(filed_name3 datatype(size))