Oracle/PostgresQL nextval procedure - the simplest realisation

Question

What wrong here? I'm looking the simplest way to realise sequence nextval over true primary key ID, stored into the table

DECLARE
smth NUMBER;
LOOP
   smth := (select gen_ns_ces_id.nextval from dual);
   EXIT WHEN smth > (select MAX(ID_NS_WORK) from CES.NS_CES)
END LOOP;

CREATE SEQUENCE "CES"."GEN_NS_CES_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 2 START WITH 1837 NOCACHE NOORDER NOCYCLE ;

ID    NAME
1845  JONNY
..    ....

Not sure I understand the question. What do you mean by `realise`? Do you mean that you are trying to reset the sequence's value based on the largest primary key value in a table? — sstan, Jul 31 '15 at 13:43

score 2 · Answer 1 · answered Jul 31 '15 at 12:34

2

If you are using Oracle, I would expect an into:

select gen_ns_ces_id.nextval into smith
from dual;

answered Jul 31 '15 at 12:34

Gordon Linoff

2

`smth := gen_ns_ces_id.nextval;` is also valid starting with Oracle 11. – sstan Jul 31 '15 at 12:39
to me warning word 'select' as incorrect – Artem.Borysov Jul 31 '15 at 12:41

1 Answers1