{"id":197,"date":"2013-06-27T06:52:03","date_gmt":"2013-06-27T04:52:03","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=197"},"modified":"2020-08-18T16:57:31","modified_gmt":"2020-08-18T14:57:31","slug":"oracle-database-12c-sequence-nextval-as-default-and-identity-columns","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-sequence-nextval-as-default-and-identity-columns\/","title":{"rendered":"Oracle Database 12c: sequence.nextval as default and identity columns"},"content":{"rendered":"<p>Finally! I can count how many times I&#8217;ve seen questions<a href=\"http:\/\/stackoverflow.com\/questions\/10613846\/create-table-with-sequence-nextval-in-oraclehttp:\/\/\" target=\"_blank\" rel=\"noopener noreferrer\"> like this one<\/a>.<\/p>\n<p>The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:<\/p>\n<pre class=\"toolbar-overlay:false lang:default decode:true\">SQL&gt; create sequence foo_seq;\r\n\r\nSequence created.\r\n\r\nSQL&gt; create table foo (\r\n2 id number default foo_seq.nextval,\r\n3 bar varchar2(50) not null,\r\n4 constraint foo_pk primary key (id)\r\n5 );\r\n\r\nTable created.\r\n\r\nSQL&gt; insert into foo (bar) values ('baz');\r\n\r\n1 row created.\r\n\r\nSQL&gt; insert into foo (bar) values ('test');\r\n\r\n1 row created.\r\n\r\nSQL&gt; insert into foo (bar) values ('whoo');\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\nSQL&gt; select * from foo;\r\n\r\nID BAR\r\n---------- --------------------------------------------------\r\n1 baz\r\n2 test\r\n3 whoo<\/pre>\n<p>&nbsp;<\/p>\n<p>But Oracle has fixed this twice, in the new release it&#8217;s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:<\/p>\n<pre class=\"toolbar-overlay:false lang:pgsql decode:true\">SQL&gt; create table bar (\r\n2 id number generated as identity,\r\n3 foo varchar2(50) not null,\r\n4 constraint bar_pk primary key (id)\r\n5 );\r\n\r\nTable created.\r\n\r\nSQL&gt; insert into bar (foo) values ('baz');\r\n\r\n1 row created.\r\n\r\nSQL&gt; insert into bar (foo) values ('test');\r\n\r\n1 row created.\r\n\r\nSQL&gt; insert into bar (foo) values ('whoo');\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\nSQL&gt; select * from bar;\r\n\r\nID FOO\r\n---------- --------------------------------------------------\r\n1 baz\r\n2 test\r\n3 whoo<\/pre>\n<p>I&#8217;ve said &#8220;explicitly&#8221; because actually a sequence is created with a system-generated name, so you&#8217;ll still need to deal with sequences.<\/p>\n<pre class=\"toolbar-overlay:false lang:pgsql decode:true\">TABLE_NAME TABLE_TYPE\r\n------------------------------- -----------\r\nISEQ$$_23657 SEQUENCE<\/pre>\n<p>&nbsp;<\/p>\n<p>cheers<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Finally! I can count how many times I&#8217;ve seen questions like this one. The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition: SQL&gt; create sequence foo_seq; Sequence created. SQL&gt; create &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-sequence-nextval-as-default-and-identity-columns\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[336,326,3,52,132],"tags":[78,82,80,22,75,81],"class_list":["post-197","post","type-post","status-publish","format-standard","hentry","category-devops","category-oracle","category-oracledb","category-12c","category-triblog","tag-howto","tag-identity","tag-new-features","tag-oracle-database","tag-oracle-database-12c","tag-sequences"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=197"}],"version-history":[{"count":17,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/197\/revisions"}],"predecessor-version":[{"id":1987,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/197\/revisions\/1987"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}