sequence creation in oracle

Question

I want to create a sequence in oracle where the max value of the column field (Empid) must be the min value of the sequence.

The below was the one i found in our same stackexchange

create sequence mytemp_seq start with &v_Startval;

This command prompts me to enter the max value of teh column name which i have to enter.

How can I fix the value of &v_startval with out it prompting ,but directly setting the values from the below statement

select max(empid) from mytemp..

I am trying like this below

create sequence mytemp_seq start with (SELECT MAX(empid) from mytemp)

But it doesnt work.

Answer 1

You could do it with some PL/SQL:

declare
  v_startval integer;
begin
  select max(empid)+1 into v_startval from mytemp;
  execute immediate 'create sequence mytemp_seq start with ' || v_startval;
end;

Answer 2

In sqlplus you can do

col max_id new_value seq_min_val  
SELECT MAX(empid)+1 AS max_id from mytemp;
create sequence mytemp_seq start with &seq_min_val;