I get "ORA-00900: invalid SQL statement" error while modifying column

Question

I have this code in SQL developer:

create table course_table 
(
    course_id number(5) not null,
    course_name varchar2(25) not null,
    course_hours number(1) not null,
    department varchar2(10) not null,
    description varchar2(50) not null,
    teacher varchar2(20) not null,
    create_dttm DATE,
    update_dttm TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_created varchar2(20) not null,
    user_updated varchar2(20) not null
);

commit;

alter table course_table
add CONSTRAINT course_id_pk PRIMARY KEY (course_id);

ALTER TABLE course_table
**ALTER** COLUMN course_id varchar2(5)

I get an error on the bold word. It says

ORA-00900: invalid SQL statement.

Can someone help me what am I doing wrong?

@hoss yes thank you to you too someone else said the same solution to me — Faraz, May 25 '15 at 18:22

score 2 · Answer 1 · answered May 25 '15 at 17:44

The Oracle Syntax for modifying a column is MODIFY

ALTER TABLE course_table MODIFY course_id varchar2(5);

I believe you are using the Sql Server syntax (which is ALTER TABLE ALTER COLUMN)

Example

Obviously this begs the question why you don't just create the column with the correct type in the first place, lest you face migration issues.

score 1 · Accepted Answer · answered May 25 '15 at 22:26

Always add the CONSTRAINT inside the table like so

CREATE TABLE COURSE_TABLE
(
COURSE_ID NUMBER(5) NOT NULL,
CONSTRAINT COURSE_TABLE_PK PRIMARY KEY(COURSE_ID)
);

This will solve all the head aches in the world trying the sql fiddler or trying to modify it. Since you already know that is going to be the primary key you might as well install the constraint from the start.

I get "ORA-00900: invalid SQL statement" error while modifying column

2 Answers2