| Need to reset sequence numbers after an import [message #19462] |
Wed, 20 March 2002 08:02  |
Rich
Messages: 14
Registered: March 2002
|
Junior Member |
|
|
I'm doing a partial database load through an export file. The problem is I also need to reset all of the sequences used to seed the IDs for those tables.
There doesn't seem to be a way to specify which sequences to include in an export....I'm using the tables=() parameter of the exp function to specify which tables to export/import, but I can't see how to include the sequences I use for those tables as well.
My other though is to use a script after loading to reset them all, but this fails. Any ideas?
declare
v_next_id number;
begin
select max(detail_id)+1 into v_next_id from aa_order_detail;
drop sequence aa_order_detail_seqno;
commit;
CREATE SEQUENCE aa_order_detail_seqno
START WITH v_next_id
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
NOCACHE
NOORDER;
commit;
end;
|
|
|
|
| Re: Need to reset sequence numbers after an import [message #19464 is a reply to message #19462] |
Wed, 20 March 2002 08:10  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
In order to run DDL (create/alter/drop) in an anonymous block, you will need to use dynamic SQL:
execute immediate 'create sequence a_order_detail_seqno START WITH ' || v_next_id || ' INCREMENT BY ...';
You also do not need the commit statements since a commit is implicit with each DDL statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
