Create Table / Sequence / Trigger in Apex Oracle SQL - ORA-00922 / ORA-00907 / ORA-00922

Question

Using: Application Express 18.1.0.00.45

Please note: I am very new to Oracle Apex and SQL. For a project I have to create an Application straight in Apex.

I was trying to create a table that works with a Primary Key that auto-increments itself.

Yesterday I created my first Application with a page for user input in a table, a page with table display and filter option and was playing around with forms, dashboard and authentication methods. I removed the app after playing to start the "Real Work", but I my enthusiasm went quickly away when I realized that I am doing something very wrong but am not sure what.. :)

After lots of googling / reading etc, I am still not sure what is wrong..

See below the code:

-- Create Sequence
CREATE SEQUENCE seq_odm_for_pk
    START WITH 1
    INCREMENT BY 1
    CACHE 100; 


-- Create Table for User Input
CREATE TABLE ODM_Progress_v1 (
    -- General Details:
    ID int NOT NULL AUTO_INCREMENT, 
    TRAINEE varchar(50) NOT NULL, --1
    COACH varchar(50) NOT NULL, --2
    STATUS varchar(50) NOT NULL, --3
    REGION varchar(5) NOT NULL, --4
    -- Actions:
    ACTION_TAKEN varchar(100) NOT NULL, --5
    ACTION_DETAILS varchar(250), --6
    ACTIONED_BY varchar(50) NOT NULL, --7
    ACTIONED_DATE DATE NOT NULL, --8
    -- Constraints that perform checks for each column:
    CONSTRAINT CHK_GeneralDetails CHECK (TRAINEE!=COACH AND (STATUS IN('New', 'In Progress', 'Completed')) AND (REGION IN('EMEA', 'APAC', 'AMER'))),
    -- Set Primary Key (Trainee+Coach):
    CONSTRAINT PK_ODMProgress PRIMARY KEY (TRAINEE,REGION,ID)
); 


-- Create Trigger
CREATE trigger_for_pk_odm_progress
  BEFORE INSERT ON ODM_Progress_v1
  FOR EACH ROW
  WHEN (new.ID is null)
BEGIN
  select seq_odm_for_pk.nextval into :new.ID from DUAL;
  -- :new.PK_ODMProgress := seq_odm_for_pk.nextval;
END;

The script finishes running with 3 errors, see below:

CREATE OR REPLACE SEQUENCE seq_odm_for_pk START WITH 1
INCREMENT BY 1 CACHE 100

ORA-00922: missing or invalid option

CREATE TABLE ODM_Progress_v1 ( -- General Details: ID int NOT NULL AUTO_INCREMENT, TRAINEE varchar(50) NOT NULL, --1 COACH varchar(50) NOT NULL, --2 STATUS varchar(50) NOT NULL, --3
REGION varchar(5) NOT NULL, --4 -- Actions: ACTION_TAKEN varchar(100) NOT NULL, --5 ACTION_DETAILS varchar(250), --6
ACTIONED_BY varchar(50) NOT NULL, --7 ACTIONED_DATE DATE NOT NULL, --8 -- Constraints that perform checks for each column: CONSTRAINT CHK_GeneralDetails CHECK (TRAINEE!=COACH AND (STATUS IN('New', 'In Progress', 'Completed')) AND (REGION IN('EMEA', 'APAC', 'AMER'))), -- Set Primary Key (Trainee+Coach): CONSTRAINT PK_ODMProgress PRIMARY KEY (TRAINEE,REGION,ID) )

ORA-00907: missing right parenthesis

CREATE OR REPLACE trigger_for_pk_odm_progress BEFORE INSERT ON ODM_Progress_v1 FOR EACH ROW WHEN (new.ID is null) BEGIN SELECT seq_odm_for_pk.nextval INTO :new.ID FROM DUAL; -- :new.PK_ODMProgress := seq_odm_for_pk.nextval; END;

ORA-00922: missing or invalid option

Can you please help me unravel this (to me, complete) mystery?

The final application should at least contain 1 table with primary key and sequence (created from scratch, see above) and have at least 2 pages, one is for data input, the other is for data display with use tabs or navigation menu.

Thank you in advance!

Answer 1

That code looks OK, more or less. Here you go:

