Diferencias entre columnas

Me he visto obligado a sustituir algunas tablas de un esquema por vistas sobre otro. La idea es hacer una refactorización, por lo que necesito asegurarme de que las nuevas vistas tengan las mismas columnas que las antiguas tablas. Para eso me he hecho un script que se encarga de compararme nombres y tipos de variable. Como utiliza la vista ALL_TAB_COLS sirve para comparar indistintamente vistas y tablas. Por si a alguien le resulta de utilidad, ahí va:

-- Script para comparar columnas de dos vistas o tablas.
-- http://www.tsoracle.com
--------------
-- Sets varios
--------------
SET SERVEROUTPUT ON 
SET VERIFY OFF
---------------------
-- Valores de entrada
---------------------
ACCEPT PROPIETARIO1 PROMPT "Propietario del esquema 1: "
ACCEPT TABLAOVISTA1 PROMPT "Nombre de la tabla o vista 1: "
ACCEPT PROPIETARIO2 PROMPT "Propietario del esquema 2: "
ACCEPT TABLAOVISTA2 PROMPT "Nombre de la tabla o vista 2: "
DECLARE
  
  --------------------
  -- Tipos de Variable
  --------------------
  -- Tipo para diferencias unitarias
  TYPE tDiferencia IS RECORD (
    vColumna ALL_TAB_COLS.COLUMN_NAME%TYPE,
    vValorA  VARCHAR2(200),
    vValorB  VARCHAR2(200) );

  -- Tipo para conjuntos de diferencias
  TYPE tDiferencias IS TABLE OF tDiferencia INDEX BY BINARY_INTEGER;
  
  -------------
  -- Constantes
  -------------
  VOWNER1 CONSTANT ALL_TAB_COLS.OWNER%TYPE      := UPPER( 
                                                     SUBSTR( '&&PROPIETARIO1',
                                                             1, 30 ) );
  VTABLA1 CONSTANT ALL_TAB_COLS.TABLE_NAME%TYPE := UPPER( 
                                                     SUBSTR( '&&TABLAOVISTA1',
                                                             1, 30 ) );

  VOWNER2 CONSTANT ALL_TAB_COLS.OWNER%TYPE      := UPPER( 
                                                     SUBSTR( '&&PROPIETARIO2',
                                                             1, 30 ) );
  VTABLA2 CONSTANT ALL_TAB_COLS.TABLE_NAME%TYPE := UPPER( 
                                                     SUBSTR( '&&TABLAOVISTA2',
                                                             1, 30 ) );
  VNULL  CONSTANT VARCHAR2(30) := 'SIN VALOR';
  ------------
  -- Variables
  ------------
  -- Variables para llenar las diferencias entre columnas
  plDistintoTipo     tDiferencias;
  plDistintaLongitud tDiferencias;
  plDistintoNulable  tDiferencias;

  -- Variable boleana que me indica si existen las dos tablas/vistas
  bExistenAmbas BOOLEAN;
  
  -----------
  -- Cursores
  -----------
  CURSOR cColumnasDistintoTipo IS
    SELECT A.COLUMN_NAME VCOLUMNA, -- Columna de la discordia

           -- Datos de la columna de la tabla 1
           NVL( A.DATA_TYPE, VNULL ) DATA_TYPE1, 
           TO_CHAR( A.DATA_LENGTH )  DATA_LENGTH1,
           NVL( A.NULLABLE,  VNULL ) NULLABLE1,

           -- Datos de la columna de la tabla 2
           NVL( B.DATA_TYPE, VNULL ) DATA_TYPE2, 
           TO_CHAR( B.DATA_LENGTH )  DATA_LENGTH2,
           NVL( B.NULLABLE,  VNULL ) NULLABLE2

    FROM   ALL_TAB_COLS A, ALL_TAB_COLS B
    WHERE  A.COLUMN_NAME = B.COLUMN_NAME
      AND  A.OWNER       = VOWNER1
      AND  A.TABLE_NAME  = VTABLA1
      AND  B.OWNER       = VOWNER2
      AND  B.TABLE_NAME  = VTABLA2
      AND  ( NVL( A.DATA_TYPE, VNULL )  != NVL( B.DATA_TYPE, VNULL ) OR
             A.DATA_LENGTH              != B.DATA_LENGTH             OR
             NVL( A.NULLABLE,  VNULL )  != NVL( B.NULLABLE,  VNULL ) 
            )
    ORDER BY A.COLUMN_ID;

  -----------------------------
  -- Funciones y procedimientos
  -----------------------------
  -- Di -------------------------------------------------------------
  PROCEDURE Di( cadena VARCHAR2 ) IS
    -- Procedimiento que hace un dbms_output.put_line cortando
    -- la cadena de entrada a 255 para evitar que de un error
    -- de línea demasiado larga.
  BEGIN
    DBMS_OUTPUT.PUT_LINE( SUBSTR( cadena, 1, 255 ) );
  END Di;

  -- diSeparador ----------------------------------------------------
  PROCEDURE diSeparador IS
    -- Procedimiento que escribe unas líneas que hacen las veces
    -- de separador de sección.
  BEGIN
    Di('.');
    Di('====================================================' );
  END;

  -- diDiferencia ---------------------------------------------------
  PROCEDURE diDiferencia( rDif tDiferencia ) IS 
    -- Procedimiento que escribe por pantalla una diferencia
  BEGIN
    Di( rDif.vColumna || '. En ' || VTABLA1 || ': ' || rDif.vValorA || ', ' 
                                 || VTABLA2 || ': ' || rDif.vValorB );
  END diDiferencia;
  
  -- diDiferencias -----------------------------------------------------------
  PROCEDURE diDiferencias( vDescri VARCHAR2, plDif tDiferencias ) IS 
    -- Procedimiento que escribe en pantalla la lista de diferencias
  BEGIN
    diSeparador();
    di( vDescri );

    IF plDif.LAST IS NOT NULL THEN 
      FOR nInd IN plDif.FIRST..plDif.LAST LOOP 
        diDiferencia( plDif( nInd ) );
      END LOOP;
    
    ELSE 
      Di( 'No ha diferencias' );
    END IF;
  END diDiferencias;

  -- agregaDiferencia -----------------------------------------------
  PROCEDURE agregaDiferencia( rDif tDiferencia, plDif IN OUT tDiferencias ) IS 
    -- Procedimiento que agrega una diferencia a una lista de diferencias.
    ------------
    -- Variables
    ------------
    -- Para el siguiente índice de la tabla
    nIndSiguiente BINARY_INTEGER;
  BEGIN
    IF plDif.LAST IS NULL THEN 
      -- La lista está vacía, me están pidiendo que agrege el primer elemento
      nIndSiguiente := 1;
    ELSE 
      nIndSiguiente := plDif.LAST + 1;
    END IF;

    plDif( nIndSiguiente ) := rDif;
  END agregaDiferencia;

  -- agregaDifSiExiste ----------------------------------------------
  PROCEDURE agregaDifSiExiste( vColumna        ALL_TAB_COLS.COLUMN_NAME%TYPE,
                               vValorA         VARCHAR2,
                               vValorB         VARCHAR2,
                               plDif    IN OUT tDiferencias
                             )
  IS 
    -- Procedimiento que agrega una diferencia si los valores son distintos
    ------------
    -- Variables
    ------------
    -- Variable de diferencia auxiliar
    rDifAux tDiferencia;
  BEGIN
    IF vValorA != vValorB THEN 
      rDifAux.vColumna := vColumna;
      rDifAux.vValorA  := vValorA;
      rDifAux.vValorB  := vValorB;

      agregaDiferencia( rDifAux, plDif );
    END IF;    
      
  END agregaDifSiExiste;

  -- diColumnasQueNoEstan -------------------------------------------
  PROCEDURE diColumnasQueNoEstan(
              vOwnerA ALL_TAB_COLS.OWNER%TYPE,
              vTablaA ALL_TAB_COLS.TABLE_NAME%TYPE,
              vOwnerB ALL_TAB_COLS.OWNER%TYPE,
              vTablaB ALL_TAB_COLS.TABLE_NAME%TYPE )
  IS
    -- Procedimiento que saca por pantalla la relación de columnas
    -- de la tabla o vista A que no están en la tabla o vista b

    -----------
    -- Cursores
    -----------
    -- Cursor que saca las columnas de la tabla o vista A
    -- que no están en la tabla o vista B
    CURSOR cNoColumnas  IS
      SELECT A.COLUMN_NAME
      FROM   ALL_TAB_COLS A
      WHERE  A.OWNER      = vOwnerA
        AND  A.TABLE_NAME = vTablaA
        AND  NOT EXISTS(
              SELECT 'X'
              FROM   ALL_TAB_COLS B
              WHERE  B.OWNER       = vOwnerB
                AND  B.TABLE_NAME  = vTablaB
                AND  B.COLUMN_NAME = A.COLUMN_NAME )
      ORDER BY A.COLUMN_ID;
  BEGIN
    diSeparador();

    Di( 'Columnas de '      || vOwnerA || '.' || vTablaA ||
        ' que no están en ' || vOwnerB || '.' || vTablaB ||
        ':');

    FOR rCol IN cNoColumnas LOOP
      Di( rCol.COLUMN_NAME );
    END LOOP;
  END diColumnasQueNoEstan;

  -- existeTablaOVista ---------------------------------------------------------
  FUNCTION existeTablaOVista( vPropietario VARCHAR2,
                              vTabla       VARCHAR2 ) 
  RETURN BOOLEAN IS 
    -- Función que comprueba se existe una tabla o vista con el propietario
    -- y nombre que le pasan como parámetro.
    ------------
    -- Variables
    ------------
    vAux VARCHAR2(1);
  BEGIN
    SELECT 'X'
    INTO   vAux
    FROM   ALL_OBJECTS 
    WHERE  OWNER       = vPropietario
      AND  OBJECT_NAME = vTabla 
      AND  OBJECT_TYPE IN ( 'TABLE', 'VIEW' );
    
    -- Si llego hasta aquí es que existe.
    RETURN TRUE;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      RETURN FALSE;
  END existeTablaOVista;

