Thursday, June 27, 2013

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 comments:

  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

    ReplyDelete
  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

    ReplyDelete