划线反馈

OceanBase 数据库 V4.x 中通过 PL cursor 去 fetch 数据时遇到了报错 ORA-01002: fetch out of sequence 的原因和解决方法

更新时间：2026-07-17 07:16

适用版本：V4.2.x、V4.3.x、V4.4.x内容类型：Troubleshoot

问题现象

在 OceanBase 数据库 V4.x 中通过 PL 代码段中的 cursor 游标去 fetch 数据时遇到了 ORA-01002: fetch out of sequence 的报错，如下图所示：

而同样的业务 PL 代码在 OceanBase 数据库 V3.x 环境中可以正常执行。

问题原因

PL 代码报错 ORA-01002: fetch out of sequence 的直接原因是在 cursor 的 open 和 fetch 中间出现了 commit 导致该 cursor 被提前关闭失效了。根本原因是因为 OceanBase 数据库 V3.x 版本和 OceanBase 数据库 V4.x 版本的处理逻辑发生了变化。OceanBase 数据库 V3.x 版本兼容原在 Oracle 的行为，对流式 cursor 与非流式 cursor 中的 commit 操作区别对待：

流式 cursor： PL 中的普通 cursor 默认是流式的。在流式 cursor open 后执行 commit，会对 cursor 进行失效和关闭。
非流式 cursor： PS cursor 默认是非流式的。在非流式 cursor open 后执行 commit，不会对 cursor 进行失效操作。

而 OceanBase 数据库 V4.x 没有区分流式 cursor 与非流式 cursor，只要在 cursor open 后执行了 commit，均会对 cursor 做失效和关闭处理。

问题的风险及影响

业务 PL 代码从 OceanBase 数据库 V3.x 切换到 V4.x 版本后执行失败。

适用版本

OceanBase 数据库 V4.2.1.x 版本。
OceanBase 数据库 V4.2.5（oceanbase-4.2.5.0-100000082024102022）及之后版本。

解决方法

方法一）在 cursor open 之前把想要提交的事务都 commit 掉，在 cursor fetch 过程中不提交

测试示例如下：

obclient [SYS]> select @@version_comment from dual;
+----------------------------------------------------------------------------------------------------------------+
| @@VERSION_COMMENT                                                                                              |
+----------------------------------------------------------------------------------------------------------------+
| OceanBase 4.2.1.11 (r111050022025071110-df2941afc2b82500fcee52fa34b65c0abd1c62dc) (Built Jul 11 2025 10:37:50) |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

obclient [SYS]> create table t1 (id int, a int);
Query OK, 0 rows affected (0.054 sec)

obclient [SYS]> insert into t1 values (1,1),(11,11);
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

obclient [SYS]> create table t2 (a int);
Query OK, 0 rows affected (0.052 sec)

obclient [SYS]>
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  commit;
  fetch c into val; -- 3.x 可以执行成功，4.x会执行失败
end;
/
ORA-01002: fetch out of sequence
at anonymous block , line : 10, col : 3
obclient [SYS]>
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  -- commit;
  fetch c into val;
end;
/
Query OK, 1 row affected (0.040 sec)

备注：如果在 cursor open 和 cursor fetch 之间发生了事务的隐式提交，默认配置下该 cursor 也会关闭失效的，因此也需要确保在 cursor open 和 cursor fetch 之间没有执行任何的DDL语句（DDL 语句会强制提交在它之前执行的所有 DML 语句）。

obclient [SYS]> select @@version_comment from dual;
+----------------------------------------------------------------------------------------------------------------+
| @@VERSION_COMMENT                                                                                              |
+----------------------------------------------------------------------------------------------------------------+
| OceanBase 4.2.1.11 (r111050022025071110-df2941afc2b82500fcee52fa34b65c0abd1c62dc) (Built Jul 11 2025 10:37:50) |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

obclient [SYS]> select * from gv$ob_parameters where name='_enable_enhanced_cursor_validation';
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| SVR_IP        | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                               | DATA_TYPE | VALUE | INFO                                                                                                                                | SECTION  | EDIT_LEVEL        |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| 11.xxx.xx.115 |     2882 | zone1 | TENANT |      1002 | _enable_enhanced_cursor_validation | NULL      | False | enable enhanced cursor validation, which let cursor can be fetched after transaction committed if it has not read uncommitted data. | OBSERVER | DYNAMIC_EFFECTIVE |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
1 row in set (0.003 sec)

obclient [SYS]> create table t1 (id int, a int);
Query OK, 0 rows affected (0.054 sec)

obclient [SYS]> insert into t1 values (1,1),(11,11);
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

obclient [SYS]> create table t2 (a int);
Query OK, 0 rows affected (0.054 sec)

obclient [SYS]>
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  execute immediate 'create table t3 (id int)';
  fetch c into val; -- 3.x 可以执行成功，4.x会执行失败
end;
/
ORA-01002: fetch out of sequence
at anonymous block , line : 10, col : 3

方法二）在 OceanBase 数据库 V4.2.1、V4.2.5+ 版本中还可以将租户级隐藏配置项 `_enable_enhanced_cursor_validation` 设置为 True 来绕过该问题

