CODEWITHSUNDEEP
sqloracle-database
user1712088
user1712088

Reputation: 25

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.

Upvotes: 1

Views: 17289

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30765

That's because COMMENT is a reserved word in SQL - use another column name (like HIREDETAIL_COMMENT) instead:

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

Upvotes: 1

Ben
Ben

Reputation: 52853

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.

Upvotes: 6

Related Questions