4 Replies Latest reply: May 23, 2008 9:34 AM by 453514 RSS


      Can I set the currval for a sequence inside plsql?
      select test_seq.nextval from dual;
      Now can I set the current value of the sequence to 100 so that when I run the above query again I will 101?
        • 1. Re: Sequence.currval
          1) Why? What is the business purpose of advancing the sequence? That would be a pretty unusual thing to do.

          2) Assuming a maintenance window and that yours is the only session that could be using the sequence, you could reset the current value with a couple of DDL statements, i.e.

          - ALTER SEQUENCE test_seq INCREMENT BY 97
          - Get the test_seq.nextval, which would be 100
          - ALTER SEQUENCE test_seq INCREMENT BY 1
          - The next value would be 101.

          This would not work in a multi-user environment where you didn't have exclusive access to the sequence.

          • 2. Re: Sequence.currval
            Thanks for your help Justin.

            I am trying to see if I can get a job (Re: PL/SQL procedure review done without pl/sql.

            I have to read table A and insert into table B with keys generated from table C. My plan is to assign the current key values to sequences and use that sequence in the insert statements and at the end update table C with the current values of sequences. Only a cronjob will have to access these sequences.
            • 3. Re: Sequence.currval
              I'm having trouble following the thread you linked to. It's not obvious to me what you're trying to accomplish there or how this question relates to the linked thread.

              I'm not sure that I see the point to generating keys this way. What possible benefit does the presence of table C have? Why not just generate keys based off sequences and avoid initializing the beginning state and writing the end state to a table?

              • 4. Re: Sequence.currval
                Thank you Justin for taking the time to answer my questions.

                We have a third party application (SYS1) which generates the keys from a key table. Now I have to feed records into this system taking from another system. I can not change the way the first application generates its keys. All I can do is find the next key values from table C and use them to insert new records. I also need to update the key values in table C in order for SYS1 to keep track of its keys. Kind of strange! But I can’t call the shots because I am way down in the food chain:((. Since I am not an expert in PL/SQL, I was hoping for the experts in this forum could take a look at my code and give their feedback. I know it is not fun, going through someone else code, especially when the coder is a beginner.