Sobre comentarios y cómo actualizar secuencias

Cuando se realizan cargas de datos en un esquema existente y no se utilizan las secuencias para asignar los valores de llave primaria, se produce un desfasaje entre identificadores y secuencias. Este desfasaje provoca errores de clave primaria ya existente, ya que los valores retornados por secuencia.NEXTVAL, pueden ya existir entre los datos recientemente cargados.

Para sincronizar las secuencias con los identificadores de llave primaria, puede crearse un script en PL/SQL el cual recree cada secuencia con su valor inicial según el máximo identificador de la tabla correspondiente. Para poder realizarlo, necesitamos primero tener una correspondencia entre tabla y secuencia, y en Oracle esta dependencia no existe.

Es una buena oportunidad para comenzar a adoptar la buena práctica de usar comentarios sobre los objetos. Muchas veces, en el proceso de creación de una base de datos, este paso es omitido y considerado innecesario. Supuestamente, los objetos "no necesitan describirse" fundamentandose que el diseño es lo suficientemente explicativo y que los creadores van a permanecer durante todo el proceso de desarrollo, por lo tanto no vale la pena perder el tiempo describiendo los objetos. ¿No suenan estas excusas a holgazanería?

No se deben ignorar estas máximas: 1) el diseñador puede abandonar el proyecto y 2) las personas externas que miren nuestro modelo pueden no entenderlo.

En este sentido, el responsable de base de datos debe velar porque el modelo quede totalmente documentado y que no dependa de las personas que lo crearon, en definitiva que sea lo más auto-explicativo posible.

Alguien puede considerar suficiente que la base de datos quede documentada en un diagrama o en un documento de word, pero ningún esfuerzo es suficiente si podemos hacer un poquito más. Los comentarios sobre objetos son almacenados en el diccionario de datos, y van a ser exportados junto a los objetos cuando estos sean migrados. Sin necesidad de consultar diagramas ni documentos, un programador en apuros puede hacer un DESCRIBE sobre una tabla en SQLPLUS, y saber al instante donde encontrar el dato que busca. Los comentarios ayudan a disipar las dudas más rapido, sobre todo a los que no están empapados en el modelo.

Ahora si, volviendo al tema inicial, ¿cómo relacionamos una tabla con su secuencia? Precisamente, colocando en el comentario de la columna, el nombre de la secuencia que se utiliza:

SQL> COMMENT ON cuentas.id_cuenta IS 'CUENTA_SEQ'

Acabamos de documentar que para la columna id_cuenta de la tabla cuentas, se debe utilizar la secuencia cuenta_seq. Observar que el efecto de esta sentencia es únicamente documental, todavía puede utilizarse la secuencia de la forma que se desee. La ventaja es que ahora podemos consultar la tabla de diccionario dba_col_comments en nuestros scripts.

A modo de nota, mencionamos que tambien se pueden comentar tablas y vistas, con esta sintaxis:

SQL> COMMENT ON cuentas 'Tabla para el registro de cuentas corrientes personales'

Finalmente, el bloque que actualiza las secuencias utilizando los comentarios sobre columnas.

DECLARE
  v_max NUMBER(10) := 0;
  v_desc varchar2(50);
BEGIN

  FOR t IN (SELECT u.table_name,
                   u.column_name,
                   uc.comments secuencia
            FROM   user_tab_columns  u,
                   user_col_comments uc
            WHERE  u.table_name = uc.table_name
            AND    u.column_name = uc.column_name
            AND    u.column_name LIKE 'ID_%')
  LOOP
      EXECUTE IMMEDIATE 'SELECT MAX(' || t.column_name || ') FROM ' ||
t.table_name INTO v_max;

      dbms_output.put_line('Eliminando secuencia '|| t.secuencia||'...');
      v_desc := 'DROP SEQUENCE '|| t.secuencia;
      EXECUTE IMMEDIATE 'DROP SEQUENCE '|| t.secuencia;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE '|| t.secuencia ||' INCREMENT BY
1 START WITH '|| (NVL(v_max,0) + 1);
      dbms_output.put_line('Secuencia '|| t.secuencia||' recreada.');

  END LOOP;
END;

Se requiere el permiso CREATE SEQUENCE por parte del ejecutor de este script.