Change sequence value with subselect

Question

Change sequence value with subselect

Navigation

#1 by (3 votes)

3

I have the following case: I need to change the value of a sequence according to the max (id) of the referring table, I am trying to do something like:

ALTER SEQUENCE CFOP_SEQ INCREMENT BY (SELECT MAX(ID)+1 FROM CFOP);

But it returns me the message "Invalid number". Does anyone know how I can solve this problem?

sql oracle

asked by anonymous 30.12.2016 / 11:42

1 answer

Passing variable in jQuery within Ajax How to do select auto relationship N: N

score 3 · Accepted Answer

You can use a pl to do this. By storing the value of the select within a variable using into and then running alter with execute .

declare
  maxId number;
begin 

  SELECT MAX(ID)+1 
    into maxId
  FROM CFOP;

  execute immediate 'ALTER SEQUENCE CFOP_SEQ INCREMENT BY '||maxId;
end;
/

If you need to write any other command you can add it right in the body of PL / SQL, in cases of SELECT you need to add the returns in variables or in a course using INTO .

You only use the immediate execute Follow the link for more information about PL / SQL.

link