使用序列的错误ORA-02287

今天一个开发的同事问我一个问题，说在执行一条sql语句的时候报了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的使用，大家一般都认为是取之不尽，用之不竭，感觉大量使用时应该的，在很多时候还是需要好好斟酌一下，有些补丁或者临时的处理是否一定需要使用到序列，序列资源也是很宝贵的资源，如果在测试脚本中做了大量的自增处理也是很大的浪费。

本文永久更新链接地址：