Auditoría de tablas mediante triggers con información sobre el usuario

Uno de los usos de los triggers puede ser el de auditar los cambios que se realizan en los datos de nuestras tablas. Entre la información que podríamos considerar relevante, se encuentra la del autor de dichos cambios. El problema es que, en el contexto de ejecución de un trigger, sólo podemos tener acceso al usuario de base de datos que realiza el cambio, por ejemplo usando una consulta como esta:
SELECT user
INTO vusuario
FROM dual; 
Si nuestra aplicación gestiona sus propios usuarios (los cuales supongamos que se conectan a las aplicaciones mediante uno o varios esquemas de Oracle prefijados) no tendremos acceso al usuario real que realiza un cambio dentro del trigger puesto que estos no tienen parámetros. Hoy veremos cómo conseguir que en el contexto de un trigger podamos acceder de forma sencilla y eficiente a la información del usuario real (no el de base de datos) que está realizando un cambio en nuestra tabla y así podamos auditarlo.

Para empezar, necesitaremos desarrollar un paquete que gestione los usuarios que utilizan las aplicaciones. Debería tener al menos algo como esto:
CREATE OR REPLACE PACKAGE datos_usuario
AS

/*******************************************************************************
-- PROCEDURE set_usuarioTriggers
--
-- Descripción: establece la variable interna de usuario para usar en triggers
--
-- Entrada:    pe_usuario: usuario de la aplicación
--
------------------------------------------------------------------------------
******************************************************************************/
PROCEDURE set_usuarioTriggers(
    pe_usuario IN  VARCHAR2 -- 255
);

   
/******************************************************************************
-- FUNCTION get_usuarioTriggers
--
-- Descripción: establece la variable interna usuario para usar en triggers
--
-- Salida:      usuario actual de la aplicación
--
******************************************************************************/
FUNCTION get_usuarioTriggers RETURN VARCHAR2; -- 255



END datos_usuario;
/

CREATE OR REPLACE PACKAGE BODY datos_usuario
AS

-- Variable que contendrá el usuario para usar en triggers
-- Se debe inicializar cada vez que un usuario se conecta
-- A la base de datos desde una aplicación
usuarioTriggers    VARCHAR2(255);


PROCEDURE set_usuarioTriggers(pe_usuario  IN VARCHAR2) IS

BEGIN
    usuarioTriggers := pe_usuario;
END set_usuarioTriggers;


FUNCTION get_usuarioTriggers RETURN VARCHAR2 IS

BEGIN
    RETURN usuarioTriggers;
END get_usuarioTriggers;


END datos_usuario;
/ 
Al inicio de nuestras aplicaciones habrá que invocar la función  datos_usuario.set_usuarioTriggers pasándole como parámetro el usuario real que la está utilizando.

Para terminar, desde el trigger de auditoría tendremos acceso al valor de dicho usuario llamando a la función datos_usuario.get_usuarioTriggers. Por ejemplo, algo como esto:

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
    INSERT INTO job_history(
        employee_id, start_date, end_date,
        job_id, department_id, user_id, date_id)
    VALUES(
        :OLD.employee_id, :OLD.start_date, :OLD.end_date,
        OLD.job_id, :OLD.department_id, datos_usuario.get_usuarioTriggers, SYSDATE);
END;
/

Aclaraciones sobre el envío de emails con ficheros adjuntos

En relación a un artículo publicado anteriormente sobre envío de emails, me gustaría hacer las siguientes aclaraciones:

1) Si queremos adjuntar un fichero dentro de un directorio en el servidor, es obligatorio usar un directorio de Oracle previamente creado. No podemos referenciar un directorio del servidor directamente.

Por ejemplo: si tenemos que leer archivos que se encuentren en la ruta /imagenes, habrá que crear el directorio previamente de esta manera:
CREATE OR REPLACE DIRECTORY images_dir AS '/imagenes'
/

Para leer los archivos desde nuestros programas, es necesario otorgar el permiso correspondiente al usuario (siendo usuario el usuario que leerá los adjuntos):
GRANT READ ON DIRECTORY images_dir TO usuario
/ 

