ORA-00922: missing or invalid option , can't create tables

Question

I'm trying to create some tables in Oracle LiveSQL and I'm getting ORA-00922: missing or invalid option but I can't figure it out why it doesn't work; here is the code(the comments stand for the tables in the relational model).I apologize for eventual stupid mistakes, I'm a complete beginner of SQL programming.

/* Base di dati
KEYWORD(codice, nome)
UTENTE(CF, nome, cognome, email)
ANNUNCIO(codice, categoria, nome, descrizione, data_pubblicazione, data_scadenza, prezzo, comune, di_persona, tipo, CFUtente)
OFFERTA(CFutente, data, ora, importo, note, codice_annuncio)
INCLUSIONE(codice_keyword, codice_annuncio)
*/

/*
KEYWORD(codice, nome)
*/

create table keyword
(codice char(4) primary key,
nome varchar(20)
)

/*
UTENTE(CF, nome, cognome, email)
*/

create table utente
(CF char(16) primary key,
nome varchar(20),
email varchar(20)
)

/*
ANNUNCIO(codice, categoria, nome, descrizione, data_pubblicazione, 
data_scadenza, prezzo, comune, di_persona, tipo, CFUtente)
*/

create table annuncio
(codice char(10) primary key,
categoria varchar(11),check(categoria in 
('elettronica','immobili','veicoli','altro')),
nome varchar(20),
desc varchar(100),
data_pubb date,
data_scad date,
prezzo float,
comune varchar(20),
di_persona boolean,
tipo varchar(8),check(tipo in ('vendita','acquisto')),
CFutente char(16) references utente(CF),
check((tipo = 'vendita' AND di_persona is null) or (tipo = 'acquisto' and 
prezzo is null and comune is null))
)

/*
OFFERTA(CFutente, data, ora, importo, note, codice_annuncio)
*/

create table offerta
(CFutente char(16) references utente(CF),
data date,
ora time,
importo float,
note varchar(100),
codice_annuncio char(10) references annuncio(codice),
primary key(CFutente,data,ora),
check(CFutente <> (SELECT CFutente FROM annuncio WHERE 
codice=codice_annuncio))
)

/*
INCLUSIONE(codice_keyword, codice_annuncio)
*/

create table inclusione
(codice_keyword char(4) references keyword(codice),
codice_annuncio char(10) references annuncio(codice),
primary key(codice_keyword,codice_annuncio)
)

`char` is [never a useful datatype in Oracle](https://stackoverflow.com/a/42165653/230471). Also, while `float` is perfectly valid, it always seems to me just an obscure way of specifying `number` so personally I have never used it or seen the point of it. Also, foreign key columns inherit the datatype of their parent so you don't need to repeat it, e.g. just `CFutente references utente(CF)`. — William Robertson, Aug 30 '21 at 12:44

MT0 · Accepted Answer · 2021-08-27T21:48:40.437

You need to terminate each statement with ;
Oracle does not have a boolean SQL data type. You can use di_persona NUMBER(1) CHECK (di_persona IN (1, 0)) instead.
desc is a reserved word and cannot be used as an identifier; if it as an abbreviation for "description" then you can use descr instead.
You cannot use a subquery in a CHECK constraint.
Oracle does not have a time data type. You can use INTERVAL DAY(0) TO SECOND instead; or, if data and ora are supposed to be a combined date and time then just use the data column as the DATE data type always has year, month, day, hour, minute and second components so you do not need a separate time column.

create table keyword(
  codice char(4) primary key,
  nome varchar(20)
);

create table utente(
  CF char(16) primary key,
  nome varchar(20),
  email varchar(20)
);

create table annuncio(
  codice char(10) primary key,
  categoria varchar(11),
  check(categoria in ('elettronica','immobili','veicoli','altro')),
  nome varchar(20),
  descr varchar(100),
  data_pubb date,
  data_scad date,
  prezzo float,
  comune varchar(20),
  di_persona NUMBER(1) CHECK (di_persona IN (1, 0)),
  tipo varchar(8),check(tipo in ('vendita','acquisto')),
  CFutente char(16) references utente(CF),
  check(
    (tipo = 'vendita' AND di_persona is null)
    or (tipo = 'acquisto' and prezzo is null and comune is null)
  )
);

create table offerta(
  CFutente char(16) references utente(CF),
  data date,
  ora INTERVAL DAY(0) TO SECOND,
  importo float,
  note varchar(100),
  codice_annuncio char(10) references annuncio(codice),
  primary key(CFutente,data,ora)
);

db<>fiddle here

ORA-00922: missing or invalid option , can't create tables

1 Answers1