Anuncios en tutorial de programación PLSQL

martes, 7 de enero de 2020

Cómo obtener información sobre los procedimientos, funciones y triggers PLSQL

Obtener información sobre Procedimientos y Funciones PL/SQL


La vista USER_PROCEDURES proporciona información sobre todas la funciones y procedimientos dentro de nuestro esquema, tanto a nivel de esquema como aquellas que se encuentran definidas dentro de los paquetes PL/SQL.

ALL_PROCEDURES

Las columnas más significativas dentro de esta vista son:
  • AUTHID: Identifica si el procedimiento o función se ejecutará con los permisos del usuario que lo llama (CURRENT_USER) o con los permisos del propietario del programa (DEFINER).
  • DETERMINISTIC: Esta columna toma el valor de YES si la función se ha definido como deterministas (deterministic), lo que teóricamente significa que el valor devuelto por la función PLSQL queda unívocamente determinado dependiendo de los valores que tomen los argumentos de la función.
  • PIPELINED: Cuando toma el valor de YES quiere decir que la función ha sido definida como pipelined, lo cual quiere decir que puede ejecutarse el paralelo como parte de una consulta con ejecución paralela.
  • OVERLOAD: Este campo tendrá un valor numérico positivo si el programa correspondiente esta overloaded, o lo que es lo mismo, cuando dentro del mismo paquete PLSQL existen al menos dos subprogramas con el mismo nombre (como podéis ver, la palabra overloaded casi nada tiene que ver con su traducción directa sobrecargado).

A continuación os dejo algunos ejemplos de consultas en las que se utiliza la vista USER_PROCEDURES y que os pueden resultar de utilidad.

La primera consulta permite encontrar todos los procedimientos y funciones PL/SQL que se ejecutan bajo el permiso del usuario que las invoca (los permisos del usuario que invoca un programa son determinados en el momento de la ejecución para resolver las referencias a otros objetos de la base de datos Oracle como pueden ser, por ejemplo, las propias tablas).
SELECT   object_name, procedure_name
    FROM   user_procedures
   WHERE   authid = 'CURRENT_USER'
ORDER BY   object_name, procedure_name
Con esta consulta se pueden encontrar todas las funciones que no son deterministas:
SELECT   object_name, procedure_name
    FROM   user_procedures
   WHERE   deterministic = 'NO'
ORDER BY   object_name, procedure_name

Obtener información sobre los triggers en una BBDD Oracle


Si en nuestra base de datos Oracle manejamos triggers (disparadores), la vista USER_TRIGGERS contiene información sobre todos los triggers definidos en nuestro propio esquema. Los campos clave de esta vista son:
  • TRIGGER_NAME: Nombre del trigger.
  • TRIGGER_TYPE: Este campo muestra información sobre si nos encontramos ante un trigger BEFORE o un trigger AFTER, y si dicho trigger está definido a nivel de registro o de sentencia SQL (por ejemplo, en un trigger que se dispara antes de la ejecución de una sentencia INSERT, el valor de esta columna será BEFORE STATEMENT).
  • TRIGGERING_EVENT: Indica el tipo de operación SQL que causa la ejecución del triger (los valores posibles serían INSERT, UPDATE, DELETE, INSERT OR UPDATE, INSERT OR DELETE, UPDATE OR DELETE, INSERT OR UPDATE OR DELETE).
  • TABLE_NAME: Esta columna contiene el nombre de la tabla sobre la que se ha definido el trigger.
  • STATUS: Indica el estado en que se encuentra el trigger, que puede ser ENABLED (habilitado) o DISABLED (deshabilitado).
  • WHEN_CLAUSE: Muestra el contenido de la cláusula WHEN que se utiliza para que el trigger solo se dispare cuando se cumplen una serie de condiciones.
  • TRIGGER_BODY: Contiene el código PL/SQL que se ejecuta cuando el trigger se dispara.

ALL_TRIGGERS

A continuación os dejos algunas consultas SELECT de utilidad que utilizan la vista USER_TRIGGERS.

Si queremos detectar todos los triggers de nuestro esquema que están deshabilitados:
SELECT   *
  FROM   user_triggers
WHERE   status = 'DISABLED'
Para encontrar el listado de todas los triggers sobre una determinada tabla y que se ejecutan a nivel de registro:
SELECT   *
  FROM   user_triggers
WHERE   table_name = '<nombre DE LA TABLA>' 
   AND   trigger_type LIKE '%EACH ROW'
Si lo que buscamos es identificar todos los triggers que se disparan durante la ejecución de un DELETE:
SELECT   *
  FROM   user_triggers
WHERE   triggering_event LIKE '%DELETE%'

Por otro lado conviene saber que una de las limitaciones de la vista USER_TRIGGERS es que el campo TRIGGER_BODY es de tipo LONG, por lo que no puede utilizarse en una comparación SQL. Así, por ejemplo, si queremos encontrar todos los triggers que contienen el texto “primary_flag” e intentamos ejecutar la siguiente consulta SELECT, veremos como en lugar de una serie de resultados se genera el error “ORA-00932: tipos de datos inconsistentes: se esperaba NUMBER se ha obtenido LONG”:
SELECT   *
  FROM   user_triggers
WHERE   trigger_body LIKE '%primary_flag%'
Por eso si queremos buscar un texto dentro del contenido de los cuerpos de los triggers de nuestro esquema, es necesario pasarlo de tipo LONG a tipo CLOB. Para hacerlo podemos hacer lo siguiente:
CREATE TABLE user_triggers_temp
AS
  SELECT   trigger_name
       ,   table_owner
       ,   table_name
       ,   status
       ,   TO_LOB (ut.trigger_body) AS trigger_body_clob
    FROM   user_triggers ut;


SELECT   *
  FROM   user_triggers_temp
WHERE   trigger_body_clob LIKE '%primary_flag%';

DROP TABLE user_triggers_temp;

Por otro lado, también es bueno saber que la vista USER_TRIGGER_COLS guarda la información relativa a las columnas a las que se hace referencia dentro del cuerpo de los triggers.

0 comentarios: