Solved

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

Posted on 2003-11-17
5
4,999 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
ID: 9763359
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
ID: 9769359
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
ID: 9771050
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…

910 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

20 Experts available now in Live!

Get 1:1 Help Now