Oracle: ORA-00984: column not allowed here

Question

I've got a problem with my database. I have created table:

CREATE TABLE TOURNAMENT
  (
    TOURNAMENT_ID   INTEGER NOT NULL,
    DATE            DATE NOT NULL,
    CITY            VARCHAR2 (100 CHAR) NOT NULL,
    PRIZEPOOL       NUMBER (18,2) NOT NULL,
    COUNTRY         VARCHAR2 (100 CHAR) NOT NULL,
    ARENA           VARCHAR2 (100 CHAR),
    SHOP_ID         INTEGER,
    PRODUCTION_ID   INTEGER NOT NULL
  );

ALTER TABLE TOURNAMENT ADD CONSTRAINT TOURNAMENT_PK PRIMARY KEY ( TOURNAMENT_ID ) ;

And when I try to insert a row with:

prompt Tabulka TOURNAMENT

insert into TOURNAMENT (TOURNAMENT_ID, DATE, CITY, PRIZEPOOL, COUNTRY, ARENA, SHOP_ID, PRODUCTION_ID)
values (1, 08-OCT-2015, 'Moscow', 15000, 'Russia', NULL, 1, 3);

I get an error message:

ORA-00984: column not allowed here

Does anybody know what this could mean?

Firstly, you cannot have a column named DATE in a table, rename this to something like TOURNAMENT_DATE — shrek, Jun 05 '18 at 22:02
To explain the error message, the parser is rejecting the word `OCT` in your `values` clause. It has interpreted it as a column name (similar to https://stackoverflow.com/q/10178292/230471), and column names are not allowed in the `values` clause of `insert`. — William Robertson, Jun 06 '18 at 08:03

score 3 · Accepted Answer · answered Jun 05 '18 at 22:04

This should work -

CREATE TABLE TOURNAMENT
  (
    TOURNAMENT_ID   INTEGER NOT NULL ,
    TOURNAMENT_DATE       DATE NOT NULL ,
    CITY       VARCHAR2 (100 CHAR) NOT NULL ,
    PRIZEPOOL   NUMBER (18,2) NOT NULL ,
    COUNTRY        VARCHAR2 (100 CHAR) NOT NULL ,
    ARENA       VARCHAR2 (100 CHAR) ,
    SHOP_ID   INTEGER ,
    PRODUCTION_ID INTEGER NOT NULL
  ) ;
ALTER TABLE TOURNAMENT ADD CONSTRAINT TOURNAMENT_PK PRIMARY KEY ( TOURNAMENT_ID ) ;



insert into TOURNAMENT (TOURNAMENT_ID, TOURNAMENT_DATE, CITY, PRIZEPOOL, COUNTRY, ARENA, SHOP_ID, PRODUCTION_ID) values (1, TO_DATE('08-OCT-2015','DD-MON-YYYY'), 'Moscow', 15000, 'Russia', NULL, 1, 3);

score 2 · Answer 2 · answered Jun 05 '18 at 22:01

You are writing a date value the wrong way; one way could be with ANSI format:

INSERT INTO TOURNAMENT(
                       TOURNAMENT_ID,
                       DATE_,
                       CITY,
                       PRIZEPOOL,
                       COUNTRY,
                       ARENA,
                       SHOP_ID,
                       PRODUCTION_ID
                      )
     VALUES (
             1,
             DATE '2015-10-08',
             'Moscow',
             15000,
             'Russia',
             NULL,
             1,
             3
            );

Or, if you want to keep the format you showed, with a to_date:

to_date('08-OCT-2015', 'DD-MON-YYYY')

Besides, DATE is a reserved word, you'd better avoid it to name table columns.

Thank you. Actually I didn't have 'DATE' there. All the variables were named in my slavic language, but since i wanted you to understand what i'ts supposed to do, I translated the whole table :) — J. Donič, Jun 05 '18 at 22:19

Oracle: ORA-00984: column not allowed here

2 Answers2