Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: Sequence question

    I will cut the long story short and try to keep it simple. Let's look at the code below:

    create sequence seq1;

    select seq1.nextval, seq1.currval, seq1.nextval, seq1.nextval+1
    from dual;

    As a programmer, I am expecting the answer of: 1 1 2 3.

    However, Oracle9i spits out 1 1 1 2.

    Is that right? Or is that a bug in the system?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you cannot select nextval and nextval from the same select statement (as you see in your example).

    So you are basically doing a currval when you have 2 nextvals in your statement. I believe this is because Oracle needs to hold that value for you to use.

    As you see in my example below, you cannot gather two sequence numbers from the same sequence in a single statement (ie: sequence not allowed here).

    PHP Code:
    12:03:00 kod-platformselect seq1.nextval
    seq1.currvalseq1.nextvalseq1.nextval+
    12
    :03:00   2  from dual;

       
    NEXTVAL    CURRVAL    NEXTVAL SEQ1.NEXTVAL+1
    ---------- ---------- ---------- --------------
             
    1          1          1              2

    Elapsed
    00:00:00.00
    12
    :03:00 kod-platform
    12:03:00 kod-platform>  select seq1.nextval,
     
    seq1.currvalseq1.nextvalseq1.nextval+1 from dual;

       
    NEXTVAL    CURRVAL    NEXTVAL SEQ1.NEXTVAL+1
    ---------- ---------- ---------- --------------
             
    2          2          2              3

    12
    :03:20 kod-platformselect seq1.nextval
    seq1.currvalseq1.nextval+1, (select seq1.nextval from dualfrom dual;

    select seq1.nextvalseq1.currval
    seq1.nextval+1, (select seq1.nextval from dualfrom dual
                                                                    
    *
    ERROR at line 1:
    ORA-02287sequence number not allowed here

    Elapsed
    00:00:00.00 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Posts
    12

    Thumbs up

    Thanks for the quick reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •