CODEWITHSUNDEEP
oracle-databaseplsqlsequence
David Caissy
David Caissy

Reputation: 2239

Automatically setting Oracle's sequence start value

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

Upvotes: 2

Views: 1419

Answers (2)

Sebas
Sebas

Reputation: 21542

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

Upvotes: 5

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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;

Upvotes: 2

Related Questions