Create Table with Sequence.Nextval in Oracle

Sequence in create table?

The following statement

create table t23 ( id number default seq_001.nextval );

succeeds on Oracle 18c (demo on db<>fiddle).

but fails on Oracle 11gR2 with this error:

ORA-00984: column not allowed here

db<>fiddle uses XE editions, but I don't think that's the explanation. So it seems the exam was not "was validated against 11g Release 2 version 11.2.0.1.0" with full rigour.

Oracle join nextval from sequence

You could once select the NEXTVAL for every such insert, which increments the sequence and then use CURRVAL within the INSERT.

Let's say this is your sequence

create sequence seq START WITH 123 ;

Always specify the column names in the INSERT to avoid confusion, don't use select * from

select seq.NEXTVAL FROM DUAL;
INSERT INTO TARGET(JOB_NO,col1,col2,..) 
SELECT seq.CURRVAL, a.col1,a.col2 FROM my_source_table A, ..

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

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).

How to show sequence table in sequence folder

There are only simple explanations for the error ORA-02289: sequence does not exist when you can ad Hoc query it.

Let's list them

the sequence exists, but in a different database than your process is connected to.
the sequence exists, but in a different schema than your process is using to connect.

Solution of the former case is obvious.

In the latter case

check the schema of the sequence

select SEQUENCE_OWNER from all_sequences where sequence_name = 'XXTG_SAMPLE_HEADER_S';

check the user your process connects
grant SELECT on the sequence to the connect user

connect with the user that created the sequence and

grant select on seq_user.XXTG_SAMPLE_HEADER_S to connect_user;

Oracle sequences - what happens if a sequence generates a number that already exists in the table

It should create error. Your insertion will fail for sequence 151 and with next try the sequence will be 152 and that will succeed.

You can create a new sequence starting from the higher value of all the id in current table then select from that sequence.
Create sequence starting from 152 is:

CREATE SEQUENCE New_seq
  MINVALUE 1
  START WITH 152
  INCREMENT BY 1
  CACHE 20;

And surely you can alter your sequence to restart it from 152 as @jarlh suggested.

ALTER SEQUENCE sequencename MINVALUE 152 RESTART

Inserting into an ORACLE table that has an auto increment sequence from another table?

You put the sequence to the wrong place; should've been in select, while insert into should contain the "target" column (let's presume its name is id):

INSERT INTO address (
    id,
    streetaddress,
    city,
    province,
    postalcode
)
    SELECT
        address.NEXTVAL,
        streetaddress,
        city,
        province,
        postalcode
    FROM
        import_employee

Error you got - if everything else was OK - means that you're inserting 4 values into 5 columns, and that's a mismatch.

How to create id with AUTO_INCREMENT on Oracle?

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

Using IDENTITY for auto-generation of autonumeric field and replace sequence nextval

You can do something like this

SQL> create sequence seqemp start with 1 increment by 1 ;

Sequence created.

SQL> create table emp ( id number default seqemp.nextval , name varchar2(10) ) ;

Table created.

SQL> insert into emp ( name ) values ( 'AA' ) ;

1 row created.

SQL>  insert into emp ( name ) values ( 'AB' ) ;

1 row created.

SQL>  insert into emp ( name ) values ( 'AC' ) ;

1 row created.

SQL> select * from emp ;

        ID NAME
---------- ----------
         1 AA
         2 AB
         3 AC

You cannot replace the column by an identity because it is not supported. However you might add a new column as IDENTITY with the properties of the sequence

SQL> alter table emp add newid number generated by default as identity ( start with 1 increment by 1 ) ;
    
Table altered.

SQL> select * from emp ;

        ID NAME            NEWID
---------- ---------- ----------
         1 AA                  1
         2 AB                  2
         3 AC                  3

If you want to keep the original name:

Drop the column ID
Rename the column NEWID as ID

If you want the same order in the columns

Create a backup table with the order or the columns as you want
Drop original table
Rename backup table to original table

Of course, if you have constraints you will need to take care of them as well.