Solved

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

Posted on 2003-11-17
5
4,983 Views
Last Modified: 2007-12-19
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..



0
Comment
Question by:manoj_cts
  • 2
5 Comments
 
LVL 1

Expert Comment

by:markag
Comment Utility
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
 

Author Comment

by:manoj_cts
Comment Utility
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
 
LVL 1

Accepted Solution

by:
markag earned 50 total points
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now