Hello guys,
In Managing Sequences it says:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm
Uses and Restrictions of NEXTVAL and CURRVAL
CURRVAL and NEXTVAL can be used in the following places:
VALUES clause of INSERT statements- The
SELECT list of a SELECT statement - The
SET clause of an UPDATE statement
Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:
- 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 GROUPBY clause or ORDERBY 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 - The
DEFAULT value of a column in a CREATETABLE or ALTERTABLE statement - The condition of a
CHECK constraint
____________________________________________________________________________________________________
--i was trying to do something like this:
CREATE SEQUENCE random_seq
START WITH 500
INCREMENT BY 1
CACHE 10;
CREATE SEQUENCE round_seq
INCREMENT BY 1
NOCACHE;
INSERT INTO departments(department_id,department_name,manager_id)
VALUES(round_seq.NEXTVAL, 'Department Round_seq', 100);
INSERT INTO employees(employee_id, last_name, email, hire_date)
VALUES(random_seq.NEXTVAL,'Lalo', 'lalo@', SYSDATE);
--until here, everything was fine.
UPDATE employees
SET department_id = round_seq.CURRVAL
WHERE employee_id = random_seq.CURRVAL;
/* and here i got:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.*/
--then i tried
UPDATE employees
SET department_id = round_seq.CURRVAL
WHERE employee_id = 500;
and it was good.
_____________________________________________________________________________
So i'm guessing we cant use a pseudocolumns(CURRVAL or NEXTVAL) anywhere in a WHERE clause. Is that right?