ORA-08002: sequence BOOKID.CURRVAL is not yet defined in this session

select bookid.currval from dual;
出错，ORA-08002: sequence BOOKID.CURRVAL is not yet defined in this session
非要先select nextval后才能查询currval。否则报错。

ORA-8002 When Selecting CURRVAL From Sequence [ID 1019173.102] 


--------------------------------------------------------------------------------


  Modified 09-SEP-2010     Type PROBLEM     Status PUBLISHED   






Problem Description:
--------------------


You have created the following sequence:


    CREATE SEQUENCE some_sequence
      START WITH 1000;


Later, in your code, you try to access the current value of the sequence
using the CURRVAL pseudo column:


    SELECT some_sequence.CURRVAL
    from dual;


You get the following error:


    ORA-08002: sequence SOME_SEQUENCE.CURRVAL is not yet defined in this session




Solution Description:
---------------------


The NEXTVAL function acts as a sequence initializer. This can be misleading 
since in our example when we create the sequence we START WITH 1000. This does 
not however initialize the sequence. The first call to NEXTVAL initializes the 
sequence to the START WITH value. (Note that it does NOT increment the value.) 


See the output below for a complete example:


  SQL> CREATE SEQUENCE some_sequence
    2  START WITH 1000;


  Sequence created.


  /* Use CURRVAL here before NEXTVAL and the error is thrown */
  SQL> SELECT some_sequence.CURRVAL "Value" 
    2  FROM DUAL;
  FROM DUAL
       *
  ERROR at line 2:
  ORA-08002: sequence SOME_SEQUENCE.CURRVAL is not yet defined in this session


  /* Now call NEXTVAL and initialize the sequence .. */
  SQL> SELECT some_sequence.NEXTVAL "Value"
    2  FROM DUAL;


      Value
  ---------
       1000


  /* Now we have access to the current value CURRVAL */
  SQL> SELECT some_sequence.CURRVAL "Value"
    2  FROM DUAL;


      Value
  ---------
       1000


  /* Now that the sequence has been initialized with the first call
  to NEXTVAL, the 2nd call to NEXTVAL increments as it should.. */


  SQL> SELECT some_sequence.NEXTVAL "Value"
    2  FROM DUAL;


      Value
  ---------
       1001


  SQL> SELECT some_sequence.CURRVAL "Value"
    2  FROM DUAL;


      Value
  ---------
       1001
        


Solution Explanation:
---------------------


Before you can access CURRVAL for a sequence, you must first initialize the 
sequence with NEXTVAL.




References:
-----------


Oracle Server SQL Reference, Pseudocolumns CURRVAL and NEXTVAL.




Search Words:
-------------


ORA-8002








==》
The NEXTVAL function acts as a sequence initializer. This can be misleading since in our example when we create the sequence we START WITH 1000. This does not however initialize the sequence. The first call to NEXTVAL initializes the sequence to the START WITH value. (Note that it does NOT increment the value.)


oracle是以会话来管理sequence的。
当前的值就是那个会话所具有的最终值，如果一次都没有要求返回NEXTVAL值的情况下，根本就不存在CURRVAL值



这是oracle内部管理sequence的一个机制，
例如多个会话如果一起查询currval值，如果没有一个标准就乱了。所以如果能触发oracle定义出初始值（未使用的下一个值），让oracle来管理为每个会话分配未使用下一个nextval值，就解决了当前会话的currval值的问题。
打个比喻，有3个池塘，分别每个池塘里有一条鱼，每条鱼都说自己是所在池塘里的大鱼（currval），那就没有意义，因为没有对比标准就它一条，如果它得到对比标准（oracle管理当前分配的值）：10斤上是大鱼（nextval），每个池塘的鱼就知道它自己是否是大鱼了。这个比喻不知道是否恰当。


假設有sequence seq1，currval=100
現在用戶A先執行nextval,再currval往表裡插入數據,此時currval=101;


此時另一session 用戶B直接用currval往表裡插入數據，是不是就存在問題了？有唯一約束的就出問題了;
所以DB直接限制另外的session必須先nextval=102，再插入操作

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26263620/viewspace-1408519/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26263620/viewspace-1408519/