Ora-00907...

[mitsvicious]

Hello there and I am very happy to join your community :) I am trying to create a table in oracle by typing the following simple code.
SQL> create table MY_CROPS
2 (CROP NUMBER(1) NOT NULL
3 NAME VARCHAR2(15),
4 START_OF_SEASON DATE,
5 END_OF_SEASON DATE,
6 PRIMARY KEY (CROP));

Can somebody please tell me why I keep getting that error?
ERROR at line 3:
ORA-00907: missing right parenthesis

what's wrong with my parenthesis... :(

Thank you in advance :)

[mitsvicious]

sorry for that guys, I just figured out that I am missing a comma at the second row of the code... too much studying and the brain is going to blow up... Hope my next query will be more advanced :D thanks anyway :)

[mitsvicious]

didn't take long did it? :D well I am having a problem with a foreign key now... :(

I have this table:

SQL> create table MY_CLASS
2 (TYPE NUMBER(1) NOT NULL,
3 NAME VARCHAR2(20) NOT NULL,
4 PERIOD VARCHAR2(20) NOT NULL,
5 USE VARCHAR2(20) NOT NULL,
6 PRIMARY KEY (TYPE));

and I want to create this one:

SQL> create table MY_FINDS
2 (FIND_ID NUMBER(2) NOT NULL,
3 XCOORD NUMBER(2) NOT NULL,
4 YCOORD NUMBER(2) NOT NULL,
5 DEPTH NUMBER(2,2) NOT NULL,
6 FIELDNOTES VARCHAR2(40) NOT NULL,
7 PRIMARY KEY (FIND_ID),
8 FOREIGN KEY (TYPE) REFERENCES MY_CLASS (TYPE));

when I try to run the above statement I get this error:

ERROR at line 8:
ORA-00904: "TYPE": invalid identifier

I have checked that the first table exists, what's going on?...

[gintsp]

You haven't column type in your second table.

Gints Plivna

http://www.gplivna.eu

[debasisdas]

"TYPE" is a restricted / reserved key word.

[mitsvicious]

ohhhh :) damn you are right!! Thank you very much guys!!!

[mitsvicious]

aaaalrighty... I am back with a question that bugs me for the past two freaking hours... I would be grateful if I could take some of your brains :) this is the code I write:
SQL> create table MY_CLASS
2 (CLASS NUMBER(1) NOT NULL,
3 NAME VARCHAR2(15) NOT NULL CHECK(NAME=UPPE R(NAME)),
4 PERIOD VARCHAR2(20) NOT NULL CHECK(PERIOD=UP PER(PERIOD)),
5 USE VARCHAR2(20) CHECK(USE=UPPER (USE)),
6 PRIMARY KEY (CLASS));

Table created.

SQL> create table MY_FINDS
2 (FIND_ID NUMBER(2) NOT NULL,
3 XCOORD NUMBER(2) NOT NULL,
4 YCOORD NUMBER(2) NOT NULL,
5 DEPTH NUMBER(2,2) NOT NULL,
6 FIELDNOTES VARCHAR2(40) CHECK(FIELDNOTE S=UPPER(FIELDNO TES)),
7 PRIMARY KEY (FIND_ID),
8 FOREIGN KEY (CLASS) REFERENCES MY_CLASS (CLASS));

and this is the error I get...
ERROR at line 8:
ORA-00904: "CLASS": invalid identifier

Is "class" a forbidden word too? because I ve checked the index of those words and I couldnt find it anywhere... Excuse me for my qs but I am really newbie in this and above all I have tried to fix it myself but I can't :(

[debasisdas]

SQL> create table MY_FINDS
2 (FIND_ID NUMBER(2) NOT NULL,
3 XCOORD NUMBER(2) NOT NULL,
4 YCOORD NUMBER(2) NOT NULL,
5 DEPTH NUMBER(2,2) NOT NULL,
6 FIELDNOTES VARCHAR2(40) CHECK(FIELDNOTE S=UPPER(FIELDNO TES)),
7 PRIMARY KEY (FIND_ID),
8 FOREIGN KEY (CLASS) REFERENCES MY_CLASS (CLASS));

and this is the error I get...
ERROR at line 8:
ORA-00904: "CLASS": invalid identifier

In the second table you don't have the CLASS field . How can you make that the forein key. specify name of any other existing and comartible field for FOREIGN KEY FIELD. rest all is OK.

[mitsvicious]

thank's mate... I really have to be more careful with this ...