Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Question 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_delta NUMBER;
    l_nextval NUMBER;
    l_getdelta VARCHAR2(500);
    l_alter VARCHAR2(300);

    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);


    SELECT test_seq.nextval
    INTO l_nextval
    FROM dual;

    l_alter := 'ALTER SEQUENCE test_seq INCREMENT BY 1';



  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    It seems that you didn't pay enough attention while reading documentation about EXECUTE IMMEDIATE. Did you read it, anyway?

    Here's an example of how you were supposed to select into a local variable.
    SQL> create or replace procedure prc_seq (par_seq_name in char) is
      2    l_str     varchar2(500);
      3    l_nextval number;
      4  begin
      5    l_str := 'select ' || par_seq_name || '.nextval from dual';
      6    execute immediate l_str into l_nextval;
      8    dbms_output.put_line (l_nextval);
      9  end;
     10  /
    Procedure created.
    SQL> exec prc_seq('my_seq');
    PL/SQL procedure successfully completed.
    SQL> exec prc_seq('my_seq');
    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Jan 2004
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts