Anuncios en tutorial de programación PLSQL

jueves, 6 de octubre de 2022

Cómo analizar la interdepencia entre los objetos de una base de datos Oracle

Para analizar y conocer las dependencias existentes entre funciones, procedimientos, paquetes y triggers a los que tiene acceso un usuario de la base de datos Oracle se debe utilizar la vista USER_DEPENDENCIES.

USER_DEPENDENCIES

Esta vista, sin ir más lejos, se puede utilizar para realizar un análisis de nuestro código PL/SQL, permitiendo por ejemplo identificar que programas necesitan ser revisados y actualizados si realizamos algún tipo de cambio sobre una tabla determinada de la base de datos.

Campos y consultas ejemplo con la vista USER_DEPENDENCIES

Los campos clave de la vista USER_DEPENDENCIES son los siguientes:

  • NAME: nombre del objeto de la base de datos Oracle.
  • TYPE: tipo de objeto.
  • REFERENCED_OWNER: propietario del objeto con el que tiene algún tipo de dependencia.
  • REFERENCED_NAME: nombre del objeto referenciado.
  • REFERENCED_TYPE: tipo del objeto referenciado.

A continuación os dejo algunos ejemplos de consultas SELECT que se pueden realizar sobre la vista USER_DEPENDENCIES.

Para encontrar todos los objetos de la base de datos que dependen o hacen referencia a una determinada tabla:

  SELECT   type,
           name
    FROM   user_dependencies
   WHERE   referenced_name = '[nombre de la tabla]'
ORDER BY   type,
           name

Para encontrar los objetos de un determinado esquema que dependen de un determinado paquete:
  SELECT   referenced_type,
           referenced_name
    FROM   user_dependencies
   WHERE   name = '[nombre del paquete]'
     AND   referenced_owner = '[nombre del esquema]' 
ORDER BY   referenced_type,
           referenced_name

Utilidad de la vista USER_DEPENDENCIES

Una buena práctica a la hora de programar en PLSQL que resulta altamente recomendable es evitar repetir los mismos comando SQL una y otra vez mediante su inclusión en procedimientos o funciones PLSQL. En este sentido la vista USER_DEPENDENCIES puede resultar de gran ayuda a la hora de identificar lo que podríamos considerar como “violaciones” de dicha buena práctica. Veamos a continuación un ejemplo.

En PL/SQL es muy común encontrar dentro del código muchas consulsultas SELECT que devuelven un único registro utilizando una clave primaria (primay key). Veamos un ejemplo utilizando una de las tablas más comunes de Oracle EBS:

PROCEDURE process_order (header_id_in IN NUMBER)
IS
  l_order_number
    ont.oe_order_headers_all.order_number%TYPE;
BEGIN
. . . . . . . . . . . . 
  SELECT   order_number
    INTO   l_order_number
    FROM   ont.oe_order_headers_all
   WHERE   header_id = header_id_in;
. . . . . . . . . . . .
END;

Mi recomendación cuando nos encontremos con este tipo de cosas mientras codificamos en PLSQL es escribir una función que contenga la consulta SELECT repetida y que devuelva el valor que necesitamos. De esta manera en nuestro procedimiento PLSQL llamaremos a dicha función cada vez que sea necesario.

Es decir, podemos crear un paquete PL/SQL denominado, por ejemplo, OE_ORDER_API que incluya una función llamada GET_ORDER_NUMBER. De esta manera el procedimiento anterior podría escribirse como sigue:

PROCEDURE process_order (header_id_in IN NUMBER)
IS
  l_order_number
    ont.oe_order_headers_all.order_number%TYPE;
BEGIN
. . . . . . . . . . . . 
  l_order_number := 
    oe_order_api.get_order_number(header_id_in);
. . . . . . . . . . . .
END;

De esta manera si por cualquier causa necesitamos cambiar la consulta SELECT por cualquier motivo (como por ejemplo si queremos utilizar la funcionalidad de almacenamiento cache de resultados ofrecida por la versión 11g de las bases de datos Oracle), solo tendremos que cambiar el código en un sito (en la función OE_ORDER_API. GET_ORDER_NUMBER), en lugar de tener que buscar todas las ocurrencias de la consulta SELECT in todo el código PLSQL.

Ahora supongamos que la mencionada buena práctica anterior ha sido incluida en los estándares de desarrollo de nuestra empresa y nuestro equipo de desarrollo necesita implementarla en todo el código PLSQL propio. Supongamos también dichos estándares señalan que las únicas unidades de programa que pueden contener sentencias SQL son los paquetes que terminan con el sufijo _API.

Llegado este punto podemos escribir una consulta SELECT sobre la vista USER_DEPENDENCIES que identifique todas las unidades de programa que violan esta norma:

  SELECT   name,
           TYPE,
           referenced_owner,
           referenced_name
    FROM   user_dependencies
   WHERE       TYPE IN ('PACKAGE', 
                        'PACKAGE BODY',
                        'PROCEDURE', 
                        'FUNCTION', 
                        'TRIGGER', 
                        'TYPE')
           AND referenced_type = 'TABLE'
           AND name NOT LIKE '%\_API' ESCAPE '\'
ORDER BY   name, 
           referenced_owner, 
           referenced_name

0 comentarios: