Oracle PL/SQL 版本 12.2.0.1.0 与 12.1.0.2.0 - 使用参数立即执行 [英] Oracle PL/SQL Release 12.2.0.1.0 vs 12.1.0.2.0 - execute immediate with parameters

问题描述

DECLARE
  max_id INTEGER;
BEGIN
  SELECT MAX(ID) + 1 INTO max_id FROM MY_TABLE;

  EXECUTE IMMEDIATE 'CREATE SEQUENCE  MY_TABLE_ID  MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH ' || max_id || ' CACHE 100 NOORDER  NOCYCLE  NOPARTITION';

END;

上面给了我 ORA-00933: SQL 命令没有正确结束 在

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production

并且在

   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production

在我将执行语句更改为以下内容后，它在两个版本上都可以正常工作，没有任何错误.

After I change the execute statement to the below, it works on both versions without any errors.

 CREATE SEQUENCE  MY_TABLE_ID  MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH ' || max_id || '''

这是一个已知问题吗?

推荐答案

根据 12cR1 或 12cR2 文档.

该语句在 12cR1 中确实有效，但似乎被默默忽略了，因为它似乎没有做任何事情 - 尽管它可能在幕后，正如@XING 的演示所暗示的那样.并且在 12cR2 和 11gR2 中预计会出现错误.

The statment does work in 12cR1 but appears to be silently ignored, since it doesn't appear to do anything - though it may be under the hood, as @XING's demo suggests. And it errors are expected in 12cR2 and 11gR2.

所以这似乎是 12cR1 中未记录的(根据 MoS 搜索)错误，因为当包含无效选项时它不会导致错误.除了...... MoS中有一些点击间接将其称为有效，例如错误 23210794，其中指定选项显示为一种解决方法；相反，文档 ID 2253744.1 提到

So this seems to be an undocumented (according to a MoS search) bug in 12cR1, in that it doesn't cause an error when an invalid option is included. Except... there are a few hits in MoS that refer to it indirectly as being valid, e.g. bug 23210794 where specifying the option is shown as a workaround; conversely doc ID 2253744.1 mentions

PARTITION/NOPARTITION 关键字，不是 12.1 特性，在序列创建过程中被使用

PARTITION/NOPARTITION keyword, which is not a 12.1 feature, was used during the sequence creation

有趣的是，all_sequences 视图在 12cR1，但随后在 12cR2.(纯属推测，但也许这与分区表的自增列的内部实现有关，并不真正打算为其他序列公开.)

Interestingly the all_sequences view gained a partition_count column in 12cR1, but then lost it again in 12cR2. (Purely speculation, but perhaps this is related to the internal implementation of auto-increment columns for partitioned tables, and wan't really meant to be exposed for other sequences.)

有关更多信息，您需要向 Oracle 提出服务请求.

For more information you would need to raise a service request with Oracle.

但解决您的问题的方法是从您的语句中删除 NOPARTITION 关键字.

But the fix to your problem is to remove the NOPARTITION keyword from your statement.

这篇关于Oracle PL/SQL 版本 12.2.0.1.0 与 12.1.0.2.0 - 使用参数立即执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！