环境
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> CREATESEQUENCE 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是不区分对象
名大小写的。
完整日志
[sql]view plaincopy
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 andReal Application Testing options
SQL> CREATESEQUENCE seq_WGB_Test
2 MINVALUE 0
3 INCREMENT BY 1
4 START WITH 1000;
CREATESEQUENCE 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> CREATESEQUENCE 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>
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-6-26 15:24:49 |
赞(
操作
