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 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 - The
DEFAULT
value of a column in a CREATE
TABLE
or ALTER
TABLE
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?