-
Execute immediate
hi all,
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;
END;
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
i.e.
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
Name
-----------------------
SEQUENCE_OWNER
SEQUENCE_NAME
MIN_VALUE
MAX_VALUE
INCREMENT_BY
CYCLE_FLAG
ORDER_FLAG
CACHE_SIZE
LAST_NUMBER
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?
Otherwise:
select 'create sequence '||sequence_owner||
'.'||sequence_name||' start with '||min_value||
' maxvalue '||max_value||';'
from dba_sequences
/
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|