CODEWITHSUNDEEP
sqloracle-database
Bonzay
Bonzay

Reputation: 750

Oracle modify column not allowed because of the length of value

I need to modify a column in my table, decreasing its length from 10 to 1: 

alter table mytable
modify mycolumn varchar2(1)

but this returns the following error:

  1. 00000 - "cannot decrease column length because some value is too big"

I have deleted the rows that I thought they were causing the problem:

delete from mytable
where length(mycolumn) > 1

and then 

select count(*) from mytable
where length(mycolumn) > 1

returns 0

But when I try to run the alter modify statement I get again the same error message.

What am I missing?

Upvotes: 0

Views: 2108

Answers (1)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

By default varchar2(1) is treated as varchar with the length of 1 byte. However, some encodings require more than 1 byte to store a character (UTF-8, UTF-16, etc.). Probably there's a character in the table that requires more than one byte, so the size of varchar2(1) is simply not enough.

varchar2(1 char) forces Oracle count the size in characters, not bytes, so that fixes the issue.

Upvotes: 2

Related Questions