SQL> CREATE SEQUENCE seq_odm_for_pk
  2      START WITH 1
  3      INCREMENT BY 1
  4      CACHE 100;

Sequence created.

Table: I'm on 11g which doesn't support auto-incremented columns, so I removed that clause:

SQL> CREATE TABLE ODM_Progress_v1 (
  2      -- General Details:
  3      ID int NOT NULL AUTO_INCREMENT,
  4      TRAINEE varchar(50) NOT NULL, --1
  5      COACH varchar(50) NOT NULL, --2
  6      STATUS varchar(50) NOT NULL, --3
  7      REGION varchar(5) NOT NULL, --4
  8      -- Actions:
  9      ACTION_TAKEN varchar(100) NOT NULL, --5
 10      ACTION_DETAILS varchar(250), --6
 11      ACTIONED_BY varchar(50) NOT NULL, --7
 12      ACTIONED_DATE DATE NOT NULL, --8
 13      -- Constraints that perform checks for each column:
 14      CONSTRAINT CHK_GeneralDetails CHECK (TRAINEE!=COACH AND (STATUS IN('New', 'In Progress', 'Completed')) AND (REG
ION IN('EMEA', 'APAC', 'AMER'))),
 15      -- Set Primary Key (Trainee+Coach):
 16      CONSTRAINT PK_ODMProgress PRIMARY KEY (TRAINEE,REGION,ID)
 17  );
    ID int NOT NULL AUTO_INCREMENT,
                    *
ERROR at line 3:
ORA-00907: missing right parenthesis


SQL> l3
  3*     ID int NOT NULL AUTO_INCREMENT,
SQL> c/auto_increment//
  3*     ID int NOT NULL ,
SQL> /

Table created.

Trigger contains an error in line #1: it is not "trigger_for" but "trigger for" (no underscore):

SQL> CREATE trigger_for_pk_odm_progress
  2    BEFORE INSERT ON ODM_Progress_v1
  3    FOR EACH ROW
  4    WHEN (new.ID is null)
  5  BEGIN
  6    select seq_odm_for_pk.nextval into :new.ID from DUAL;
  7    -- :new.PK_ODMProgress := seq_odm_for_pk.nextval;
  8  END;
  9  /
CREATE trigger_for_pk_odm_progress
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL> l1
  1* CREATE trigger_for_pk_odm_progress
SQL> c/er_/er /
  1* CREATE trigger for_pk_odm_progress
SQL> l
  1  CREATE trigger for_pk_odm_progress
  2    BEFORE INSERT ON ODM_Progress_v1
  3    FOR EACH ROW
  4    WHEN (new.ID is null)
  5  BEGIN
  6    select seq_odm_for_pk.nextval into :new.ID from DUAL;
  7    -- :new.PK_ODMProgress := seq_odm_for_pk.nextval;
  8* END;
SQL> /

Trigger created.

SQL>

So:

• sequence is OK, but - for vast majority of cases - a simple create sequence seq_odm_for_pk; is enough
• for CREATE TABLE remove AUTO_INCREMENT (if you aren't on 12c)
• trigger: remove underscore

Now, depending on where you executed those commands, you might have got errors. If you ran them in Apex SQL Workshop, run them one-by-one (and keep only one command in the window). Doing so, it should be OK.

Also, I've noticed that you used VARCHAR datatype - switch to VARCHAR2.

Finally, there's no use in constraining primary key columns with the NOT NULL clause - primary key will enforce it by default.

As of Apex itself: the way you described it, you should create an Interactive Report; the Wizard will create a report (to view data), along with a form (to insert/modify/delete data).

Answer 2

The query you ran in your script is not the same as you posted in your code, as can be read in error text.

Code for creating your sequence as you wrote it in your code should be fine:

CREATE SEQUENCE seq_odm_for_pk
    START WITH 1
    INCREMENT BY 1
    CACHE 100; 

As of 11g you cannot use AUTO_INCREMENT in Oracle when creating table. It is not even necessary since you're having a trigger populating your :new.ID with nextval from sequence. So, in your CREATE TABLE remove AUTO_INCREMENT for your ID and everything should be fine.

While creating a trigger you omitted TRIGGER keyword (CREATE OR REPLACE TRIGGER trigger_for_pk_odm_progress). Also, I'm not sure if you did or did not put END; at the end of your create trigger command. if not, put it.

I hope that helped :)