BEGIN
  -- Amplío el buffer del dbms_output para que no se quede pequeño
  DBMS_OUTPUT.DISABLE;
  DBMS_OUTPUT.ENABLE(10000000);
  
  IF existeTablaOVista( VOWNER1, VTABLA1 ) THEN  
    IF existeTablaOVista( VOWNER2, VTABLA2 ) THEN 
      bExistenAmbas := TRUE;
    ELSE 
      diSeparador();
      di( 'No existe la tabla o vista ' || VOWNER2 || '.' || VTABLA2 );
      bExistenAmbas := FALSE;
    END IF;
  ELSE 
    diSeparador();
    di( 'No existe la tabla o vista ' || VOWNER1 || '.' || VTABLA1 );
    bExistenAmbas := FALSE;
  END IF;

  -- Sólo continúo si existen ambas tablas:
  IF bExistenAmbas THEN
    
    -- Columnas de la tabla 1 que no están en la tabla 2
    diColumnasQueNoEstan( VOWNER1, VTABLA1, VOWNER2, VTABLA2 );

    -- Columnas de la tabla 2 que no están en la tabla 1
    diColumnasQueNoEstan( VOWNER2, VTABLA2, VOWNER1, VTABLA1 );

    -- Ahora recopilo las diferencias en las columnas que sí están en las dos
    -- tablas o vistas.
    FOR rDife IN cColumnasDistintoTipo LOOP 
      agregaDifSiExiste( rDife.VCOLUMNA, rDife.DATA_TYPE1, rDife.DATA_TYPE2, 
                         plDistintoTipo );
      
      agregaDifSiExiste( rDife.VCOLUMNA, rDife.DATA_LENGTH1, rDife.DATA_LENGTH2, 
                         plDistintaLongitud );

      agregaDifSiExiste( rDife.VCOLUMNA, rDife.NULLABLE1, rDife.NULLABLE2, 
                         plDistintoNulable );
    END LOOP;

    -- Pinto la lista de diferencias por cada tipo
    diDiferencias( 'Diferencias de tipo:',     plDistintoTipo );
    diDiferencias( 'Diferencias de longitud:', plDistintaLongitud );
    diDiferencias( 'Diferencias de nulable:',  plDistintoNulable );
  END IF;
END;
/
SET VERIFY ON 
Anuncios