| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Change the length of a column
You can't change the data dictionary length unless you empty the table.... BUT.... you can shrink the existing data and then add a constraint that will not allow people to put in information larger then your "new" length.
for example:
SQL> update emp set ename= substr(ename,1,4);
14 rows updated.
SQL> alter table emp add constraint enam_len check ( length(ename) <= 4 );
Table altered.
SQL> update emp set ename = ename || '!';
update emp set ename = ename || '!'
*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.ENAM_LEN) violated
Now, you have the varchar2 column that never contains more then 4 characters, no one can put in data > 4 characters and since a varchar2(50) takes as much space as a varchar2(100), you have achieved everything (except for the data dictionary update) you set out to.
On 1 May 1997 17:03:52 GMT, lovedog_at_gwis2.circ.gwu.edu (Shaochun Lin) wrote:
>Are there any quick way to make the column length shorter.
>Some columns is too long. I want to shrink them from varchar2(100)
>to varchar2(50), but Oracle don't let you do that directly unless the
>the column is empty. Thanks for any helps, Shaochun,
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |