ORA-08002: 序列 SEQ_WGB_TEST2.CURRVAL 尚未在此会话中定义

小咪咪 2022-02-23 11:14 219阅读 0赞

### 环境 ###

Oracle 11.2.0 + SQL Plus

### 问题 ###

查询Sequence的当前值出现以下错误：

SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;
    
    SELECT seq_WGB_Test2.CURRVAL FROM dual
    
           *
    第 1 行出现错误:
    
    ORA-08002: 序列 SEQ_WGB_TEST2.CURRVAL 尚未在此会话中定义

### 解决 ###

首先创建一个测试序列：

SQL> CREATE SEQUENCE seq_WGB_Test2
      2  INCREMENT BY 1
      3  START WITH 1000;
    
    序列已创建。

然后查询当前值：

SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;
    SELECT seq_WGB_Test2.CURRVAL FROM dual
           *
    第 1 行出现错误:
    ORA-08002: 序列 SEQ_WGB_TEST2.CURRVAL 尚未在此会话中定义

调用NEXTVAL后再次查询当前值：

SQL> SELECT seq_WGB_Test2.NEXTVAL FROM dual;
    
       NEXTVAL
    ----------
          1000
    
    SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;
    
       CURRVAL
    ----------
          1000
    
    SQL>

**创建Sequence后直接查询它的当前值（CURRVAL）会出错，要先调用Sequence对象.NEXTVAL，才能查询当前值。注意：Oracle是不区分对象名大小写的。**

### 完整日志 ###

Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
    
    C:\Users\Wentasy>sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on 星期三 5月 8 18:49:34 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    请输入用户名:  wgb
    输入口令:
    
    连接到:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> CREATE SEQUENCE seq_WGB_Test
      2  MINVALUE 0
      3  INCREMENT BY 1
      4  START WITH 1000;
    CREATE SEQUENCE seq_WGB_Test
                    *
    第 1 行出现错误:
    ORA-00955: 名称已由现有对象使用
    
    
    SQL> select * from tab;
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    ADMIN                          TABLE
    BIN$3nsCzoq0TWOFAF7FNWp2Ww==$0 TABLE
    BIN$6nilOleNQvqabmvq1Zg+xA==$0 TABLE
    BIN$UITFwn98Tgymdh0eN9itmQ==$0 TABLE
    BIN$lMXf63gRRYScHIKIQowBtQ==$0 TABLE
    BIN$nrOJ+rQPQC2q5p78nwGiiA==$0 TABLE
    BIN$q+6u6SPaR8q8tuTSUD9qgA==$0 TABLE
    BIN$qNbqiRwcT+Gy44xHHER59Q==$0 TABLE
    CATEGORY                       TABLE
    COMMONS                        TABLE
    CONTACTOR                      TABLE
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    COUNTRY                        TABLE
    DEPT                           TABLE
    EMP                            TABLE
    ENROLLMENT                     TABLE
    FACES                          TABLE
    MEMBER                         TABLE
    MESSAGE                        TABLE
    PRODUCT                        TABLE
    STUDENT                        TABLE
    TBBILL                         TABLE
    TBPERSON                       TABLE
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    TBSUIT                         TABLE
    TBUSERTBSUIT                   TABLE
    TEST                           TABLE
    T_ADDRESS                      TABLE
    T_COURSE                       TABLE
    T_HIBERNATE_USER               TABLE
    T_ITEM                         TABLE
    T_MONEY_USER                   TABLE
    T_ORDER                        TABLE
    T_STUDENT                      TABLE
    T_USER                         TABLE
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    T_USER2                        TABLE
    
    已选择34行。
    
    SQL> select * from seq;
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
    
    ------------------------------ ---------- ---------- ------------ - - ----------
    
    LAST_NUMBER
    -----------
    HIBERNATE_SEQUENCE                      1 1.0000E+28            1 N N         20
    
              1
    
    SEQ_FOR_TEST                            1 1.0000E+28            1 N N         20
    
              5
    
    SEQ_WGB_ADMIN                           1 1.0000E+28            1 N Y          0
    
             20
    
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
    
    ------------------------------ ---------- ---------- ------------ - - ----------
    
    LAST_NUMBER
    -----------
    SEQ_WGB_COMMON                          1 1.0000E+28            1 N N         20
    
              1
    
    SEQ_WGB_COMMONS                         1 1.0000E+28            1 N N         20
    
              9
    
    SEQ_WGB_CONTACTOR                       1 1.0000E+28            1 N Y          0
    
             28
    
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
    
    ------------------------------ ---------- ---------- ------------ - - ----------
    
    LAST_NUMBER
    -----------
    SEQ_WGB_FACES                           1 1.0000E+28            1 N N         20
    
             12
    
    SEQ_WGB_MEMBER                          1 1.0000E+28            1 N N         20
    
              7
    
    SEQ_WGB_MESSAGE                         1 1.0000E+28            1 N N         20
    
              6
    
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
    
    ------------------------------ ---------- ---------- ------------ - - ----------
    
    LAST_NUMBER
    -----------
    SEQ_WGB_MONEY_USER                      1 1.0000E+28            1 N N         20
    
              8
    
    SEQ_WGB_TEST                            1 1.0000E+28            1 N N         20
    
            242
    
    
    已选择11行。
    
    SQL> CREATE SEQUENCE seq_WGB_Test2
      2  INCREMENT BY 1
      3  START WITH 1000;
    
    序列已创建。
    
    SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;
    SELECT seq_WGB_Test2.CURRVAL FROM dual
           *
    第 1 行出现错误:
    ORA-08002: 序列 SEQ_WGB_TEST2.CURRVAL 尚未在此会话中定义
    
    
    SQL> SELECT seq_WGB_Test2.NEXTVAL FROM dual;
    
       NEXTVAL
    ----------
          1000
    
    SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;
    
       CURRVAL
    ----------
          1000
    
    SQL>

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>