Oracle sequences: CURRVAL not allowed here?
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;
Error on the second line (SELECT statement). I don't really understand the problem because this works:
INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT Customer_Seq.currval from dual;
source to share
You posted some sample code, so it is not clear what you are trying to achieve. If you want to know the assigned value, say to jump to some other procedure, you can 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 that were set with default values or with startup code.
source to share
You are not saying which version of Oracle you are using. In the past, there have been limitations on where sequences can be used in PL / SQL - mostly, if not all, in 11G. Also, there are limitations 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).
source to share
It doesn't really answer your question directly, but perhaps what you want to do can be solved with the INSERT 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; /
source to share
You cannot use a sequence in a WHERE clause - it looks natural in your context, but Oracle does not allow reference in a comparison expression.
[change]
This would be the 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;
source to share
You have not created any
sequence
First create any sequence of your loop and cache. This is one of the main examples
Create Sequence seqtest1
Start With 0 -- This Is Hirarchy Starts With 0
Increment by 1 --Increments by 1
Minvalue 0 --With Minimum value 0
Maxvalue 5 --Maximum Value 5. So The Cycle Of Creation Is Between 0-5
Nocycle -- No Cycle Means After 0-5 the Insertion Stopes
Nocache --The cache Option Specifies How Many Sequence Values Will Be Stored In Memory For Faster Access
You cannot do if Where on Sequence clause in SQL beacuse cannot filter the sequence. Use routines like @APC,
source to share