Creating table via SQL Command Line, invalid identifier

Question

I'm currently learning SQL and I've installed oracle 11g express on my system. I'm trying to create a table however when I try to run the below command I get the following Error Message:

ERROR at line 3: ORA-00904 : invalid identifier

CREATE TABLE PROJECTS (
    proID       NUMBER(4) NOT NULL,
    Desc        CHAR(20),
    sDate       DATE,
    eDate       DATE,
    Budget      NUMBER(7,2),
    maxStaff    NUMBER(2)
);

Can anybody please tell me what I'm doing wrong?

Thanks for all the replies, I ran this command succesfully:

CREATE TABLE PROJECTS (
    proID       NUMBER(4) NOT NULL,
    description CHAR(20),
    sDate       DATE,
    eDate       DATE,
    Budget      NUMBER(7,2),
    maxStaff    NUMBER(2)
);

Really Appreciate the fast replies!

Chris

Also, consider not abbreviating your column names. You have 30 characters to work with and I recommend using all of them. Expand sDate to START_DATE for example. It will make view/API creation much easier to read. — mmmmmpie
– mmmmmpie, Commented Dec 22, 2014 at 14:21
Look here for the list of reserved keywords in Oracle: docs.oracle.com/cd/B19306_01/em.102/b40103/… — Sylvain Leroux
– Sylvain Leroux, Commented Dec 22, 2014 at 15:09
@KhurramAli, [identifier] is non-standard, used by Microsoft SQL Server, not Oracle. — Shannon Severance
– Shannon Severance, Commented Dec 22, 2014 at 22:48

mmmmmpie · Accepted Answer · 2014-12-22 13:55:57Z

1

You have DESC in as a column name. While you can use it you will have to encompass it in quotes:

CREATE TABLE PROJECTS (
    proID       NUMBER(4) NOT NULL,
    "Desc"        CHAR(20),
    sDate       DATE,
    eDate       DATE,
    Budget      NUMBER(7,2),
    maxStaff    NUMBER(2)
);

You will also have to use quotes every time you call it in a query. I recommend just changing that column to something else (maybe DESCRIPTION?)

answered Dec 22, 2014 at 13:55

mmmmmpie

3,0631 gold badge21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shannon Severance Over a year ago

A warning: Once you do this, "Desc" is case sensitive, unlike the other column names.

mmmmmpie Over a year ago

You are correct. It is recommended to not use these type of identifiers if it can be avoided.

Sparky · Accepted Answer · 2014-12-22 13:56:00Z

1

Since DESC is a reserved word, you would have to enclose it in double quotes.

However, I would not recommend using reserved words for fields names, perhaps change to description or something similar

answered Dec 22, 2014 at 13:56

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Comments

Sylvain Leroux · Accepted Answer · 2014-12-22 15:28:50Z

As already said several times, the error is caused here by the use of a reserved keyword unquoted as an identifier. For sake of completeness:

Oracle has an impressive list of reserved keywords.
Unquoted identifiers are internally converted upper-case by Oracle.
Quoted identifiers are case-sensitive

So:

CREATE TABLE T (DESC INT);

ORA-00904: : invalid identifier as DESC is a keyword

CREATE TABLE T (Desc INT);

ORA-00904: : invalid identifier same reason as unquoted identifiers are converted all upper-case

CREATE TABLE T ("DESC" INT);

Table created by using quotes, "DESC" is no longer recognized as a reserved keyword

INSERT INTO T("Desc") VALUES (1);

ORA-00904: "Desc": invalid identifier Quoted identifiers are case-sensitive. "DESC" is not the same columns as "Desc"

INSERT INTO T("DESC") VALUES (1);

1 row(s) inserted

That being said, you should avoid using a keyword as an identifier...

Collectives™ on Stack Overflow

Creating table via SQL Command Line, invalid identifier

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related