The following procedure doesn't compile if sequence SEQ_ADR does not exist before compilation. I had to create the sequence manually before being able to compile this procedure. How can I avoid this manual generation?
PROCEDURE A_270(proc_id number) IS
seq_cnt number;
curr_max number;
BEGIN
select count(*) into seq_cnt from user_sequences where sequence_name='SEQ_ADR';
if seq_cnt > 0 then
execute immediate 'drop sequence SEQ_ADR';
end if;
select max(id)+1 into curr_max from adress;
execute immediate 'create sequence SEQ_ADR start with '||curr_max||'';
insert into adress(ID,
IMPORTED_DT
)
select
SEQ_ADR.nextval ID,
sysdate IMPORTED_DT
from new_adress;
END;
Edited by: totalnewby on Aug 23, 2012 6:41 AM