{"id":31,"date":"2009-01-30T10:53:16","date_gmt":"2009-01-30T10:53:16","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=31"},"modified":"2016-05-10T10:54:39","modified_gmt":"2016-05-10T10:54:39","slug":"looking-forward-to-11g-sequence-expressions-in-plsql","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=31","title":{"rendered":"Looking forward to 11g sequence expressions in PL\/SQL"},"content":{"rendered":"<p>Although behind the scenes Oracle will perform a &#8220;select <sequence>.nextval from dual&#8221; and therefore really doesn&#8217;t improve performance or anything I&#8217;m looking forward to write 11g PL\/SQL code where you can use the sequence pseudo-columns CURRVAL and NEXTVAL in expressions. First off, I always prefer to use the <sequence>.NEXTVAL in the SQL statement itself and use the RETURNING INTO clause to assign the new value to a PL\/SQL variable. BUT, if the case requires for some reason to get the sequence value in PL\/SQL without going through a SQL statement first just imagine how beautiful and elegant your code will look like without having &#8220;select <sequence>.netxval into <some varialbe> from dual;&#8221; spread all over the place&#8230;<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; create sequence seq;\r\n\r\n-- the 10g and prior way\r\nSQL&gt; declare\r\n  nextval number;\r\nbegin\r\n  select seq.nextval\r\n  into nextval\r\n  from dual;\r\nend;\r\n\/\r\n\r\n-- the 11g way\r\nSQL&gt; declare\r\n  nextval number;\r\nbegin\r\n  nextval := seq.nextval;\r\nend;\r\n\/\r\n<\/pre>\n<p>You can basically use the sequence pseudo-columns everywhere where a number value is valid in the expression, e.g. &#8220;&#8230;if seq.currval > 10 then&#8230;&#8221;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Although behind the scenes Oracle will perform a &#8220;select .nextval from dual&#8221; and therefore really doesn&#8217;t improve performance or anything I&#8217;m looking forward to write 11g PL\/SQL code where you can use the sequence pseudo-columns CURRVAL and NEXTVAL in expressions. First off, I always prefer to use the .NEXTVAL in the SQL statement itself and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-31","post","type-post","status-publish","format-standard","hentry","category-plsql"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/31","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=31"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/31\/revisions"}],"predecessor-version":[{"id":33,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/31\/revisions\/33"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}