Dans l'écosystème Oracle Database, contrairement à d'autres systèmes de gestion de bases de données comme MySQL ou SQL Server, l'implémentation d'une colonne auto-incrémentée nécessite traditionnellement la création manuelle d'une séquence et d'un déclencheur (trigger), ou l'appel explicite de la méthode NEXTVAL. Pour optimiser le flux de développement et éviter les erreurs de manipulation, il est pertinent d'encapsuler cette logique dans des fonctions réutilisables.

L'approche standard consiste à définir une table et sa séquence associée comme suit :

-- Création de la table de test
CREATE TABLE UTILISATEURS (
    ID_USER NUMBER PRIMARY KEY,
    NOM_USER VARCHAR2(50) NOT NULL
);

-- Définition de la séquence
CREATE SEQUENCE SEQ_UTILISATEURS 
START WITH 1 
INCREMENT BY 1 
NOCACHE;

-- Insertion classique
INSERT INTO UTILISATEURS (ID_USER, NOM_USER) 
VALUES (SEQ_UTILISATEURS.NEXTVAL, 'Jean Dupont');

Bien que fonctionnelle, cette méthode devient fastidieuse sur des projets d'envergure. Si une séquence est oubliée ou mal nommée, l'insertion échoue. Pour pallier cela, nous pouvons concevoir une solution PL/SQL dynamique qui vérifie l'existence de la séquence et la génère automatiquement si nécessaire.

1. Fonction utilitaire de formatage

Cette fonctoin permet de s'assurer que les noms d'objets respectent les contraintes de longueur d'Oracle (souvent 30 caractères dans les versions plus anciennes).

CREATE OR REPLACE FUNCTION fn_formater_nom(
    p_chaine IN VARCHAR2,
    p_longueur IN INTEGER
) RETURN VARCHAR2 IS
BEGIN
    IF p_chaine IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN SUBSTR(p_chaine, 1, p_longueur);
END;

2. Procédure de génération dynamique de séquence

Cette procédure analyse la structure de la table, identifie la valeur maximale actuelle de la clé primaire et crée la séquence correspondante.

CREATE OR REPLACE PROCEDURE pr_creer_sequence_dynamique(
    p_nom_table IN VARCHAR2
) 
AUTHID CURRENT_USER AS
    v_seq_nom       VARCHAR2(31);
    v_cle_primaire  VARCHAR2(50);
    v_sql_max       VARCHAR2(1000);
    v_val_max       NUMBER;
    v_sql_create    VARCHAR2(1000);
BEGIN
    -- Construction du nom de la séquence (Préfixe SEQ_)
    v_seq_nom := UPPER(fn_formater_nom('SEQ_' || REPLACE(p_nom_table, '-', '_'), 30));

    -- Récupération du nom de la colonne de la clé primaire
    BEGIN
        SELECT column_name INTO v_cle_primaire
        FROM user_cons_columns
        WHERE constraint_name IN (
            SELECT constraint_name 
            FROM user_constraints 
            WHERE table_name = UPPER(p_nom_table) AND constraint_type = 'P'
        ) AND ROWNUM = 1;

        -- Recherche de la valeur maximale existante
        v_sql_max := 'SELECT MAX(' || v_cle_primaire || ') FROM ' || p_nom_table;
        EXECUTE IMMEDIATE v_sql_max INTO v_val_max;
    EXCEPTION
        WHEN OTHERS THEN
            v_val_max := 0;
    END;

    v_val_max := NVL(v_val_max, 0) + 1;

    -- Création de la séquence via SQL dynamique
    v_sql_create := 'CREATE SEQUENCE ' || v_seq_nom || 
                    ' START WITH ' || v_val_max || 
                    ' INCREMENT BY 1 NOCACHE';
    
    EXECUTE IMMEDIATE v_sql_create;
END;

3. Fonction d'obtention de l'identifiant

C'est le point d'entrée principal pour le développeur. Elle vérifie si l'objet existe avant d'apeler la séquence.

CREATE OR REPLACE FUNCTION fn_get_prochain_id(
    p_nom_table IN VARCHAR2
) RETURN INTEGER 
AUTHID CURRENT_USER AS
    v_seq_nom  VARCHAR2(100);
    v_existe   INTEGER;
    v_nouveau_id INTEGER;
    v_sql_exec   VARCHAR2(1000);
BEGIN
    v_seq_nom := UPPER(fn_formater_nom('SEQ_' || REPLACE(p_nom_table, '-', '_'), 30));

    -- Vérification de l'existence de la séquence dans le dictionnaire de données
    SELECT COUNT(*) INTO v_existe 
    FROM user_objects 
    WHERE object_type = 'SEQUENCE' AND object_name = v_seq_nom;

    IF v_existe = 0 THEN
        pr_creer_sequence_dynamique(p_nom_table);
    END IF;

    -- Récupération de la valeur suivante
    v_sql_exec := 'SELECT ' || v_seq_nom || '.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE v_sql_exec INTO v_nouveau_id;

    RETURN v_nouveau_id;
END;

L'utilisation du mot-clé AUTHID CURRENT_USER est cruciale ici : elle permet à la procédure d'exécuter des commandes DDL (Data Definition Language) avec les privilèges de l'utilisateur qui appelle la fonction, évitant ainsi les erreurs de droits insuffisants lors d'un EXECUTE IMMEDIATE.

Grâce à cette encapsulation, l'insertion de données devient extrêmement simplifiée et robuste :

-- L'insertion déclenchera automatiquement la création de la séquence si elle est absente
INSERT INTO UTILISATEURS (ID_USER, NOM_USER) 
VALUES (fn_get_prochain_id('UTILISATEURS'), 'Alice Martin');

Cette approche garantit une meilleure maintenance du code SQL et réduit les risques d'exceptions liées aux objets manquants dans la base de données.