CODEWITHSUNDEEP
oracle-databaseoracle10g
user682571
user682571

Reputation: 169

sequence creation in oracle

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.

Upvotes: 5

Views: 1529

Answers (2)

schurik
schurik

Reputation: 7928

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;

Upvotes: 6

Tony Andrews
Tony Andrews

Reputation: 132580

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;

Upvotes: 8

Related Questions