"ORA-00984: column not allowed here" when attempting to create table using SQL

Question

I am attempting to run this script to create a table, however I get a column not allowed error. After doing some research it seems that it could be that it could be a syntax error concerning values, however I am not inserting any values.

CREATE TABLE SALESPERSON (
 sales_id     VARCHAR2(10) PRIMARY KEY,
 sales_fname  VARCHAR2(35) NOT NULL,
 sales_lname  VARCHAR2(35) NOT NULL,
 sales_email  VARCHAR2(35) NOT NULL,
 sales_region VARCHAR2(35) NOT NULL CHECK(sales_region IN ('NORTH','SOUTH','EAST','WEST')),
 sales_phone  CHAR(10)  NOT NULL,
 hire_date    DATE DEFAULT 01-JAN-2001 NOT NULL);

What am I overlooking?

score 0 · Answer 1 · answered Oct 18 '16 at 14:50

0

Put quotes on your default date:

hire_date    DATE DEFAULT '01-JAN-2001' NOT NULL);

answered Oct 18 '16 at 14:50

msturek

541
6
17

It's better not use a string as a date this way; a better way could be using to_date – Aleksej Oct 18 '16 at 15:07

score 0 · Accepted Answer · answered Oct 18 '16 at 14:51

0

I am getting a different error message, but probably due to the same mistake. You can't set the default to 01-JAN-2001. Perhaps just putting it in single quotes will fix it; better, to_date('01-JAN-2001', 'DD-MON-YYYY').

answered Oct 18 '16 at 14:51

This solved the problem. – Maher Jacob Oct 18 '16 at 15:05

"ORA-00984: column not allowed here" when attempting to create table using SQL

2 Answers2