oracle multiple sequence on a single table

Question

I want different sequence in a single table for different values of a column (esercizio).

I do this but is not dynamic creation and use of sequence. How can abstract the problem?


    CREATE TABLE Z_TEST
    (
        ID            NUMBER,
        ESERCIZIO     NUMBER,
        DESCRIZIONE   VARCHAR2 (200 BYTE)
    )
    LOGGING
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    MONITORING;

    ALTER TABLE Z_TEST ADD (  CONSTRAINT Z_TEST_PK PRIMARY KEY (ID, ESERCIZIO));

    CREATE SEQUENCE Z_TEST_SEQ_2010 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
    CREATE SEQUENCE Z_TEST_SEQ_2011 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
    CREATE SEQUENCE Z_TEST_SEQ_2012 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
    CREATE SEQUENCE Z_TEST_SEQ_2013 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
    CREATE SEQUENCE Z_TEST_SEQ_2014 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
    CREATE SEQUENCE Z_TEST_SEQ_2015 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

    CREATE OR REPLACE TRIGGER Z_TEST_TRG
        BEFORE INSERT
        ON Z_TEST     REFERENCING new AS New old AS Old
        FOR EACH ROW
    DECLARE
        err_code    NUMBER;
        err_msg     VARCHAR2 (200);
    BEGIN
        if :new.esercizio = 2010 then select Z_TEST_SEQ_2010.nextval into :new.ID from dual; end if;
        if :new.esercizio = 2011 then select Z_TEST_SEQ_2011.nextval into :new.ID from dual; end if;
        if :new.esercizio = 2012 then select Z_TEST_SEQ_2012.nextval into :new.ID from dual; end if;
        if :new.esercizio = 2013 then select Z_TEST_SEQ_2013.nextval into :new.ID from dual; end if;
        if :new.esercizio = 2014 then select Z_TEST_SEQ_2014.nextval into :new.ID from dual; end if;
        if :new.esercizio = 2015 then select Z_TEST_SEQ_2015.nextval into :new.ID from dual; end if;
    EXCEPTION WHEN OTHERS THEN
        err_msg := SUBSTR (SQLERRM, 1, 200);
        err_code := SQLCODE;
        DBMS_OUTPUT.put_line ('errore: ' || err_code || ' ' || err_msg);
    END;
    /

What do you mean? You want to avoid having to manually create a new sequence and alter the trigger before you get a record with 2016, say? Why does the ID need to be different based on that value - it means ID itself isn't unique so it can't be the primary key, so what is it for, and why can't you use a single sequence to populate that column? — Alex Poole
I have many old database in access who have their own autonumber, the table reference to other but I have 1 database for year. — Albo
I think your two-part primary key should be ESERCIZIO, ID rather than ID, ESERCIZIO. Then, if you don't care about gaps in your sequence, the same sequence can be used for all values of ID. Is there any reason why you can't have gaps? — Glenn
Now I need to keep all together but renumber all keys in all tables I think is not a good idea. — Albo
I need to preserve the actual number of id that is not unique but ID,ESERCIZIO yes. — Albo

Ben Ben · Accepted Answer · 2014-02-18T14:59:13

You abstract the problem by ignoring the necessity to do this; you're creating extra data that is not maintainable in it's current form; so you should not attempt to create it.

You can create a single sequence to populate your ID column and then generate your secondary sequence when extracting from the database by using the analytic function ROW_NUMBER(). Because a sequence always increments you guarantee the "new" ID column will remain in order as long as you order by the "old" ID column.

For instance:

select row_number() over ( partition by esercizio order by id ) as id
     , descrizione
  from z_test

If you absolutely must store this in the database for some reason you could have a secondary process that populates it at regular intervals.

As an aside it's almost always bad practice to use DBMS_OUTPUT.PUT_LINE, in production code, to display errors. It requires that someone always be there to view them, which is never going to happen. If you handle an error you need to do something with it.

I disagree with doing this, as there's no need, but you can reference the sequence dynamically if you wish:

CREATE OR REPLACE TRIGGER Z_TEST_TRG
    BEFORE INSERT
    ON Z_TEST
    FOR EACH ROW
BEGIN
   execute immediate 'select z_test_seq' || :new.esercizio || '.nextval 
                        from dual'
                into :new.id;
END;

oracle multiple sequence on a single table

1 Answers