Tag - Oracle - blog.pagesd.info

Vérifier que les identifiants automatiques sont à jour

Par Michel le jeudi 5 juin 2008, 15:32

L'objectif est de s'assurer que pour toutes les tables qui ont un identifiant automatique, cet identifiant ne soit pas supérieur à la valeur de la séquence utilisée pour générer cet identifiant. (Pourquoi on se retrouve dans cette situation, c'est un autre problème).

Conventions :

tous les noms de tables sont préfixés par LUX_
tous les noms de clés primaires sont préfixés par PK_
tous les noms de séquences sont préfixés par SEQ_LUX_

Générer un script renvoyant la valeur maximum des identifiants de chaque table

Requête pour générer les SELECT MAX() de chaque table

SELECT 'UNION SELECT ''' || TABLE_NAME || ''' AS TB_NAME, '
       || 'MAX(' || COLUMN_NAME || ') AS TB_IDMAX ' 
       || 'FROM ' || TABLE_NAME
FROM   USER_IND_COLUMNS
WHERE  TABLE_NAME LIKE 'LUX_%'
AND    INDEX_NAME LIKE 'PK_%'
AND    COLUMN_NAME IN (SELECT COLUMN_NAME FROM COLS WHERE DATA_TYPE = 'NUMBER')
ORDER BY TABLE_NAME

Compléter le script généré

Sous TOAD, faire clic droit dans le résultat de la requête et choisir SaveAs et enregistrer dans le presse-papiers, ce qui donne :

UNION SELECT 'LUX_BANQUES' AS TB_NAME, MAX(IDBANQUE) AS TB_IDMAX FROM LUX_BANQUES
UNION SELECT 'LUX_CLIENTS' AS TB_NAME, MAX(IDCLIENT) AS TB_IDMAX FROM LUX_CLIENTS
UNION SELECT 'LUX_COMPTES' AS TB_NAME, MAX(IDCOMPTE) AS TB_IDMAX FROM LUX_COMPTES
UNION SELECT 'LUX_CONTACTS' AS TB_NAME, MAX(IDCONTACT) AS TB_IDMAX FROM LUX_CONTACTS
...

Dans Notepad, enlever le premier UNION et préfixer par un CREATE TABLE

CREATE TABLE TMP_IDMAXIMUMS AS 
SELECT 'LUX_BANQUES' AS TB_NAME, MAX(IDBANQUE) AS TB_IDMAX FROM LUX_BANQUES
UNION SELECT 'LUX_CLIENTS' AS TB_NAME, MAX(IDCLIENT) AS TB_IDMAX FROM LUX_CLIENTS
UNION SELECT 'LUX_COMPTES' AS TB_NAME, MAX(IDCOMPTE) AS TB_IDMAX FROM LUX_COMPTES
UNION SELECT 'LUX_CONTACTS' AS TB_NAME, MAX(IDCONTACT) AS TB_IDMAX FROM LUX_CONTACTS
...

Puis exécuter ce script pour obtenir une première table TMP_IDMAXIMUMS qui enregistrera la valeur maximum de l'identifiant de chaque table

Créer une seconde table pour enregistrer la valeur maximum de chaque séquence

CREATE TABLE TMP_SEQMAXIMUMS AS 
SELECT SUBSTR(SEQUENCE_NAME, 5) AS TB_NAME, LAST_NUMBER AS TB_SEQMAX
FROM   USER_SEQUENCES
WHERE  SEQUENCE_NAME LIKE 'SEQ_LUX_%'

Comparer le contenu des deux tables

SELECT T1.TB_NAME, TB_IDMAX, TB_SEQMAX
FROM   TMP_IDMAXIMUMS  T1,
       TMP_SEQMAXIMUMS T2
WHERE  T1.TB_NAME = T2.TB_NAME
AND    TB_IDMAX > TB_SEQMAX

(Wdevs) Oracle, auto-increment column and pre-defined values

Par Michel le lundi 21 mars 2005, 17:17

This week-end, I looked for a way to have auto-increment column in Oracle while having the possibility to set a given value in some inserts.

Several years ago, I used a trigger where the pseudo auto-incremented column was initialized with a sequence only when its value is empty.

CREATE OR REPLACE TRIGGER TRG_BI_all_Increments
   BEFORE INSERT
   ON all_Increments
   FOR EACH ROW
BEGIN
   IF (:NEW.ID IS NULL) THEN
      SELECT SEQ_all_Increments.NEXTVAL INTO :NEW.ID FROM DUAL;
   END IF;
END;

That enabled me to initialize parameters tables with default values from an sql script. The drawback was that once the data are loaded, the start value for the sequence had to be manually reset to the maxim value inserted in order to avoid duplicate errors.

I rewrote it so that when an INSERT statement define the ID value, the triggeer checks if this value is greather than the current sequence value and in this case automatically reset the sequence to the inserted value.

/* create table */
CREATE TABLE all_Increments (
    id   INT NOT NULL ,
    test VARCHAR2(100) ,
    CONSTRAINT PK_all_Increments
    PRIMARY KEY ( id )
);

/* create sequence */
CREATE SEQUENCE SEQ_all_Increments
    START WITH 1
    MAXVALUE 1.0E28
    MINVALUE 1
    NOCYCLE
    NOCACHE
    ORDER;

/* create trigger */
CREATE OR REPLACE TRIGGER TRG_BI_all_Increments
    BEFORE INSERT
    ON all_Increments
    FOR EACH ROW
DECLARE
    last_Sequence NUMBER;
    last_InsertID NUMBER;
BEGIN
    IF (:NEW.id IS NULL) THEN
         SELECT SEQ_all_Increments.NEXTVAL INTO :NEW.id FROM DUAL;
    ELSE
         SELECT NVL(Last_Number, 0) INTO last_Sequence
           FROM User_Sequences
          WHERE UPPER(Sequence_Name) = UPPER('SEQ_all_Increments');
         SELECT :NEW.id INTO last_InsertID FROM DUAL;
         WHILE (last_InsertID > last_Sequence) LOOP
              SELECT SEQ_all_Increments.NEXTVAL INTO last_Sequence FROM DUAL;
         END LOOP;
    END IF;
END;

/* test values */
INSERT INTO all_Increments (id, test) values (1, 'one');
INSERT INTO all_Increments (id, test) values (2, 'two');
INSERT INTO all_Increments (id, test) values (3, 'three');
INSERT INTO all_Increments (id, test) values (10, 'ten');
INSERT INTO all_Increments (test) values ('eleven?');
INSERT INTO all_Increments (id, test) values (5, 'five?');
INSERT INTO all_Increments (test) values ('twelve?');

blog.pagesd.info

Tag - Oracle