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 中确实有效,但似乎被默默忽略了,因为它似乎没有做任何事情 - 尽管它可能在幕后,正如@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屋!