Oracle : Mise à jour des sequences après import

Ames sensibles, s’abstenir…

Voilà un sujet qui est abordé sur internet sans forcement apporter de solution. Voici donc un script qui règle le problème.

Problème :

Sous Oracle, les séquences gèrent les « numéro auto » de façon indépendante. Si aucun trigger n’est mis en place, il faut appeler manuellement la valeur suivante de l’id au moment de faire une insertion. Mais dans le cadre d’un import de données brutes, les sequences ne sont pas mises à jour. La valeur « START » de la séquence ne peut être mise à jour…

Solution :

Il faut donc détruire les séquences et les recréer en prenant comme valeur de début, le MAX de l’id de la table en question + 1.

Pour la destruction de l’ensemble des séquences, voilà le script :

begin
  for i in (select sequence_name from user_sequences ) loop
    execute immediate ‘drop sequence ‘||i.sequence_name;
  end loop;
end;
/

Puis pour la création :

DECLARE
 PROCEDURE CREATE_SEQ (table_name IN VARCHAR2, seq_name IN VARCHAR2, column_name IN VARCHAR2, nb_cache IN INTEGER)
 IS 
  cursor_handle INTEGER;
  seq_sql VARCHAR2(400);
  resu INTEGER;
  seq_start INTEGER;
 BEGIN
  
  seq_sql := ‘SELECT MAX(‘ || column_name || ‘) FROM ‘ || table_name;  
    
  cursor_handle := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_handle,seq_sql,DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN (cursor_handle,1,seq_start);
  resu:= DBMS_SQL.EXECUTE (cursor_handle);
  
  IF DBMS_SQL.FETCH_ROWS (cursor_handle) = 0
  THEN
   seq_start:=1;
  ELSE
   DBMS_SQL.COLUMN_VALUE(cursor_handle,1,seq_start);
   IF seq_start IS NULL
   THEN
    seq_start:=1;
   ELSE 
    seq_start:=seq_start + 1;
   END IF; 
  END IF; 
        
  DBMS_SQL.CLOSE_CURSOR (cursor_handle);
  
  seq_sql := ‘CREATE SEQUENCE ‘ || SUBSTR(seq_name,1,26) || ‘ START WITH ‘
    || TO_CHAR(seq_start) || ‘ INCREMENT BY 1 CACHE ‘ || TO_CHAR(nb_cache);
  cursor_handle := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_handle,seq_sql,DBMS_SQL.V7);
  resu:= DBMS_SQL.EXECUTE (cursor_handle);
  DBMS_SQL.CLOSE_CURSOR (cursor_handle);
  
  DBMS_OUTPUT.PUT_LINE(‘Sequence ‘ || table_name || ‘ créée.’);
 END;
 
BEGIN
 DBMS_OUTPUT.ENABLE(10000);
 CREATE_SEQ (‘table’, ‘sequence’,’id’, 5);
 CREATE_SEQ (‘table2’, ‘sequence2′,’id2’, 5);
 
END;
/ 

Mots clé anglais sur le sujet :

« Oracle update sequence » « restart sequence max value »