ORA-00922 : Cannot Create table in oracle using ojdbc

Question

I want to create the following table

CREATE TABLE EMPLOYEE (
    name_id varchar(255) PRIMARY KEY,
    modified_at TIMESTAMP,
    created_at TIMESTAMP
);

but oracle give the error

ORA-00922: missing or invalid option

I also tried the following SQL request but same issue is happening

CREATE TABLE EMPLOYEE (
    name_id varchar(255),
    modified_at TIMESTAMP,
    created_at TIMESTAMP,
    CONSTRAINT EMPLOYEE UNIQUE (name_id)
);

The request where executed using java code and the driver ojdbc6-11.2.0.4.jar

is there anything wrong in my sql ?

[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f94b6c613bdc16ae650bd0cd0bf32046) Cannot replicate the error for your first query. I'm not sure what you are attempting to do with the second query but the constraint is not valid syntax as you have not told it what type of constraint you are trying to create. — MT0, Mar 19 '21 at 00:30
you are right, I edited the second request it had a problem, but I want to say is the both tables represents the same thing for my application, executing the sql directly to oracle works fine. However doing it using java code gives me the mentioned error — octopus, Mar 19 '21 at 00:37
Have you removed the semi-colon from the end of the statement when you run it via JDBC? — MT0, Mar 19 '21 at 00:38
I've updated my answer - you should just need to remove the trailing semi-colon. — MT0, Mar 19 '21 at 00:52
I'm confused. Your question says "What is wrong in my Sql" but the comments indicate your SQL is fine and the real question should be "what is wrong in how my code calls the SQL?" Can you add more content to indicate how you're calling the SQL via the code? — Atmas, Mar 19 '21 at 01:09
See [sql - “ORA-00922: missing or invalid option” when trying to insert into table - Stack Overflow](https://stackoverflow.com/questions/30818416/ora-00922-missing-or-invalid-option-when-trying-to-insert-into-table/30818459) — , Mar 19 '21 at 01:55
Probably a duplicate of https://stackoverflow.com/a/10728434/1509264 — MT0, Mar 19 '21 at 01:57

MT0 · Accepted Answer · 2021-03-19T00:47:12.167

CREATE TABLE EMPLOYEE (
   name_id varchar(255) PRIMARY KEY,
   modified_at TIMESTAMP,
   created_at TIMESTAMP
);

CREATE TABLE EMPLOYEE (
  name_id varchar(255),
  modified_at TIMESTAMP,
  created_at TIMESTAMP,
  CONSTRAINT EMPLOYEE UNIQUE (name_id)
);

is there anything wrong in my sql ?

No, it works fine db<>fiddle.

The only two things you could change are to:

use VARCHAR2 and not VARCHAR.
name the constraint something different to the name of the table.

CREATE TABLE EMPLOYEE (
    name_id     VARCHAR2(255),
    modified_at TIMESTAMP,
    created_at  TIMESTAMP,
    CONSTRAINT EMPLOYEE__name_id__u UNIQUE (name_id)
);

The request where executed using java code and the driver ojdbc6-11.2.0.4.jar

Remove the semi-colon at the end if you are running the statement via JDBC. A semi-colon is a statement terminator in SQL/Plus (and SQL developer) but is not necessary (and is a syntax error) when you are using JDBC and similar (when you will only ever pass a single statement and so the terminator is unnecessary).

You are right, after removing the semi-colon it works, thanks — octopus, Mar 21 '21 at 21:47
The final SQL that worked is the following ```CREATE TABLE EMPLOYEE ( ID VARCHAR(255), LAST_MODIFIED TIMESTAMP, CREATED TIMESTAMP, PRIMARY KEY (ID) )``` — octopus, Mar 21 '21 at 21:49

score 0 · Answer 2 · answered Mar 19 '21 at 00:47

Instead of the inline pk declaration you can use something like this:

CREATE TABLE EMPLOYEE (
   name_id varchar(255) PRIMARY KEY,
   modified_at TIMESTAMP,
   created_at TIMESTAMP
)
ALTER TABLE EMPLOYEE
ADD CONSTRAINT employee_pk PRIMARY KEY (name_id);

ORA-00922 : Cannot Create table in oracle using ojdbc

2 Answers2