oracle02287,在intersect中利用oracle sequence报错_ora-02287

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;