基于截断临时表的cursor取数时出现ORA-01002错误及方案验证

问题背景 #

我有一个PL/SQL函数，基于永久表与临时表的关联打开游标。每次创建新游标时，临时表会在自治事务中被截断并填充数据（临时表内容为永久表ID的子集）。操作流程：

• 创建cursor1并读取一半数据后不关闭；
• 再创建cursor2读取全部数据并关闭；
• 尝试读取cursor1剩余数据时，触发ORA-01002 fetch out of sequence错误。

该问题在Oracle 19.0.0.0.0及19.14.0.0.0版本中出现，但Oracle 11版本无此问题。猜测截断临时表会使cursor1失效，目前找到一个workaround：将truncateTempTable过程中的truncate语句替换为execute immediate 'delete from temp_table'; commit;（需commit否则会触发ORA-06519错误），替换后脚本运行正常。

官方文档支持说明 #

Oracle官方文档明确指出：当对表执行TRUNCATE操作时，会使所有基于该表的打开游标失效——因为TRUNCATE是DDL操作，会隐式提交事务，同时重置表的高水位线并使相关游标解析的执行计划失效。Oracle 11g到19c的版本迭代中，对游标失效的检测逻辑进行了强化，11g中未严格校验跨自治事务的DDL操作对主事务中游标的影响，19c补上了该校验，因此触发了原本被隐藏的错误。

另外，自治事务独立于主事务提交/回滚，在自治事务中执行TRUNCATE这类DDL操作，会直接影响主事务中已打开的游标，这完全符合Oracle对DDL操作和游标生命周期的定义。

Workaround的正确性验证 #

你采用的DELETE + COMMIT替代TRUNCATE的方案是正确的，原因如下：

• DELETE是DML操作，不会像TRUNCATE那样触发DDL级别的游标失效，只要游标未被显式关闭，仍可继续读取剩余数据；
• 由于操作在自治事务中，必须执行COMMIT才能结束自治事务，避免ORA-06519（检测到活动的自治事务）错误，这符合Oracle自治事务的要求；
• 虽然DELETE性能略低于TRUNCATE（大表场景下更明显），但你的临时表仅存储永久表ID的子集，数据量不大，性能差异可忽略。

如果后续临时表数据量变大，可考虑在主事务中维护临时表数据，避免跨自治事务的修改，或者采用会话级临时表（而非事务级）进一步优化逻辑。

内容的提问来源于stack exchange，提问作者Tomáš Záluský