Another New Features 12c : generated as identity / Sequence Replacement

in old version of oracle database if you want to create automatic generated number you have to create sequence and use attribute nextval.

But with oracle database 12c this concept is changed new features add when you create table called generated as identity.

Check the Below Demonstration which explain this new features :

SQL> create table test (test_id number generated as identity , test_name varchar2(20));

SQL> desc test ;
 Name   Null?      Type
 —————————————–   ——– ————–
 TEST_ID   NOT      NULL NUMBER
 TEST_NAME          VARCHAR2(20)

SQL> insert into test values (1,’osama’);
insert into test values (1,’osama’)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

The TEST_ID Column will be inserting automatically  no need to use in insert command.

SQL> insert into test (TEST_NAME) values ( ‘Jennifer’ );
1 row created.

SQL> select * from test ;
   TEST_ID TEST_NAME
———- ——————–
1       Jennifer 

Check another example :

SQL> create table test2 (TEST_ID NUMBER generated as identity (start with 1 increment by 1 cache 30 order), TEST_NAME varchar2(20));
Table created.

SQL> insert into test2 (test_name) values (‘JENNIFER’);
SQL> insert into test2 (test_name) values (‘STEVE’);
SQL> insert into test2 (test_name) values (‘USER’);

SQL> select * from test2 ;

   TEST_ID TEST_NAME
———- ——————–
1 JENNIFER
2 STEVE
3 USER

Thank you 
Osama mustafa

2 thoughts on “Another New Features 12c : generated as identity / Sequence Replacement

  1. Hi theгe і am κаѵin, its my first οccasiοn tο commentіng anywhегe, whеn i rеad this ρаrаgraph i thοught i coulԁ also create comment duе to this gοod piеce of wrіting.Fееl free to νisіt my wеb blοg: tarot

    Like

  2. Thanks fοr your personаl mаrѵelous poѕting!I certaіnly enјoуеd readіng it, you're a great author.I will make certain to booκmark your blog and wіll comе back in the future.I want to encourage you to definitely continue your great writing, haѵe a nice holiday weekend!My blog post videncia

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.