There is a table TB_LOG which did not have a sequence so far. Sequence SEQ_LOG_ID was added for the id column ID_LOG of that tabele. Now the proper value must be set so there are no conflicts when new entries are added with existing ones.
How can I set the sequence to the next valid value. The proper number must be determinated because the statement schould be ececuted within different schemas.
alter sequence SEQ_LOG_ID restart start with (select max(ID_LOG) from TB_LOG) + 1);
Start with expects a number but the select does not seem to work. How can this be done otherwise?
One option is to use a PL/SQL block.
Sequence:
SQL> create sequence seq_test;
Sequence created.
Its initial value (could be any, actually - which is probably your case):
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
1
PL/SQL block which first finds the MAX ID value in a table, adds 1 to it and uses the "new" value in alter sequence:
SQL> declare
2 l_max number;
3 begin
4 select max(id_email_log) + 1 into l_max from email_log;
5
6 execute immediate 'alter sequence seq_test restart start with ' || l_max;
7 end;
8 /
PL/SQL procedure successfully completed.
New sequence value:
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
5179
SQL>