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.
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:
Publicar un comentario