También hay que tener el privilegio de sistema CREATE ANY DIRECTORY para crear un directorio de Oracle.

2) Por otra parte, el directorio del cual leer (/imagenes en este caso) debe existir en el servidor de base de datos y tener al menos el permiso de lectura correspondientes, igual que los archivos que contenga. De no ser así, el procedimiento de envío de emails no será capaz de leerlos. Cómo se hace esto ya depende del sistema operativo del servidor.

3) Si inicialmente no tenéis acceso directo al servidor pero queréis hacer pruebas, es posible crear con PLSQL un fichero con información trivial para utilizarlo desde tus programas. Algo así como:
-- Añade permiso de escritura sobre el directorio Oracle, si no lo tenía
GRANT WRITE ON DIRECTORY images_dir TO usuario
/

-- Crea un fichero de prueba con el texto Texto de prueba
DECLARE
    l_output UTL_FILE.file_type;
    v_buffer VARCHAR2(255);

BEGIN
    -- define output directory
    l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','a');
    UTL_FILE.PUT_LINE(l_output,'Texto de prueba');
    UTL_FILE.fflush(l_output);
    UTL_FILE.fclose(l_output);

    -- Con esto me aseguro de que el fichero ya lo tengo
    l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','r');
    UTL_FILE.GET_LINE(l_output,v_buffer);
    UTL_FILE.fclose(l_output);
  
    DBMS_OUTPUT.PUT_LINE(v_buffer);
END;
/ 

Ahora ya es posible realizar una prueba con adjuntos:
DECLARE
    v_ret NUMBER;
    v_error VARCHAR2(1024);

    v_attachs        Pkg_Emails.t_attachments;
    v_replies        UTL_SMTP.replies;

BEGIN
    v_attachs(0).mimetype := 'text/plain';
    v_attachs(0).filename := 'prueba.txt';
    v_attachs(0).DIRECTORY:='IMAGES_DIR';

    v_ret := Pkg_Emails.send_mail_with_attachments(
        pe_sender            => 'MiEmail@MiDominio',
        pe_recipients        => 'TuEmail@TuDomicio',
        pe_subject            => 'Asunto de la prueba con adjunto',
        pe_message            => 'Texto de la prueba con adjunto',
        pe_attachments        => v_attachs,
        ps_replies            => v_replies);

    -- Ha habido algún error
    IF v_replies.COUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE(v_replies(0).text);
    END IF;
END;
/ 

Un detalle que puede parecer trivial pero nos puede dar muchos quebraderos de cabeza si no lo hacemos bien: si hemos creado el directorio Oracle usando un identificador sin usar comillas dobles, es obligatorio referirnos al mismo, dentro de nuestros programas, en mayúsculas. Por tanto, no es casual que se hayan usado mayúsculas en estas instrucciones PLSQL anteriores:
l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','a');
v_attachs(0).DIRECTORY:='IMAGES_DIR';

Unas cuantas utilidades para desarrolladores: Forms Tool

Hoy os quiero presentar una aplicación que, aunque la versión que utilizo ya tiene unos años, aún me resulta muy útil para trabajar con Forms y Reports, especialmente si utilizáis controladores de versiones tipo CVS o SVN. Estoy hablando de Forms Tool, de la compañía ORCL TOOLBOX:

Se trata de una herramienta que nos ofrece una serie de utilidades para los desarrolladores de Forms y Reports, a saber:

1. Comparación de objetos creados con Oracle Developer: forms (FMB), reports (RDF), librerías (PLL), librerías de objectos (OLB), scripts de PL/SQL (SQL) y menús (MMB):

La herramienta nos muestra visualmente las diferencias entre dos objetos que queramos comparar, pudiendo además eliminar o añadir lo que sobra o falta de manera visual y muy sencilla:

Esta herremienta la utilizo con cierta frecuencia para poder documentar los cambios entre versiones de objetos.

2. Herramienta de búsqueda, llamada Power Search, permite hacer búsquedas muy sofisticadas sobre un conjunto de ficheros con objetos de Oracle Developer dada una cadena de búsqueda o un valor de una propiedad, pudiend incluso utilizar expresiones regulares:

3. Developer Report: nos permite generar un completo informe sobre objetos forms, reports, etc.:

4. Por último, desde la versión V3.0 cuenta una herramienta de procesamiento de módulos por lotes, muy útil cuando queramos realizar ciertas tareas sobre un grupo de módulos, como compilar, convertir o extraerlos en uno sólo.

Para más información, podéis visitar la web de ORCL TOOLBOX.

Tooltips basados en items

Tras más de 2 años, vuelvo a publicar una entrada en mi blog. De ahora en adelante espero poder actualizar con más frecuencia.

Hoy quiero hablaros sobre los tooltips, esas ayudas textuales que aparecen cuando posicionamos el puntero del ratón sobre un item en Forms. A veces se nos da el caso de que hay que poner una ayuda de burbuja (tooltip) a un item cuyo valor no es fijo, sino que depende del valor de del mismo item u otro diferente. La manera lógica de proceder es añadir, en el trigger WHEN-NEW-RECORD-INSTANCE del bloque que contiene el item, un script como este: 

SET_ITEM_PROPERTY('BLOQUE.ITEM',TOOLTIP_TEXT,:BLOQUE.ITEM);

En este caso la ayuda de tooltip es el propio valor del item (útil por ejemplo si no tenemos espacio para mostrar el item en toda su anchura) y esto en principio funciona bien. El problema lo tendríamos si el texto de ayuda depende de otro item, en cuyo caso cuando nos cambiemos de fila el texto de ayuda no se actualizará. Para solucionarlo hay que hacer lo siguiente:

1. Añadir el código del trigger como se ha mencionado antes, es decir:  

SET_ITEM_PROPERTY('BLOQUE.ITEM',TOOLTIP_TEXT,:BLOQUE.ITEM2);

2. En la propiedad Ayuda de burbuja (Tooltip) poner el nombre del item que queremos mostrar, sin usar los dos puntos(:):

Con esto conseguiremos que el valor del texto se vaya actualizando conforme cambiamos de registro.

Alertas de más de 255 caracteres

A veces he tenido la necesidad de presentar mensajes más largos que los 255 caracteres que nos permiten las alertas de Oracle Forms. Por ello hoy os voy a presentar una función, que con la ayuda de un formulario, podréis visualizar alertas con mensajes de hasta 4000 caracteres.

Cursores con actualización óptima de filas sin bloqueos

En la entrada de hoy hablaré sobre un truco mediante el cual podremos actualizar los campos obtenidos de un cursor, de manera óptima, sin necesidad de bloquear las filas de las tablas referenciadas por el cursor.

Consultas SQL con datos ordenados y paginación de resultados

Hoy voy a hablar de una cuestión que es aparentemente sencillo de afrontar pero que puede llegar darnos muchos quebraderos de cabeza.

Supongamos que queremos obtener el identificador de departamento y nombre de la tabla de departamentos ordenados por el nombre de manera descendente. La sentencia SQL sería algo así:

SQL> SELECT department_id, department_name 
  2  FROM DEPARTMENTS
  3  ORDER BY department_name DESC; 

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          120 Treasury
           50 Shipping
          150 Shareholder Services
           80 Sales
          250 Retail Sales
          260 Recruiting
           30 Purchasing
           70 Public Relations
          270 Payroll
          200 Operations
          220 NOC

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           20 Marketing
          170 Manufacturing
          210 IT Support
          230 IT Helpdesk
           60 IT
           40 Human Resources
          240 Government Sales
          100 Finance
           90 Executive
          130 Corporate Tax
          140 Control And Credit

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          190 Contracting
          180 Construction
          160 Benefits
           10 Administration
          110 Accounting

27 filas seleccionadas. 

Envío de emails con ficheros adjuntos

Es posible que sepáis que desde hace bastante tiempo, concretamente desde la versión 8.1.7, Oracle nos brinda la posibilidad de hacer envíos de emails desde la base de datos gracias al paquete UTL_SMPT. En él tenemos implementadas las funcionalidades necesarias para establecer una conversación entre el servidor de base de datos y un servidor de correo SMTP que tengamos accesible.

