sequence

easy

create sequence v_seq;

declare
begin

for i in 1.100 loop
insert into table_x values (v_seq.nextval + 100);
insert into table_y values (v_seq.currval +100);
end loop;

-- drop and recreate sequence for use next time.
execute immediate('drop sequence_1');
execite immediate('create sequence sequence_1');
commit;

end;


is this what you meant?

hi,

creating sequece is like below script

CREATE SEQUENCE SEQUNCE
 INCREMENT BY 1 START WITH 100 MAXVALUE 999 MINVALUE 100 CYCLE
    nocache ORDER

 you can insert the sequence to 2 tables if no other procedures are calling these sequence in the gap of these inserts

ie               insert into tableA values(sequnce.nextval,cola,colb);
                  insert into tableB values (sequnce.currval,colc,cold);

if you know the maximum value of the sequence, then u can set the sequence value to that maximum or above that using the below scrpt in your procedure

alter sequence seq_name increment by x; where x is the diference between maximum and current sequence value.

In this case if you have created the sequence with  CYCLE option starting with 100, then your sequence will start from 100 in next procedure call

I have created below sequence

 CREATE SEQUENCE labor_seq
 START WITH 100
 INCREMENT BY 1
 order

and i am trying to insert it in my procedure

insert into labor_allocate
(
header_id,line_number,seq_no
)
(
select b.header_id,b.line_number,labor_seq.nextval....
)

gives me compile time error "PL/SQL: ORA-02287: sequence number not allowed here"

hi,

creating sequece is like below script

CREATE SEQUENCE SEQUNCE
 INCREMENT BY 1 START WITH 100 MAXVALUE 999 MINVALUE 100 CYCLE
    nocache ORDER

 you can insert the sequence to 2 tables if no other procedures are calling these sequence in the gap of these inserts

ie               insert into tableA values(sequnce.nextval,cola,colb);
                  insert into tableB values (sequnce.currval,colc,cold);

if you know the maximum value of the sequence, then u can set the sequence value to that maximum or above that using the below scrpt in your procedure

alter sequence SEQUNCE increment by x; where x is the diference between maximum and current sequence value.

In this case if you have created the sequence with  CYCLE option starting with 100, then your sequence will start from 100 in next procedure call

at the begining of the procedure run the script

alter sequence SEQUNCE
increment by 1;

spin that's fine i use update statment instead of insert..

Thanks