Automatically setting Oracle's sequence start value

Question

I have many existing tables, each with a column called 'id'. This column has an integer value starting at 1. So for example, the table MY_TABLE contains 3 records with ids 1, 2 and 3 (super basic).

I want to create a sequence for each table I have and set its start value with the maximun id of the table. In my example, I would need something like this:

CREATE SEQUENCE MY_TABLE_SEQ START WITH 3 INCREMENT BY 1;

I tried something like this, but it didn't work:

CREATE SEQUENCE MY_TABLE_SEQ START WITH (SELECT NVL(MAX(id),1) FROM MY_TABLE) INCREMENT BY 1;

Any idea what I might be able to do?

Thanks

Sebas · Accepted Answer · 2013-10-29 22:33:01Z

5

DECLARE
    MAXVAL MY_TABLE.ID%TYPE;
BEGIN
    SELECT NVL(MAX(id),1) INTO MAXVAL FROM MY_TABLE;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_TABLE_SEQ START WITH ' || MAXVAL || ' INCREMENT BY 1';
END
/

You could also ALTER the sequences once they are created.

Some readings about the subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951269671592

edited Oct 29, 2013 at 22:33

answered Oct 29, 2013 at 18:32

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Caissy Over a year ago

Your solution didn't work for me in the first place, but with 2 minor modifications (changing 'MY_TABLE%ID' with 'NUMBER(19,0)' and concatenating the MAXVAL variable), it did the trick. Thanks!

AndyDan Over a year ago

MY_TABLE%ID should really be MY_TABLE.ID%type.

Przemyslaw Kruglej · Accepted Answer · 2013-10-29 18:36:38Z

You could generate the CREATE SEQUENCE commands:

CREATE TABLE test_tab1 (
  id number
);

CREATE TABLE test_tab2 (
  id number
);

INSERT INTO test_tab1 VALUES (20);
INSERT INTO test_tab2 VALUES (40);

COMMIT;

DECLARE
  v_max_id NUMBER;
BEGIN
  FOR v_table IN (SELECT table_name
                    FROM user_tables
                  WHERE table_name IN ('TEST_TAB1', 'TEST_TAB2'))
  LOOP
    EXECUTE IMMEDIATE 'SELECT NVL(MAX(id), 1) FROM ' || v_table.table_name
      INTO v_max_id;
    dbms_output.put_line(
      'CREATE SEQUENCE ' || upper(v_table.table_name) || '_SEQ START WITH ' || v_max_id || ' INCREMENT BY 1;');
  END LOOP;
END;

Output:

CREATE SEQUENCE TEST_TAB1_SEQ START WITH 20 INCREMENT BY 1;
CREATE SEQUENCE TEST_TAB2_SEQ START WITH 40 INCREMENT BY 1;

Collectives™ on Stack Overflow

Automatically setting Oracle's sequence start value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related