Why is this CREATE command not running?

This is a part of a script which the whole script runs without errors. I followed the script and made minor changes, just by changing names, etc and discarded few commands like ‘create or replace force view’, sequences.

CREATE TABLE  "EMPLOYEES"
   ("EMPLOYEE_ID" NUMBER(6,0),
    "FIRST_NAME" VARCHAR2(20),
    "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
    "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
    "PHONE_NUMBER" VARCHAR2(20),
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
    "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
    "SALARY" NUMBER(8,2),
    "COMMISSION_PCT" NUMBER(2,2),
    "MANAGER_ID" NUMBER(6,0),
    "DEPARTMENT_ID" NUMBER(4,0),
        "BONUS" VARCHAR2(5),
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
     CONSTRAINT "EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX  ENABLE,
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX  ENABLE
  );

This is one of the ‘CREATE TABLE’ commands that I edited from there.

CREATE TABLE  "PRODUCT"
   ("Prod_ID" VARCHAR2(10) CONSTRAINT "Prod_ID_NN" NOT NULL ENABLE,
    "Prod_Price" NUMBER(3,2) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
    "Prod_Avail" VARCHAR2(20) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
    "Prod_MDate" DATE CONSTRAINT "Prod_MDate_NN" NOT NULL ENABLE,
    "Prod_EDate" DATE CONSTRAINT "Prod_EDate_NN" NOT NULL ENABLE,
    "Cust_ID" NUMBER(4,0),
    "Sto_ID" VARCHAR(8) CONSTRAINT "Sto_ID_NN" NOT NULL ENABLE,
    "Sup_ID" VARCHAR(10),
    CONSTRAINT "Prod_ID_PK" PRIMARY KEY ("Prod_ID")
    CONSTRAINT "Prod_Date_Interval" CHECK (Prod_EDate > Prod_MDate) ENABLE,
    USING INDEX  ENABLE
   );

Errors came out; ORA-00907: missing right parenthesis

ORA-06512: at “SYS.WWV_DBMS_SQL_APEX_210200”, line 673

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1658

ORA-06512: at “SYS.WWV_DBMS_SQL_APEX_210200”, line 659

ORA-06512: at “APEX_210200.WWV_FLOW_DYNAMIC_EXEC”, line 1829

“Prod_Avail” VARCHAR2(20) CONSTRAINT “Prod_Price_NN” NOT NULL ENABLE, “Prod_MDate” DATE CONSTRAINT “Prod_MDate_NN” NOT NULL ENABLE, “Prod_EDate” DATE CONSTRAINT “Prod_EDate_NN” NOT NULL ENABLE,

“Cust_ID” NUMBER(4,0),

“Sto_ID” VARCHAR(8) CONSTRAINT “Sto_ID_NN” NOT NULL ENABLE,

Answer

I’d suggest you to forget about double quotes while working with Oracle. Nothing good in them, only problems.

When you fix errors

• enclosing column names with mixed case into double quotes in constraints
• use unique constraint names
• rearrange using index enable

then it works:

SQL> CREATE TABLE "PRODUCT"
  2  (
  3     "Prod_ID"      VARCHAR2 (10) CONSTRAINT "Prod_ID_NN" NOT NULL ENABLE,
  4     "Prod_Price"   NUMBER (3, 2) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
  5     "Prod_Avail"   VARCHAR2 (20) CONSTRAINT "Prod_Avail_NN" NOT NULL ENABLE,
  6     "Prod_MDate"   DATE CONSTRAINT "Prod_MDate_NN" NOT NULL ENABLE,
  7     "Prod_EDate"   DATE CONSTRAINT "Prod_EDate_NN" NOT NULL ENABLE,
  8     "Cust_ID"      NUMBER (4, 0),
  9     "Sto_ID"       VARCHAR (8) CONSTRAINT "Sto_ID_NN" NOT NULL ENABLE,
 10     "Sup_ID"       VARCHAR (10),
 11     CONSTRAINT "Prod_ID_PK" PRIMARY KEY ("Prod_ID") USING INDEX ENABLE,
 12     CONSTRAINT "Prod_Date_Interval" CHECK ("Prod_EDate" > "Prod_MDate") ENABLE
 13  );

Table created.

SQL>

But – once again – I wouldn’t do it that way. How would I do it? Lke this:

SQL> CREATE TABLE product
  2  (
  3     prod_id      VARCHAR2 (10) CONSTRAINT prod_id_nn NOT NULL ENABLE,
  4     prod_price   NUMBER (3, 2) CONSTRAINT prod_price_nn NOT NULL ENABLE,
  5     prod_avail   VARCHAR2 (20) CONSTRAINT prod_avail_nn NOT NULL ENABLE,
  6     prod_mdate   DATE CONSTRAINT prod_mdate_nn NOT NULL ENABLE,
  7     prod_edate   DATE CONSTRAINT prod_edate_nn NOT NULL ENABLE,
  8     cust_id      NUMBER (4, 0),
  9     sto_id       VARCHAR (8) CONSTRAINT sto_id_nn NOT NULL ENABLE,
 10     sup_id       VARCHAR (10),
 11     CONSTRAINT prod_id_pk PRIMARY KEY (prod_id) USING INDEX ENABLE,
 12     CONSTRAINT prod_date_interval CHECK (prod_edate > prod_mdate) ENABLE
 13  );

Table created.

SQL>