| Dynamic Sequence recreation [message #19946] |
Fri, 19 April 2002 13:29  |
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
|
Junior Member |
|
|
Howdy!
I am having a difficulty with oracle and sequences. I am using Oracle 8iR3 (8.1.7.2.0) on Unixware 7.1.1.
The situation:
I have a user table that users a sequences to generated new unique user id's on insertion. A trigger is run before insert to insert the next sequence number into the table.
The user table occasionally needs to be loaded with data from a production box. I currently have to diable the triggers, disable the constraints, truncate the table, import the table, and manually determine the new sequence values.
I have tried using substitution and bind variables, PL/SQL routines, and such. I cannot seem to dynamically create a sequence with a result from another table.
In my ideal world, this hypothetical statement would look like:
CREATE USERID_SEQ
INCREMENT BY 1
START WITH (SELECT MAX(USERID)+1 FROM USERS);
I cannot execute DDL from PL/SQL (even EXECUTE IMMEDIATE cannot drop and create a sequence for me), I cannot use a bind variable or substituion variable in a CREATE SEQUENCE statement.
What am I missing?
Thank you for your time and expertise.
--Matthew
|
|
|
|
|
|
| Re: Dynamic Sequence recreation [message #19951 is a reply to message #19946] |
Fri, 19 April 2002 15:45  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm not sure what you mean when you say "even EXECUTE IMMEDIATE cannot drop and create a sequence for me".
It sure could just by doing:
execute immediate 'drop sequence userid_seq';
and
execute immediate 'create sequence userid_seq';
But, you can modify the sequence value without dropping it and recreating it. This is important if you have grants on the sequence and don't wish to have to recreate those as well:
declare
v_id number;
v_max_id number;
begin
-- get the latest sequence value
select userid_seq.nextval into v_id from dual;
-- get the highest value in your table
select max(userid) into v_max_id from users;
-- alter the sequence increment by the difference
execute immediate 'alter sequence userid_seq increment by ' || (v_max_id - v_id);
-- make the sequence value shift in one shot
select userid_seq.nextval into v_id from dual;
-- reset the increment
execute immediate 'alter sequence userid_seq increment by 1';
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
