在intersect中利用oracle sequence報錯_ora-02287

select  chapter_id,chapter_name,version_name,version_id,subjectid,parent_id
     from c_chapter_info
      
       union all
    
    select lpad(seq_only_maxid.nextval,16,'0') chapter_id,a.*
    from (
          select *
          from (
             select part,version,version_id,subjectid,chapter_id from tmp_content where part is not null
               )
         ) a
             

 

 

 

 

 

 

 

 

ORA-02287: sequence number not allowed here

原文出處：
What causes this error?
An ORA-02287 occurs when you use a sequence where it is not allowed. The usage of a sequence is limited and it can be used only in few areas of PL/SQL and coding.

Where can one use sequences
The following are the cases where you can't use a sequence:

For a SELECT Statement:

In a WHERE clause
In a GROUP BY or ORDER BY clause
In a DISTINCT clause
Along with a UNION or INTERSECT or MINUS
In a sub-query
Other areas:

A sub-query of Update or Delete
In a View or snapshot
In a DEFAULT or CHECK Condition of a table definition
Within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

How to fix it
If your statement fits one of the above said conditions, then you would see this error being raised. I have some possible solutions:

If you need the sequence value for a WHERE clause or a sub-query or order by, then fetch the sequence value into a variable and use the variable in the necessary places.
If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column
If you want to use a sequence in a view, then first create the view with all other columns and conditions without the sequence and then use the sequence when you actually type in "select a, b, c from view".
If you want to use a sequence in a select query with distinct clause, then you can use distinct in sub query and sequence.nextval in main query as follows -
insert into av_temp(col1, col2)
select t.col1, shift_allocation_seq.nextval
from (select distinct col1 from av_temp1) t;