---
title: "OceanBase 数据库 V4.x 中通过 PL cursor 去 fetch 数据时遇到了报错 ORA-01002: fetch out of sequence 的原因和解决方法-OceanBase数据库使用指南"
description: "了解OceanBase数据库在实际应用中关于OceanBase 数据库 V4.x 中通过 PL cursor 去 fetch 数据时遇到了报错 ORA-01002: fetch out of sequence 的原因和解决方法相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 V4.x 中通过 PL cursor 去 fetch 数据时遇到了报错 ORA-01002: fetch out of sequence 的原因和解决方法的难题。"
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# 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` 的报错，如下图所示：

![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/PL/20250917when-fetching-data-using-pl-cursor-ob4x-encountered-error-ora01002-fetch-out-sequence.png)

而同样的业务 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 过程中不提交

测试示例如下：

```shell
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 * 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 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]>
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 * 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)

-- 下面的 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

```

## 规避方式

无。

上一篇

[DBMS_PROFILER 存储过程调优工具](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002356139)

下一篇

[OceanBase 数据库 V4.x 版本中存储过程调用 UTL_FILE.FOPEN_I 报错 ORA-29283: invalid file operation: too many files open](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002356014) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线