how to modify the size of a column

Question

I created the table Test_Project2 in Oracle SQL Developer. After that I realized that the column proj_name is of a small size, so I decided to modify the column using the follwoing statement

ALTER TABLE TEST_PROJECT2 MODIFY proj_name VARCHAR2(300);

but for some reason Oracle SQL Developer underscores the semi-colon with red and I do not what is mistake and how to correct it

Test_Project2:

CREATE TABLE Test_Project2 (
proj_id number(30),
proj_name VARCHAR2 (30),
proj_desc VARCHAR2(300)
);

http://stackoverflow.com/questions/10179217/alter-the-size-of-column-in-table-containing-data — Mudassir Hasan, Sep 30 '16 at 07:04
i solved it..it should be: ALTER TABLE Test_Project2 MODIFY (proj_name varchar(150)); / — Amrmsmb, Sep 30 '16 at 07:05

score 140 · Accepted Answer · answered Sep 30 '16 at 12:21

140

Regardless of what error Oracle SQL Developer may indicate in the syntax highlighting, actually running your alter statement exactly the way you originally had it works perfectly:

ALTER TABLE TEST_PROJECT2 MODIFY proj_name VARCHAR2(300);

You only need to add parenthesis if you need to alter more than one column at once, such as:

ALTER TABLE TEST_PROJECT2 MODIFY (proj_name VARCHAR2(400), proj_desc VARCHAR2(400));

answered Sep 30 '16 at 12:21

sstan

35,425
6
48
66

9

I prefer to to add "CHAR" with varchar2 after the number, to avoid ambiguity with "BYTE": `ALTER TABLE TEST_PROJECT2 MODIFY proj_name VARCHAR2(300 CHAR);`. See more here: https://stackoverflow.com/questions/7477564/varchar2n-bytechar-default-char-or-byte – Matteo A Aug 21 '18 at 10:05

score 14 · Answer 2 · edited Sep 30 '16 at 10:37

14

If you run it, it will work, but in order for SQL Developer to recognize and not warn about a possible error you can change it as:

ALTER TABLE TEST_PROJECT2 MODIFY (proj_name VARCHAR2(300));

edited Sep 30 '16 at 10:37

jva

2,797
1
26
41

answered Sep 30 '16 at 07:07

Sebz

492
2
4

score 12 · Answer 3 · edited Nov 12 '19 at 10:10

12

This was done using Toad for Oracle 12.8.0.49

ALTER TABLE SCHEMA.TABLENAME 
    MODIFY (COLUMNNAME NEWDATATYPE(LENGTH)) ;

For example,

ALTER TABLE PAYROLL.EMPLOYEES 
    MODIFY (JOBTITLE VARCHAR2(12)) ;

edited Nov 12 '19 at 10:10

Oranit Dar

1,539
18
17

answered Jun 20 '19 at 20:34

Cecil Ryu Martial Arts

121
1
2

how to modify the size of a column

3 Answers3

Linked