Skip to Main Content
  • Questions
  • SQL ERROR, ORA-00984: column not allowed here

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Bayleigh.

Asked: March 30, 2017 - 3:31 am UTC

Last updated: July 30, 2020 - 12:15 am UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

I'm taking a course using SQL for the first time and while following my instructors instructions exactly as he told us to, I keep getting the error that a "column is not allowed here".

I created a table called Employee like this:

CREATE TABLE EMPLOYEE (FNAME CHAR(10), MINIT CHAR(1), LNAME CHAR(10), SSN CHAR(9) PRIMARY KEY, BDATE DATE, ADDRESS CHAR(30), SEX CHAR(1), SALARY NUMBER(5), SUPERSSN CHAR(9), DNO NUMBER(1));


And then tried to to insert values in several different ways.
First I tried like this:

INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', '123456789', 09-JAN-65, '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5);


I had the column issue with that so then I tried like this, as mentioned in a document the Professor provided us:
INSERT INTO EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO VALUES ('John', 'B', 'Smith', '123456789', 09-JAN-65, '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5));



And lastly, this way as well.
INSERT INTO EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('John', 'B', 'Smith', '123456789', 09-JAN-65, '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5);


Any way I tried to fix this it kept saying column is not allowed here. What am I doing wrong?

and Connor said...

It is your *date* string.

Without quotes, the express 09-JAN-65 is being interpreted as:

"Nine minus the column JAN minus 65"

and you don't have a column called JAN hence the issue.

SQL> CREATE TABLE EMPLOYEE (FNAME CHAR(10), MINIT CHAR(1), LNAME CHAR(10),
  2    SSN CHAR(9) PRIMARY KEY, BDATE DATE, ADDRESS CHAR(30), SEX CHAR(1),
  3    SALARY NUMBER(5), SUPERSSN CHAR(9), DNO NUMBER(1));

Table created.

SQL>
SQL> INSERT INTO EMPLOYEE VALUES ('John', 'B',
  2  'Smith', '123456789',
  3  09-JAN-65,
  4  '731 Fondren, Houston, TX',
  5  'M', 30000, '333445555', 5) ;
09-JAN-65,
   *
ERROR at line 3:
ORA-00984: column not allowed here


SQL>
SQL> INSERT INTO EMPLOYEE VALUES ('John', 'B',
  2  'Smith', '123456789',
  3  to_date('09-JAN-65','dd-mon-rr'),
  4  '731 Fondren, Houston, TX',
  5  'M', 30000, '333445555', 5) ;

1 row created.


A simple to_Date is all you need.

And by the way - thank you for your nice simple test case explaining the problem. You'd be amazed how many people with many more years of experience fail to do that. They could learn from you !


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yet the error is not getting resolved

DHRUV BHANDERI, July 29, 2020 - 8:58 am UTC

I did the necessary changes you told.. But still error continues
Connor McDonald
July 30, 2020 - 12:15 am UTC

Post your entire test case and output