Oracle SQL - "missing keyword"

Question

I am trying to create a simple table in Oracle SQL. The counterpart code works fine in SQL management studio, but not in Oracle APEX.

The following query:

CREATE TABLE Conference(
ConferenceID NUMBER GENERATED ALWAYS AS IDENTITY
(START WITH 100
INCREMENT BY 10
MINVALUE 100
MAXVALUE 100000
NO CYCLE),
Director VARCHAR(25) NOT NULL,
School Size NUMBER NOT NULL,
Location VARCHAR(50) NOT NULL,
CONSTRAINT pk_Conference PRIMARY KEY (ConferenceID)
);

is repeatedly met with the following error:

ORA-02000: missing ( keyword

I have done my due diligence searching for a solution to this problem here, here, and here.

I checked over the identity column section, as well as the Primary Key syntax here, and everything appears to look right. Despite this, I cannot find a solution.

did you try having the column names and table name in quotes? Also what is the version you are working on? — Illuminati, Apr 12 '19 at 18:44
1. in Oracle you need to use `varchar2`, not `varchar`. 2 `"School Size"` not `School Size`. **BUT** - DO NOT CREATE FIELDS WITH SPACES. 3. `NUMBER(X)`, not `NUMBER ` — T.S., Apr 12 '19 at 19:06

score 2 · Accepted Answer · answered Apr 12 '19 at 19:13

Okay, in the docs, take a look at the identity options for a column definition. It's a small typo in this case - the NOCYCLE option is one word; there's no space.

You have one other problem, which is that School Size is not a valid column name. There's a space in it, and it's not quoted. You could do either School_Size or "School Size". I'd recommend the first one, since double-quoted column names are case-sensitive and really annoying to use.

Edit: Also, they're technically synonyms, but Oracle recommends using VARCHAR2 instead of VARCHAR.

CREATE TABLE Conference(
    ConferenceID NUMBER GENERATED ALWAYS AS IDENTITY
    (START WITH 100
    INCREMENT BY 10
    MINVALUE 100
    MAXVALUE 100000
    NOCYCLE),
    Director VARCHAR2(25) NOT NULL,
    School_Size NUMBER NOT NULL,
    Location VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_Conference PRIMARY KEY (ConferenceID)
);

Nice catch on the VARCHAR/VARCHAR2. If I recall the documentation correctly, Oracle says use VARCHAR2 as the behavior of VARCHAR may change in a future implementation. — Brian Leach, Apr 12 '19 at 19:28

Oracle SQL - "missing keyword"

1 Answers1