Home » Oracle 12c » 3 NEW Best Features Of CREATE TABLE Command Introduced In Oracle 12c

3 NEW Best Features Of CREATE TABLE Command Introduced In Oracle 12c

Describing 3 new useful Create Table command enhancements introduced in Oracle Database 12c. These new features were really required to improve the coding and performance.

1. Assign Default Values To Columns Using Sequence

You can now assign sequence number as default value to a particular column in Oracle 12c to generate unique value for every row. The following is the example given to generate serial number for every record inserted into EMP table:
Create Sequence Seq_Srl_No start with 1 increment by 1;
Create Table Emp (
  Emp_no Integer Constraint Pk_Emp_No Primary Key,
  Emp_Name Varchar2(100),
  Emp_Srl_No Integer Default Seq_Srl_No.nextval 
  );

2. Generate Automatically Default Unique Number Using IDENTITY

In Oracle 12c, we can generate unique number value for a column without using sequence object, by specifying Identity keyword in Create Table Command. The following is the example given for Emp table to generate unique number which starts with 10 and increment by 2 for every new record;
Create Table Emp (
  Emp_no Integer Constraint Pk_Emp_No Primary Key,
  Emp_Name Varchar2(100),
 Emp_Srl_No Integer GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 2));

3. VISIBLE And INVISIBLE Columns In Tables

You can define Invisible columns in a Table using Create Table command in Oracle database 12c. By default every column is visible when we create a table in Oracle, but now in Oracle 12c you can specify Invisible keyword to hide a column. In the below example, a Credentials table is created with the hidden password column:
CREATE TABLE Credentials (
username varchar2(30),    
password varchar2(30) INVISIBLE
 );
   
When we describe this table using DESCRIBE command to see its structure the following would be the result:
SQL> DESC CREDENTIALS
Name                          Null?    Type  
---------------------------------------- -------- ----------------------------
USERNAME                    VARCHAR2(30) 
The PASSWORD column is hidden from the above result, so if you want to see that column you must set the COLINVISIBLE to on:
SET COLINVISIBLE ON;
Now you would be able to see that hidden column using describe command:
SQL> DESC CREDENTIALS
Name                           Null?    Type
---------------------------------------- -------- ----------------------------
USERNAME                       VARCHAR2(30)

PASSWORD (INVISIBLE)                   VARCHAR2(30)
Note: You must specify that column specifically in DML statements to process it else it would be not considered and my through an error. The following example shows if we insert directly to CREDENTIALS Table without specifying this PASSWORD column:
SQL> INSERT INTO CREDENTIALS VALUES ('SCOTT', 'TIGER'); 
INSERT INTO CREDENTIALS VALUES ('SCOTT', 'TIGER')
* ERROR at line 1: 
ORA-00913: too many values 
However we can insert by specifying value only for USERNAME column because PASSWORD COLUMN is nullable:
SQL> INSERT INTO CREDENTIALS VALUES ('SCOTT'); 
1 row created.
So if we want to insert a value to password column also then we must specify in insert statement, check the following example:
SQL> INSERT INTO CREDENTIALS (USERNAME, PASSWORD) VALUES ('SCOTT', 'TIGER'); 
1 row created.
Oracle 12c SQL new features and enhancements