5

I entered the following SQL commands in Oracle but it complained "ORA-00922: missing or invalid option"

CREATE TABLE Student (
    StuID     NUMBER(15),
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    PRIMARY KEY (StuID))

CREATE TABLE Program (
    ProCode       VARCHAR2(12),
    ProTitle      VARCHAR2(50),
    PRIMARY KEY (ProCode))

WHY???

gdoron
  • 147,333
  • 58
  • 291
  • 367
sc1013
  • 1,068
  • 2
  • 16
  • 24
  • Did you try create only one table at a time to narrow the problem? Maybe it's because you're missing `;` between those to `create table` statements? – gdoron Mar 25 '12 at 07:53
  • No. Adding the semicolons gets error like "invalid character" – sc1013 Mar 25 '12 at 08:00
  • 1
    @Steven Odd, gives exact same error on my Oracle, adding semicolons makes it work. http://sqlfiddle.com/#!4/87f1e – Joachim Isaksson Mar 25 '12 at 08:05
  • Maybe you allready have this table defined? try replace `CREATE TABLE` with `CREATE OR REPLACE TABLE` – gdoron Mar 25 '12 at 08:06
  • @gdoron I'm sure no tables were previously defined >_ – sc1013 Mar 25 '12 at 08:11
  • 2
    @gdoron: there is no create or replace table in Oracle. I agree with Joachim Isaksson: it's the missing `;` at the end of the statements. –  Mar 25 '12 at 08:13
  • NO. adding the `;` gets error! "invalid character" The problem is that if I run the statements one by one and it's fine; but failed if I run them the same time! why? – sc1013 Mar 25 '12 at 08:16
  • 2
    @Steven: which tool do you use to run this? If it's the dreaded HTML GUI (inside the browser) of OracleXE then that does not support running more than one statement. Use SQL Developer or any other GUI tool instead. –  Mar 25 '12 at 08:23
  • @a_horse_with_no_name OH WTF! I'm using this! – sc1013 Mar 25 '12 at 08:30

3 Answers3

9

If you are using the dreaded HTML GUI (inside the browser) of OracleXE then that does not support running more than one statement.

Use SQL Developer, SQL*Plus or any other GUI tool instead.

6
CREATE TABLE Student (
    StuID     NUMBER(15),
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    CONSTRAINT PK_STUID PRIMARY KEY (StuID))

Found the answer here.

Edit:

Also, try using / as a statement separator, instead of a ;

Machavity
  • 30,841
  • 27
  • 92
  • 100
egrunin
  • 24,650
  • 8
  • 50
  • 93
1

Try no to define the size of StuID. also add constraint key work and just to make sure use DROP before CREATE like this:

DROP TABLE Student;

CREATE TABLE Student (
    StuID     NUMBER,
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    constraint pk_Student PRIMARY KEY (StuID));

DROP TABLE Program;

CREATE TABLE Program (
    ProCode       VARCHAR2(12),
    ProTitle      VARCHAR2(50),
    constraint pk_Program PRIMARY KEY (ProCode));
Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
  • 1
    try to copy exactly what I wrote? because it's working for me.. I change the start to CREATE OR REPLACE TABLE , thanks to @gdoron – Dor Cohen Mar 25 '12 at 08:12
  • If I create the tables one by one it is OK but when I create them at the same time it gets failed again – sc1013 Mar 25 '12 at 08:14
  • You fell in my mistake... [read this](http://stackoverflow.com/q/1008248/601179). Sorry. – gdoron Mar 25 '12 at 08:15