Identity Columns in Oracle 12c

If you are familiar with the database platforms mostly found on Windows platforms, you're know that  you can set a default value for a column. This is commonly referred to as an Identity column. This feature was not available in Oracle, until the release of 12c. Let's see how it's done.

0) set your container (yes, I've been playing with Pluggable Databases).

NOTE: before we go any farther, you will need two roles assigned to create a table with an identity column:

• CREATE TABLE 
• CREATE SEQUENCE

When you thought about it, that was pretty obvious, wasn't it.

1) create table, with a column defined like so:

2) Insert some data like so:

NOTE: these statements will cause errors still:

The first statement fails, because the order of columns is not specified even though we have a default value specified for the identity column. Identity columns are traditionally the first column, but they don't have to be.

The second and third statement fails because we can not specify the value of an identity column set with GENERATED ALWAYS parameter.

3) When we select from the table, we see that the ID_COL has values:

Because this is new functionality, you can see the metadata for these objects with the following views:

• *_TAB_IDENTITY_COLS
• *_OBJECTS

sources:
https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402
http://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php