Some 12C Features

Will try to describe some of the 12C features and will keep editing this post.

Note:

• If you want to install Oracle 12 on Windows 7, refer the steps here 
• To troubleshoot invalid common user or role name after installation, go here

Feature List

 Identity Columns:

• Also known as auto-number column. This implies that the data in the column should increase when data is inserted).
• Read article on the same here.

 Invisible Columns:

• Columns can be made invisible
• It gives one the ability to introduce a change while minimizing any negative side effects of that change.
• Read article on the same here 

With Clause:

• Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query
• Read article on the same here 

 Truncate statements with the cascade option

• We could not truncate table with foreign key constraints.
• Now in 12c, one can execute the truncate table command with the cascade option (to cascade through the child tables).
• Note: You would need to create the foreign key with the "on delete cascade" option

Columns can have a default value in place of NULL

• You can define a column to have a default value when NULL is inserted
• e.g. create table ......  product_id NUMBER ON NULL 0,.....
• So if one explicity inserts NULL, it will be populated by value 0

Multiple Index - same columns

• You can create multiple indexes (different types) on a same set of columns.
• Assume you need to change from B*tree to Bitmap or vice versa.
• You need not drop the index.
• Just make the first index invisible and make a new index.
• Conditions:
• Different types of indexes
• Different uniqueness
• Different partitions are used

 

12 C Feature - Invisible Columns

Note: Index article on some 12c features can be seen here

The name is self explanatory.
Invisible columns means just that -- columns that are invisible.
It gives one the ability to introduce a change while minimizing any negative side effects of that change.

So, in a table, if we add a column, it will show up in a "select *" query.
All the "insert into <table> values ...." queries will break.

When you add an invisible column, you won't be able to see it even when you execute a "DESC" or when you run a "select *" query.
The "insert into <table> values ...." queries will not break.
However, you will able to see it if you specifically query for it by name.

Lets have a look at an example.
We have a table "TEST_IDENTITY" which we had used to test the identity column. Refer article on identity column

Lets add an invisible column to it.
The command will be:

ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE> INVISIBLE);

As you can see below, I added a column name as INVISIBLE and it does not show up in DESC command.
It does not show up in "select *" and has no impact on the "insert into <TABLE_NAME>" as well.

But you can insert data into it and also do a select by calling it explicitly.

You can make the column visible by running the following alter command:
ALTER TABLE <TABLE_NAME> MODIFY <INVISIBLE_COLUMN_NAME> VISIBLE;

Some questions that may help:

How to know a table exists with an invisible column?

Run a query on USER_TAB_COLS
SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = '<TABLE_NAME>';

What if I create an invisible column as NOT NULL?
INSERT will fail. You need to create with DEFAULT option or use a trigger to populate on INSERT.

Is there any massive advantage of this feature?
Its a feature which could be used but not any with massive advantage. it could create problems for some applications when made visible, but if you have coded with proper INSERTs/SELECTs (mentioning columns), it won't harm your application.

12 C Feature - Identity Columns

Note: Index article on some 12c features can be seen here 
 
Identity Columns

What is an Identity column?
This is also known as auto-number column. This implies that the data in the column should increase when data is inserted.

In previous version of Oracle 12c, this was accomplished in the one of the following two ways:

• Create a sequence
• Insert the sequence.nextval in the INSERT STATEMENT

OR

• Create a sequence
• Create a Trigger "BEFORE INSERT ON TABLE"

Trigger created:
CREATE OR REPLACE TRIGGER test_trg
BEFORE INSERT ON testseq
FOR EACH ROW
BEGIN
 SELECT test_seq.nextval INTO :new.id FROM dual;
END;


In Oracle 12c, the same thing can be achieved without the use of sequence and triggers in two ways:

Column default clause (with sequences)

Create a sequence and use it as part of the table creation.
One can specify sequences (NEXTVAL or CURRVAL) as default column values. When a new row is inserted into the table, the sequence value is automatically inserted.

Look at the screen shot below where I have created a sequence and a table.
You can see that we can also put in out own data (data with value like 100 in the example or null) and it overrides the value from the sequence.

I have made the column not null, and we can see on inserting null, it throws an error.

Using GENERATED as IDENTITY in Column definition:

Here I will show example of identity columns which can be specified in the CREATE TABLE and ALTER TABLE statements.

Create a table using the IDENTITY clause.

Create table test_identity
(
  id NUMBER GENERATED as IDENTITY,
  name VARCHAR2(100)
);

When you do a describe, you shall see that the column is by default NOT NULL;

Running a query on DBA_SEQUENCES shows a new SEQUENCE created by the name ISEQ$$_92446 (system-generated name in the format ISEQ$$_<objectID> where objectID is the object id of the table)

Also note that you cannot alter an existing non-identity column to become an identity column.

Difference between "Column default clause" and "Using GENERATED as IDENTITY"