Re: Sequences

In 8.1.7 I get :

  1 select z1seq.nextval, dummy from dual   2 union all
  3* select z2seq.nextval, dummy from dual SQL> /
select z1seq.nextval, dummy from dual

             *
ERROR at line 1:
ORA-02287: sequence number not allowed here

Indeed, the 8.1.7 SQL Reference, on p. 2-60, says :

"
Restrictions: You cannot use CURRVAL and NEXTVAL:
- A subquery in a DELETE, SELECT, or UPDATE statement

• A query of a view or of a materialized view
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY clause or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint Also, 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. "

What Oracle version (w/o error) where you using ?

Andre

Billy Verreynne wrote:

> "Daniel A. Morgan" <Daniel.Morgan_at_attws.com>
>
> > > Was wondering what is the reason behind sequences not generating
> > > numbers when used in a UNION. Also, why does Oracle tell you no-no and
> > > perform the SQL without any errors?