user1712088 user1712088 - 2 months ago 12
SQL Question

error "%s: invalid identifier" occurs when I create a table in Oracle DB

when I create a table like this:

create table DBDI_HIREDETAIL(
HireID int not null,
EquipID int not null,
Quantity int,
TotalFee float,
Comment varchar(200)
);


The error occurs like this:


Error at Command Line: "TotalFee float"
Error report:
SQL Error: ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"


I don't understand why my code has an error, it seems to be fine.

Ben Ben
Answer

COMMENT is a reserved word in Oracle; it's used for adding comments to the data dictionary. You should avoid using this as a column name.

SQL> create table a ( comment number );
create table a ( comment number )
                 *
ERROR at line 1:
ORA-00904: : invalid identifier

If you really want to use this column name you're have to quote it, i.e. "COMMENT":

SQL> create table a ( "COMMENT" number );

Table created.

I would recommend you not doing this as you have to quote the column everywhere.