Unanswered: Syncing sequences between prod and test
I'm trying to write a procedure that will syncronize sequences in our test environment with the same sequences in our production environment. I select the delta of nextvals into a local variable then alter the sequence to increment by the delta select the nextval of the test sequence then alter it again to increment by 1. The procedure works when I hardcode the sequence name but when I try to pass the sequence name into the procedure then build a string to select the delta I get a missing keyword error. I want to be able to pass the sequence name in so I can automate this process by using a cursor to run through all our sequences then loop through the cursor and execute the procedure for each one. Below is the procedure I wrote which works with the hardcoded sequence name but when I remove the comments to build the first select string then try the execute immediate statement that's when it gives me the error. I'm thinking it's because I can't select into a local variable from withing an execute immediate is this true? If this is true then any ideas how I can accomplish syncing all out sequences? Thanks!
CREATE OR REPLACE procedure sync_sequences (p_sequence IN VARCHAR2) AS
l_getdelta := 'SELECT ' || p_sequence || '.nextval@proddb - ' || p_sequence || '.nextval INTO l_delta FROM dual';
EXECUTE IMMEDIATE l_getdelta;
SELECT test_seq.nextval@proddb - test_seq.nextval INTO l_delta FROM dual;
l_alter := 'ALTER SEQUENCE test_seq INCREMENT BY '||to_char(l_delta);
EXECUTE IMMEDIATE l_alter;
l_alter := 'ALTER SEQUENCE test_seq INCREMENT BY 1';
Aha of course, thank you very much. School was a long time ago, I'm mostly a forms and application developer for data processing and don't use 'execute immediate' very much. I'm helping our dba write dynamic scripts to refresh the test environment from prod to get a little cross training in the dba world. I appreciate the help!