Re: Reducing a column's width

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: Thu, 25 Nov 1999 11:25:37 GMT
Message-ID: <383a9a45.27169767@read.news.globalnet.co.uk>

On Mon, 22 Nov 1999 19:43:00 GMT, newbie22_at_my-deja.com wrote:

>HI all,
>
>How can I change a column's width,
>where the column is not currently empty?
>
>You can't do it with ALTER TABLE,
>because the field is not empty.
>

Assuming oracle 8i:

SQL> create table fred ( a varchar2(50) );

Table created.

SQL> insert into fred values ( 'aaaaaaaaaaaaaaa' );

1 row created.

SQL> alter table fred modify a varchar2(30); alter table fred modify a varchar2(30)

                        *

ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length

SQL> alter table fred add b varchar2(50);

Table altered.

SQL> update fred set b = a, a = null;

1 row updated.

SQL> select * from fred;

A

---

B

---

aaaaaaaaaaaaaaa

SQL> alter table fred modify a varchar2(30);

Table altered.

SQL> update fred set a = b, b = null;

1 row updated.

SQL> alter table fred drop column b;

Table altered. Received on Thu Nov 25 1999 - 05:25:37 CST