?
Solved

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

Posted on 2003-11-17
5
Medium Priority
?
5,049 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

752 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