"Invalid Identifier" Error while trying to add a column using "alter" in Sql

Question

I was trying to add a column named 'description' to the table named 'building.

ALTER TABLE building ADD COLUMN description varchar(255) NOT NULL;

Im getting this error as :

SQL ERROR: ALTER TABLE building ADD COLUMN description VARCHAR(255) NOT NULL * ERROR at line 1: ORA-00904: : invalid identifier

i googled my problems, even tried gpts

Answer 1

As shown in the syntax diagrams, you should not have the COLUMN keyword - that is being seen as the column name, and is a reserved word, so it indeed an invalid identifier.

It should just be:

ALTER TABLE building ADD description varchar(255) NOT NULL;

fiddle

As commented, consider using varchar2 instead of varchar as Oracle recommends, though they're synonymous at the moment (they've been threating for them to diverge for 30+ years...)

Answer 2

Needed fixes:

1. Alter TABLE Syntax.
2. Use of datatype- varchar instead of varchar2.

SQL syntax for adding a column doesn't use COLUMN keyword in the ALTER statement. Instead, directly use column_name ie, description directly.

Wrong Syntax:

ALTER TABLE building ADD COLUMN description varchar(255) NOT NULL;

Correct syntax:

ALTER TABLE building ADD description varchar2(255) NOT NULL;