Oracle sequences: CURRVAL not allowed here?

Question

The following Oracle SQL code generates the error "ORA-02287: sequence number not allowed here":

INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT * FROM Customer where CustomerID=Customer_Seq.currval;

The error occurs on the second line (SELECT statement). I don't really understand the problem, because this does work:

INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT Customer_Seq.currval from dual;

Answer 1

You don't say what version of Oracle you are using. There have in the past been limitations on where sequences can be used in PL/SQL - mostly if not all gone in 11G. Also, there are restrictions in SQL - see this list.

In this case you may need to write:

SELECT Customer_Seq.currval INTO v_id FROM DUAL; 
SELECT * FROM Customer where CustomerID=v_id;

(Edited after comments).

Answer 2

You may not use a sequence in a WHERE clause - it does look natural in your context, but Oracle does not allow the reference in a comparison expression.

[Edit]

This would be a PL/SQL implementation:

declare
v_custID number;
cursor custCur is
  select customerid, name from customer
   where customerid = v_custID;
begin
select customer_seq.nextval into v_custID from dual;
insert into customer (customerid, name) values (v_custID, 'AAA');
commit;
for custRow in custCur loop
 dbms_output.put_line(custRow.customerID||' '|| custRow.name); 
end loop;
end;

Answer 3

You have posted some sample code, so it is not clear what you are trying to achieve. If you want to know the assigned value, say for passing to some other procedure you could do something like this:

SQL> var dno number
SQL> insert into dept (deptno, dname, loc)
  2      values (deptno_seq.nextval, 'IT', 'LONDON')
  3      returning deptno into :dno
  4  /

1 row created.

SQL> select * from dept
  2  where deptno = :dno
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        55 IT             LONDON

SQL>

Edit

We can use the RETURNING clause to get the values of any column, including those which have been set with default values or by trigger code.

Answer 4

This doesn't really directly answer your question, but maybe what you want to do can be resolved using a the INSERT's RETURNING clause?

DECLARE
  -- ...
  last_rowid rowid;
  -- ...
BEGIN
  -- ...
  INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA') RETURNING rowid INTO last_rowid;
  SELECT * FROM Customer where rowid = last_rowid;
  -- ...
END;
/