Solved

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

Posted on 2003-11-17
5
5,022 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

860 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