Kayshav.com
About Developer Oracle 12c Oracle 19c Technology Information Sitemap

Oracle Table - New Features
-- 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
);


  1. Oracle 12c Table creation with ROW ARCHIVAL feature

  2. Analyze Table Details

  3. Auto number Trigger 11g syntax Details

Oracle 12c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: June 27th, 2016

  11053