i am trying to recreate the sequences using execute immediate.
FOR r_cur IN c_cur LOOP
str1 := (select max(' ||r_tsr.tbl_seq_ref_col_nm|| ')+1' ||
'from ' || r_tsr.tbl_seq_ref_tbl_nm;
EXECUTE IMMEDIATE str1;
could you let me know any way to caputure MAX value from above SQL statment.
because i want to use same MAX value for recreating the Sequence
str3:= ' CREATE sequence CNV_TEST minvalue 1 maxvalue 100000 start with || max_val || increment by 1 cache 20';
EXECUTE IMMEDIATE str3;
thanks in advance.
Don't you need to take into account the owner? Drop the sequence before re-creating it (ORA-00955: name is already used by an existing object)?
SQL> desc dba_sequences
Do any primary keys in your data rely upon sequence-generated values? Why not start it with one plus the last_number?
Do you really want to record 999999999999999999999999999 for a max value?
select 'create sequence '||sequence_owner||
'.'||sequence_name||' start with '||min_value||
' maxvalue '||max_value||';'
Spool this out, edit it as necessary, save it as a sql file, then run it.
execute immediate 'select max(' ||r_tsr.tbl_seq_ref_col_nm|| ')+1 from ' || r_tsr.tbl_seq_ref_tbl_nm INTO my_number;
Click Here to Expand Forum to Full Width