Re: anyone seen this weirdness with sequences in 11gr2?

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 16:51:01 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Real Application Clusters, Automatic Storage Management and OLAP options

SQL> create sequence c_seq nocache;

Sequence created.

SQL> select c_seq.nextval from dual;

   NEXTVAL

         1

SQL> select c_seq.nextval from dual;

   NEXTVAL

         2

SQL> drop sequence c_seq;

Sequence dropped.

SQL> create sequence c_seq nocache;

Sequence created.

SQL> select c_seq.nextval from dual;

   NEXTVAL

         1

SQL> drop sequence c_seq;

Sequence dropped.

SQL> create sequence c_seq nocache;

Sequence created.

SQL> create table x(x number);

Table created.

SQL> insert into x(x) values(c_seq.nextval);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x;

         X

         1

SQL> On Thu, Apr 8, 2010 at 4:38 PM, <TESTAJ3_at_nationwide.com> wrote:

>
> Start with 1 shows same number, insert 2 into the table.
>
> go figure, i'm thinking bug also, oh wait "undocumented feature" :)
>
> joe
>
> _______________________________________
> Joe Testa, Oracle Certified Professional
> Senior Engineering & Administration Lead
> (Work) 614-677-1668
> (Cell) 614-312-6715
>
>
>
>
>
> From: Robert Freeman <robertgfreeman_at_yahoo.com> To:
> TESTAJ3_at_nationwide.com Cc: Oracle-L Freelists <oracle-l_at_freelists.org>
> Date: 04/08/2010 05:27 PM Subject: Re: anyone seen this weirdness with
> sequences in 11gr2? Sent by: oracle-l-bounce_at_freelists.org
> ------------------------------
>
>
>
> Try to create the sequence using the start with syntax, does it do the same
> thing? I tried this on 11.2 also and got the same results. Sounds like a
> bug to me.
>
> I do notice that if you do a select c_seq.nextval from dual; on the second
> sequence creation that is seems to work, which is weird.
>
> It's a bug I'm betting.
>
> RF
>
> Robert G. Freeman
> Master Principal Consultant, Oracle Corporation
> Oracle ACE
> Author:
> Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY
> SOON!
> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
> (Sybex)
> Oracle Database 11g New Features (Oracle Press)
> Oracle Database 10g New Features (Oracle Press)
> Other various titles
> Blog: *http://robertgfreeman.blogspot.com*<http://robertgfreeman.blogspot.com/>
>
>
> ------------------------------
> *From:* "TESTAJ3_at_nationwide.com" <TESTAJ3_at_nationwide.com>*
> To:* oracle-l_at_freelists.org*
> Sent:* Thu, April 8, 2010 2:22:08 PM*
> Subject:* anyone seen this weirdness with sequences in 11gr2?
>
>
> This works correctly in 11gr1:
>
> SQL> drop sequence c_seq;
>
> Sequence dropped.
>
> SQL> create sequence c_seq nocache;
>
> Sequence created.
>
> SQL> select c_seq.nextval from dual;
>
> NEXTVAL
> ----------
> 1
>
> SQL>
> SQL> drop sequence c_seq;
>
> Sequence dropped.
>
> SQL> create sequence c_seq nocache;
>
> Sequence created.
>
> SQL> drop table x;
>
> Table dropped.
>
> SQL> create table x(x number);
>
> Table created.
>
> SQL> insert into x(x) values(c_seq.nextval);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from x;
>
> X
> ----------
> 2
>
>
> The question is why from the c_seq.nextval WITHIN an insert do I get back a
> 2 instead of a 1 like if i did it OUTSIDE of an insert.
>
> this works correctly in 11gr1.
>
> thanks, joe
>
> _______________________________________
> Joe Testa, Oracle Certified Professional
> Senior Engineering & Administration Lead
> (Work) 614-677-1668
> (Cell) 614-312-6715
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l