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

环境

 

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----------      1000SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;   CURRVAL----------      1000SQL>

 

 

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

 

完整日志

Microsoft Windows [Version 6.1.7601]Copyright (c) 2009 Microsoft Corporation.  All rights reserved.C:\Users\Wentasy>sqlplusSQL*Plus: Release 11.2.0.1.0 Production on 星期三 5月 8 18:49:34 2013Copyright (c) 1982, 2010, Oracle.  All rights reserved.请输入用户名:  wgb输入口令:连接到:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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                          TABLEBIN$3nsCzoq0TWOFAF7FNWp2Ww==$0 TABLEBIN$6nilOleNQvqabmvq1Zg+xA==$0 TABLEBIN$UITFwn98Tgymdh0eN9itmQ==$0 TABLEBIN$lMXf63gRRYScHIKIQowBtQ==$0 TABLEBIN$nrOJ+rQPQC2q5p78nwGiiA==$0 TABLEBIN$q+6u6SPaR8q8tuTSUD9qgA==$0 TABLEBIN$qNbqiRwcT+Gy44xHHER59Q==$0 TABLECATEGORY                       TABLECOMMONS                        TABLECONTACTOR                      TABLETNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------COUNTRY                        TABLEDEPT                           TABLEEMP                            TABLEENROLLMENT                     TABLEFACES                          TABLEMEMBER                         TABLEMESSAGE                        TABLEPRODUCT                        TABLESTUDENT                        TABLETBBILL                         TABLETBPERSON                       TABLETNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------TBSUIT                         TABLETBUSERTBSUIT                   TABLETEST                           TABLET_ADDRESS                      TABLET_COURSE                       TABLET_HIBERNATE_USER               TABLET_ITEM                         TABLET_MONEY_USER                   TABLET_ORDER                        TABLET_STUDENT                      TABLET_USER                         TABLETNAME                          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          1SEQ_FOR_TEST                            1 1.0000E+28            1 N N         20          5SEQ_WGB_ADMIN                           1 1.0000E+28            1 N Y          0         20SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE------------------------------ ---------- ---------- ------------ - - ----------LAST_NUMBER-----------SEQ_WGB_COMMON                          1 1.0000E+28            1 N N         20          1SEQ_WGB_COMMONS                         1 1.0000E+28            1 N N         20          9SEQ_WGB_CONTACTOR                       1 1.0000E+28            1 N Y          0         28SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE------------------------------ ---------- ---------- ------------ - - ----------LAST_NUMBER-----------SEQ_WGB_FACES                           1 1.0000E+28            1 N N         20         12SEQ_WGB_MEMBER                          1 1.0000E+28            1 N N         20          7SEQ_WGB_MESSAGE                         1 1.0000E+28            1 N N         20          6SEQUENCE_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          8SEQ_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----------      1000SQL> SELECT seq_WGB_Test2.CURRVAL FROM dual;   CURRVAL----------      1000SQL>

 

 

 

 @Wentasy 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]