-- In previous versions of Oracle, when a table is created, the primary key (example 
-- below - edw_sales_id) had to have a sequence and a trigger to get the next ID 
-- value (sequence.NEXTVAL) for auto creation of primary key. In Oracle 12c the same 
-- can be achieved as shown below.  

-- The column can also be made invisible (tax_id), that contain private information.

-- The table name and column name can be 128 characters long.

-- The VARCHAR2 column can have a max size of 32767 (from 4000).
-- To set the max size to 32767, following has to be performed

-- 1. Start Oracle in Upgrade mode
-- 2. Login as SYSDBA
-- 3. Set the parameter MAX_STRING_SIZE to EXTENDED
-- 4. Execute $OH/rdbms/admin/utl32k.sql
-- 5. Start the database

ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE;

CREATE TABLE edw_sales_fact
(edw_sales_id     NUMBER GENERATED BY DEFAULT AS IDENTITY
                   (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
 division_id      NUMBER,
 invoice_id       NUMBER,
 tax_id           VARCHAR2(30) INVISIBLE,
 quantity         NUMBER,
 sales_amt        NUMBER,
 sales_desc       VARCHAR2(32000),
 invoice_date     DATE DEFAULT TO_DATE(20140101,'yyyymmdd'),
 created_date     DATE DEFAULT SYSDATE NOT NULL
);

-- The above table can also be created using sequence as shown below

CREATE SEQUENCE seq_edw_sales_id START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE TABLE edw_sales_fact
(edw_sales_id     NUMBER DEFAULT seq_edw_sales_id.NEXTVAL PRIMARY KEY,
 division_id      NUMBER,
 invoice_id       NUMBER,
 tax_id           VARCHAR2(30) INVISIBLE,
 quantity         NUMBER,
 sales_amt        NUMBER,
 sales_desc       VARCHAR2(32000),
 invoice_date     DATE DEFAULT ON NULL TO_DATE(20140101,'yyyymmdd'),
 created_date     DATE DEFAULT SYSDATE NOT NULL
);

-- Test script:

INSERT INTO edw_sales_fact
(division_id,
 invoice_id,
 tax_id,
 quantity,
 sames_amt)
SELECT
 level,
 level*10,
 'A'||LPAD(level,5,'0'),
 level*2.5,
 level*2.5*12.5
FROM DUAL
CONNECT BY level <=5;

COMMIT;

-- Previous version database tables can be enhanced to use auto numbering by ALTER
-- command.  Before executing this, the trigger used for auto numbering should be
-- dropped or disabled.

ALTER TABLE table_name MODIFY (
  key_column_id  DEFAULT  sequence_name.NEXTVAL
);