使用序列的错误ORA

今天一个开发的同事问我一个问题,说在执行一条sql语句的时候报了ORA错误,脑海中删除了各种权限的问题之后,他提供给我的错误还是在我预料之外。
ERROR at line 1:
 ORA-02287: sequence number not allowed here

这个问题看错误信息是很明显和sequence有关的。但是为什么会报出这个错误呢,在几封邮件交流之后,问题就明朗起来,语句是类似下面这样的结构,insert into customer(xxxxx,xxxxx,xxx...............)
 select distinct xxxxx,seq_value.nextval,xxxx,xxxx,xxx... from new_table group by xxx,xxx,xxx,xxx;
真实的脚本洋洋洒洒一大页,各种子查询,表关联,函数处理,看起来还是需要些耐心的。简缩之后就是上面的结构,
 这个Insert采用了子查询来插入数据,根据和开发的沟通,是需要提供一个补丁,做修复数据所用,所以会有大量的数据过滤操作。
 插入数据的时候使用seq_value.nextval也还是很常见的,怎么会报出错误了呢,按照这个语句的结构发现还是最开头的distinct和group操作导致的,这种数据的统计分析操作让本来就不确定的sequence值更加不确定,所以斟酌再三还是建议他们先创建一个临时表,不涉及序列的操作,只对序列之外的数据通过distinct,group by过滤之后,在insert的时候对刚创建的临时表和序列结合起来,一次插入。

伪代码类似下面的形式,
create table temp_tab as select distinct xxxxxx  from xxxx  group by xxxxxx;
 insert into customer(xxxxx,xxxx) select xxxx,seq_vvalue.nextval from temp_tab;

我们来简答模拟一下这个问题。
 首先为了不影响原有的sequence,我们可以创建一个类似的sequence,然后在脚本中替换即可,这样也不会对原有环境的sequence值造成影响。
CREATE SEQUENCE  "NEW_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1012852 CACHE 20 NOORDER  NOCYCLE
然后我们创建一个表
create table new_test( id1 number,id2 number,name varchar2(30));
然后尝试distinct和group by 操作,发现都不可以。
n1@TEST11G> select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name;
 select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name
                *
 ERROR at line 1:
 ORA-02287: sequence number not allowed here

n1@TEST11G> select distinct new_seq.nextval,id1,id2,name from new_test;
 select distinct new_seq.nextval,id1,id2,name from new_test
                        *
 ERROR at line 1:
 ORA-02287: sequence number not allowed here
其实这个问题的原因还是很容易理解的,这种sequence值的动态不确定性,很容易出问题。其实不光使用distinct,group by 会有问题,很多相关的操作都是如此。
 比如union,union all
 select new_seq.nextval,id1,id2,name from new_test
 union all
 select new_seq.nextval,id1,id2,name from new_test

minus操作。
select new_seq.nextval,id1,id2,name from new_test
 minus
 select new_seq.nextval,id1,id2,name from new_test

使用In子查询
select new_seq.nextval id1,id2,name from new_test  where id1 in (select new_seq.nextval from new_test )

order by操作
select new_seq.nextval,id1,id2,name from new_test order by id2;

换个角度来看,对于这类问题,也可以使用临时表来处理,也可以使用pl/sql来处理,都能达到比较目的,另外一个角度来说,对于sequence的使用,大家一般都认为是取之不尽,用之不竭,感觉大量使用时应该的,在很多时候还是需要好好斟酌一下,有些补丁或者临时的处理是否一定需要使用到序列,序列资源也是很宝贵的资源,如果在测试脚本中做了大量的自增处理也是很大的浪费。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/13c543c57703605dd604c28ad9216bd9.html