Oracle Sequence - Last value need to be Max of primary key column +1

How to alter my sequence so that last value of sequence should be one more than the max value value of primary key cloumn of the table.
It seems i need to have a procedure which will alter all the sequences at one shot.
Could any one suggest me on this..



manoj_ctsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

markagCommented:
You cannot alter the Start with of a sequence. You must drop it and recreate it.

-- Get max value of current pk
Select MAX(MY_PK) + 1 FROM MY_TABLE;

-- returns...
12345

-- drop and recreate sequence
DROP SEQUENCE MY_SEQUENCE;

CREATE SEQUENCE MY_SEQUENCE START WITH 12345 increment by 1 ...yadda yadda yadda

HTH,
Mark
0
manoj_ctsAuthor Commented:
This is fine but it will can be done every time.I want to have a procedure which will do it for all the sequences.
0
markagCommented:
firstly, sequences have no object association with a table. They are on an island. You have no mechanism to connect a sequence to a specific table. You would need to create a table that has the sequence name and the table it is associated with; then you could easily open a cursor of that table, walk the recordset, grab the max value on the table, and drop and recreate the sequence.

Example:
Create the original sequences
DEV_HT4>create sequence my_group_seq start with 1 increment by 1;
DEV_HT4>create sequence my_att_seq start with 1 increment by 1;
Create the table and values
CREATE TABLE my_seq_tabs
(SEQUENCE_NAME varchar2(30), table_name varchar2(30), primary_key_col varchar2(30))
/
-- plug in some test values from real tables
INSERT INTO my_seq_tabs
                  (sequence_name, table_name, primary_key_col)
       VALUES('MY_GROUP_SEQ', 'MAINT_GROUP', 'GROUP_ID')
/
INSERT INTO my_seq_tabs
                  (sequence_name, table_name, primary_key_col)
       VALUES('MY_ATT_SEQ', 'MAINT_ATTORNEY', 'ATTORNEY_ID')
/

Create procedure....
CREATE OR REPLACE PROCEDURE HT4.SEQUENCE_MAKER
IS
   CURSOR c_seqs
   IS
        SELECT sequence_name, table_name, primary_key_col
            FROM my_seq_tabs;

   rec_seqs                                c_seqs%ROWTYPE;
   v_sql                                     VARCHAR2(4000);
   v_max                                     PLS_INTEGER;
BEGIN
   OPEN c_seqs;

   LOOP
        FETCH c_seqs INTO rec_seqs;
        EXIT WHEN c_seqs%NOTFOUND;
        -- grab max value of column on table
        v_sql    :=
            'SELECT MAX(' || rec_seqs.primary_key_col || ') + 1 FROM ' ||
               rec_seqs.table_name;
        EXECUTE IMMEDIATE v_sql
             INTO v_max;

        BEGIN
             -- drop sequence
             v_sql        := 'DROP SEQUENCE ' || rec_seqs.sequence_name;
             EXECUTE IMMEDIATE v_sql;
        EXCEPTION
             WHEN OTHERS THEN
                  -- trap for sequence does not exist. just continue on...
                  NULL;
        END;

        -- recreate sequence 1 greater than max val
        v_sql    :=
            'CREATE SEQUENCE ' || rec_seqs.sequence_name || ' START WITH ' || v_max ||
               ' INCREMENT BY 1';
        EXECUTE IMMEDIATE v_sql;
   END LOOP;

   CLOSE c_seqs;
EXCEPTION
   WHEN OTHERS THEN
        raise_application_error(-20001, SQLERRM);
END;

This should work, but I ran out of time to test it....

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.