配置项名称：_enable_enhanced_cursor_validation，默认值为 False，租户级别/DYNAMIC_EFFECTIVE。

配置项描述（中文）： 是否允许 cursor 在没读取当前事务修改的表的情况下在事务结束后依然可以 fetch。

Parameter Description(In English)： whether enable cursor fetch after transaction terminated if has not access tables modified by the transaction。

功能详解：

当开关打开时，cursor open 时检查该 cursor 是否访问了事务已经修改过的表，如果没有则 cursor 在事务提交和回滚后依然可以 fetch 数据，不受事务的状态影响。
当开关关闭时，cursor open 时不检查该 cursor 是否访问了事务修改过的表，事务提交或者回滚后 cursor 将无法继续 fetch 数据，会报错 ORA-01002: fetch out of sequence。

测试示例如下：

obclient [SYS]> select @@version_comment from dual;
+----------------------------------------------------------------------------------------------------------------+
| @@VERSION_COMMENT                                                                                              |
+----------------------------------------------------------------------------------------------------------------+
| OceanBase 4.2.1.11 (r111050022025071110-df2941afc2b82500fcee52fa34b65c0abd1c62dc) (Built Jul 11 2025 10:37:50) |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

obclient [SYS]> select * from gv$ob_parameters where name='_enable_enhanced_cursor_validation';
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| SVR_IP        | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                               | DATA_TYPE | VALUE | INFO                                                                                                                                | SECTION  | EDIT_LEVEL        |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| 11.xxx.xx.115 |     2882 | zone1 | TENANT |      1002 | _enable_enhanced_cursor_validation | NULL      | False | enable enhanced cursor validation, which let cursor can be fetched after transaction committed if it has not read uncommitted data. | OBSERVER | DYNAMIC_EFFECTIVE |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
1 row in set (0.003 sec)

obclient [SYS]> create table t1 (id int, a int);
Query OK, 0 rows affected (0.054 sec)

obclient [SYS]> insert into t1 values (1,1),(11,11);
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

obclient [SYS]> create table t2 (a int);
Query OK, 0 rows affected (0.052 sec)

obclient [SYS]>
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  commit;
  fetch c into val; -- 3.x 可以执行成功，4.x会执行失败
end;
/
ORA-01002: fetch out of sequence
at anonymous block , line : 10, col : 3
obclient [SYS]>
obclient [SYS]> alter system set "_enable_enhanced_cursor_validation"=true;
Query OK, 0 rows affected (0.005 sec)

obclient [SYS]>
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  commit;
  fetch c into val;
end;
/
Query OK, 1 row affected (0.002 sec)

不过需要注意的是：即使开启了 _enable_enhanced_cursor_validation 参数，如果在 cursor open 和 cursor fetch 之间提交的 commit 操作涉及到了 cursor 已经关联的表数据，cursor fetch 仍然会报错：

obclient [SYS]> select @@version_comment from dual;
+----------------------------------------------------------------------------------------------------------------+
| @@VERSION_COMMENT                                                                                              |
+----------------------------------------------------------------------------------------------------------------+
| OceanBase 4.2.1.11 (r111050022025071110-df2941afc2b82500fcee52fa34b65c0abd1c62dc) (Built Jul 11 2025 10:37:50) |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

obclient [SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

obclient [SYS]> select * from gv$ob_parameters where name='_enable_enhanced_cursor_validation';
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| SVR_IP        | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                               | DATA_TYPE | VALUE | INFO                                                                                                                                | SECTION  | EDIT_LEVEL        |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
| 11.xxx.xx.115 |     2882 | zone1 | TENANT |      1002 | _enable_enhanced_cursor_validation | NULL      | True  | enable enhanced cursor validation, which let cursor can be fetched after transaction committed if it has not read uncommitted data. | OBSERVER | DYNAMIC_EFFECTIVE |
+---------------+----------+-------+--------+-----------+------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------+----------+-------------------+
1 row in set (0.003 sec)

obclient [SYS]> create table t1 (id int, a int);
Query OK, 0 rows affected (0.054 sec)

obclient [SYS]> insert into t1 values (1,1),(11,11);
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

obclient [SYS]> create table t2 (a int);
Query OK, 0 rows affected (0.052 sec)

-- 下面的 commit 语句提交的事务修改未涉及 cursor 已经关联的表数据
-- 因此开启 _enable_enhanced_cursor_validation 参数后，执行不会报错
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  insert into t2 values(1);
  update t2 set a=2;
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  commit;
  fetch c into val;
end;
/
Query OK, 1 row affected (0.062 sec)

-- 下面的 commit 语句提交的事务修改涉及到了 cursor 已经关联的表数据
-- 因此即使开启了 _enable_enhanced_cursor_validation 参数，还是会报错的
obclient [SYS]> declare
  val int;
  cursor c is select a from t1 where id > 10;
begin
  update t1 set a=2 where id=1;
  open c;
  fetch c into val;
  insert into t1 values(1,2);
  commit;
  fetch c into val;
end;
/
ORA-01002: fetch out of sequence
at anonymous block , line : 10, col : 3