No obstante, la implementación del protocolo de conversación requiere conocer bastante al detalle cómo funciona SMTP; por ello Oracle incluyó en sus distribuciones un paquete de demostración llamado DEMO_MAIL que nos facilitaba bastante las cosas. Más adelante, desde la versión 10g, tenemos a nuestra disposición el paquete UTL_MAIL que es aún nos lo pone más fácil.

Sin embargo, ninguna de estas implementaciones permite enviar un email de manera sencilla para el caso de emails con adjuntos, de manera que sólo haya que indicar el nombre del fichero y ocasionalmente su tipo mime; en la implementación que os presento es así, pero no hay que olvidar que el fichero debe estar almacenado en el propio servidor de base de datos.

Simulación de la sentencia continue en PL/SQL

En ocasiones se nos da la circunstancia de que dentro de bucle nos pude interesar ejecutar cierta parte del código siempre que se cumpla una condición, no haciendo nada más en esa iteración en caso contrario. Lo normal sería hacerlo más o menos así (en todos estos suspuestos se sobreentiende que tenemos al menos una condición de salida para el bucle):

(...)
LOOP
    (...)
    IF test THEN
        -- Código a ejecutar
        (...)
    END IF;
END LOOP;

(...)


La cosa se complica si tenemos varios de esos tests:

(...)
LOOP
    (...)
    IF test1 THEN
        -- Código a ejecutar test1
        (...)

        IF test2 THEN
            -- Código a ejecutar test2
            (...)
        END IF;

        (...)
    END IF;
END LOOP;
(...)


De aquí se puede llegar a la conclusión de que si son muchas las condiciones a comprobar, el programa puede llegar a ser bastante ilegible puesto que se hacen muchos anidamientos de sentencias IF.

Insertar imágenes en Oracle Reports

Existen varias maneras de incluir imágenes en nuestros reports. En este artículo os hablaré de algunas de esas  posibilidades.

La primera consiste en incrustar directamente la imagen. Para ello nos vamos al diseño del informe y desde el menú Insertar → Imagen seleccionamos la imagen que deseemos. Esta manera es la más sencilla, pero tiene dos inconvenientes, sobre todo si utilizamos una misma imagen (como un logotipo) en muchos reports diferentes: el tamaño del fichero del report aumenta puesto que la imagen va incrustada, y si en un momento dado hubiera que sustituir la imagen por otra habría que modificar los reports uno a uno.

Commit sin validación previa

He aquí lo que dice Oracle sobre la instrucción COMMIT cuando se ejecuta en un formulario de Forms:
“Causes Oracle Forms to update data in the database to match data in the form. Oracle Forms  first validates the form, then, for each block in the form, deletes, inserts, and updates to the database, and performs a database commit. As a result of the database commit, the database releases all row and table locks.”
Finalmente advierte lo siguiente:
“If you use a PL/SQL COMMIT statement in an anonymous block or a form-level procedure, Oracle Forms interprets that statement as a call to the COMMIT_FORM built-in”. Resumiendo, no es posible hacer un COMMIT sin evitar la validación de los datos modificados en los bloques.

Cómo crear en Forms un canvas tabular con scrollbar vertical y horizontal

Hola a todos y bienvenidos a este espacio web en el cual pretendo compartir con la comunidad mis conocimientos sobre PL/SQL, Oracle Forms y Reports fruto de 10 años de experiencia con estas herramientas.

Mi intención no es más que dar a conocer ciertos trucos o técnicas que pueden servir de gran ayuda sobre todo a los que lleváis poco tiempo trabajando con ellas, aunque en ocasiones es posible que resulte interesante a los más expertos. En cualquier caso, espero que os sea de utilidad.

Hoy voy a empezar explicando cómo se le puede poner a un canvas tabular barras de desplazamiento horizontales y verticales al mismo tiempo. Algo tan aparentemente trivial no lo es en Forms, puesto que en principio es posible hacerlo de manera sencilla, pero luego no funciona como es de esperar.