CODEWITHSUNDEEP
sqloracle-databaseoracle11g
J. Donič
J. Donič

Reputation: 163

Oracle: ORA-00984: column not allowed here

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?

Upvotes: 1

Views: 784

Answers (2)

shrek
shrek

Reputation: 887

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);

Upvotes: 3

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 2

Related Questions