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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Bayleigh.

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

Answered by: Connor McDonald - Last updated: July 30, 2020 - 12:15 am UTC

Category: SQL*Plus - 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 we 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 !


and you rated our response

  (1 rating)

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

Reviews

Yet the error is not getting resolved

July 29, 2020 - 8:58 am UTC

Reviewer: DHRUV BHANDERI from India

I did the necessary changes you told.. But still error continues
Connor McDonald

Followup  

July 30, 2020 - 12:15 am UTC

Post your entire test case and output