51

Possible Duplicate:
How to make a varchar2 field shorter in Oracle?

In Oracle(9i and above)

 Alter table employee  
 MODIFY ename varchar2(10);

I want to alter the column ename of table employee from varchar2(30) to varchar2(10)

  • Case1 : if the table has data having ename column containing values which are of length less than 10 char(i mean it can fit in varchar2(10) comfortably) - is this allowed by oracle ?

  • Case 2: if the table has data having ename column containing values which are of length greater than 10 char(i mean it can not fit in varchar2(10)) - is this not allowed by oracle ?

Community
  • 1
  • 1
lowLatency
  • 5,534
  • 12
  • 44
  • 70
  • 2
    I'm not sure I understand what the question is. What would you want to happen to an employee that has a 20 byte `ENAME` value if you try to decrease the size of the column to `VARCHAR2(10)`? Do you want to truncate the data after the 10th byte? – Justin Cave Apr 16 '12 at 18:05
  • 2
    Appears similar to http://stackoverflow.com/questions/8404194/how-to-make-a-varchar2-field-shorter-in-oracle/8404240#8404240 – xQbert Apr 16 '12 at 18:05
  • @Justin Cave... yes I do not have any problem with that ......as this is obvious that if i change the size to varchar2(10)...data will be truncated for ENAME having 20 byte...will oracle give some type of warning for this alter or will do it – lowLatency Apr 16 '12 at 18:15
  • No, you cannot have data greater than the size of a column in a column. What is the point otherwise? If you do have data greater than the size of the columns why do you want to decrease it? – Ben Apr 16 '12 at 18:16
  • @jain007:i think link mentioned by `XQBERT` cater your problem ,first update all column having data more than 10 byte to 10 byte ,and then alter you're column. – Gaurav Soni Apr 16 '12 at 18:18
  • @ Gaurav...what is mentioned by Bob in the below post is also right...that it will give an error.....right? And what i understood is if i want to apply this case 2 successfully then i have to follow what xQbert says.....i.e first update all columns having data more than 10 byte – lowLatency Apr 16 '12 at 18:47

1 Answers1

43

Case 1 : Yes, this works fine.

Case 2 : This will fail with the error ORA-01441 : cannot decrease column length because some value is too big.

Share and enjoy.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • 3
    ...and if i need the case 2 to work, then will i need to follow http://stackoverflow.com/questions/8404194/how-to-make-a-varchar2-field-shorter-in-oracle/8404240#8404240 by xQbert – lowLatency Apr 16 '12 at 18:30
  • 2
    Yes - you'll need to truncate any values which are greater in length than the size to which you'll shrink the column. – Bob Jarvis - Слава Україні Apr 16 '12 at 19:06
  • 3
    Or better, study the impact of truncate that data. If you can make a process that would leave the information as rich as before, do it. For example, converting key words into short version of them. e.g: Example -> e.g. Dropping information is not good if didn't studied the impact. – Roger Apr 16 '12 at 22:34