Archive

Archive for the ‘SQL’ Category

Procesamiento de imagenes en Oracle Database 11g

diciembre 19, 2013 Deja un comentario

Existen varias opciones para almacenar imágenes en BBDD Oracle. La manera más común es utilizar una columna de tipo BLOB y cargar una imagen como contenido binario.

A partir de la versión 11g, Oracle ha introducido Oracle Multimedia (anteriormente conocido como Oracle InterMedia), una extensión que permite guardar imágenes, audio, vídeo y otra información multimedial.

Oracle Multimedia introduce los objetos de tipo ORDAudio, ORDDoc, ORDImage, ORDVideo, and SI_StillImage y métodos para:

  • Extraer metadatos y atributos de datos multimediales
  • Incrustar metadatos generados por otras aplicaciones en ficheros imagen
  • Incorporar datos multimediales a partir de Oracle Multimedia, web servers, file systems, y otros servidores
  • Ejecutar operaciones y aplicar transformaciones a imágenes

En otras palabras, Oracle Multimedia permite manipular imágenes con una simple instrucción SQL.

En este post, el enfoque será en las operaciones que se pueden realizar sobre imágenes.

Empezamos creando una tabla donde guardaremos una imagen en formato TIFF.

CREATE TABLE AVT_IMG
(
ID NUMBER,
TIFF_IMG ORDSYS.ORDImage
);

Además se crea un directorio que apunte a la ubicación de las imágenes.

CREATE OR REPLACE DIRECTORY avt_img_dir as 'C:\Imagenes_Blog';

Directorio Imagenes
Construimos un objeto ORDImage a partir de la imagen y lo insertamos en la tabla que acabamos de crear.

Leer más…

Normalización de cadenas de texto en PL/SQL

octubre 16, 2013 5 comentarios

La normalización de cadenas de texto es uno de los temas recurrentes en el desarrollo de procesos ETL. Cuando se tratan datos anagráficos, es muy común encontrarse con duplicados debidos a errores tipográficos y de data entry.

Consideremos el siguiente ejemplo:

Ejemplo Datos Duplicados

Se puede ver que los dos registros se refieren a la misma persona física, pero hay elementos que impiden considerar los registros como duplicados perfectos. El NIF ha sido transcrito mal. Es presumible que el error venga de una introducción manual de los datos ya que la N y la H están muy cerca en el teclado español. Tanto el nombre como el domicilio aparecen en dos formatos distintos, mientras que en el caso de la población es el acento el que hace que no coincidan las dos cadenas.

¿Qué herramientas tenemos en PL/SQL para matchear los dos registros y eliminar los duplicados?

La función SOUNDEX devuelve una representación fonética de una cadena. El resultado es la codificación de cómo se pronunciaría un texto en inglés. A pesar de que estemos trabajando con texto en otro idioma, la función puede resultar útil.

Si la aplicamos por ejemplo a la población, tendremos:

SELECT POBLACIO, SOUNDEX(POBLACIO)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

Ejemplo SOUNDEX

En este caso, el código resultante es el mismo y podríamos utilizarlo para considerar que tienen igual valor.

En otros casos, como el nombre, el resultado de la función SOUNDEX no nos ayuda.

SELECT NOMDES, SOUNDEX(nomdes)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

El package UTL_MATCH contiene unas funciones desarrolladas para facilitar la detección de duplicados. Hay cuatro funciones:

  • EDIT_SIMILARITY: la función calcula la distancia de Levenshtein. Esta medida debe el nombre al científico ruso que desarrolló un algoritmo para medir la distancia entre dos cadenas de texto s1 y s2. La distancia se calcula como numero de inserciones, cambios, cancelaciones de caracteres que permiten pasar de la cadena s1 a la cadena s2. La función devuelve un número que representa la distancia entre las dos cadenas: 0 indica dos cadenas idénticas. En el ejemplo, si asumimos que el formato correcto es “NOMBRE APELLIDO”, la función devuelve:
SELECT nomdes, utl_match.edit_distance(NOMDES, 'SCOTT TIGER')
FROM clientes
WHERE NOMDES LIKE '%SCOTT%'Ejemplo EDIT_DISTANCE

La distancia entre las dos cadenas no es muy grande y podríamos considerar las dos cadenas como coincidentes.

  • EDIT_DISTANCE_SIMILARITY: actúa como la función EDIT_SIMILARITY pero devuelve un valor normalizado entre 100 (cadenas coincidentes) y 0 (total discordancia).
  • JARO_WINKLER: esta función utiliza el algoritmo de Jaro-Winkler que calcula un índice de similitud entre dos cadenas, para intentar tener en cuenta posibles errores tipográficos. El valor 1 representa dos cadenas coincidentes.
    Ejemplo JARO-WINKLER
  • JARO_WINKLER_SIMILARITY: la función utiliza el algoritmo de Jaro-Winkler y normaliza los resultados entre 0 y 100.

Las funciones del paquete UTL_MATCH permiten definir un umbral de aceptabilidad para establecer si dos cadenas coinciden “lógicamente” y pueden resultar una herramienta muy útil en el proceso de data cleansing.

Categorías:SQL Etiquetas: , ,

Columnas Virtuales – Metadatos en estructuras físicas

marzo 1, 2011 2 comentarios

La evolución del motor de base de datos Oracle se nutre de tres fuentes:

  • Resolución a errores (bugs) y consolidación de funcionalidades incompletas de versiones anteriores.
  • Incorporación de soluciones a nuevas necesidades tecnológicas y a tendencias estratégicas en el tratamiento de la información.
  • Propuestas de nuevas funcionalidades surgidas en el departamento de I+D+I de Oracle.

Como parte de las nuevas características y funcionalidades incorporadas en la versión 11g del gestor de base de datos Oracle, a continuación se detallará e ilustrará con ejemplos una de ellas: las Columnas Virtuales.  Crean una nueva tendencia en cuanto a la definición de objetos mixtos, conformados por una parte física y otra lógica, ambas combinadas en un mismo objeto de base de datos.

En términos teóricos se definiría como la consecución de la unificación entre una estructura física (como es la de tabla) y una estructura lógica o metadato. Un ejemplo de este tipo de estructura, pero de definición global, serían las vistas (view).

Si por un lado las estructuras lógicas o metadatos evolucionaron hacia estructuras físicas, como es el caso de vistas a vistas materializadas, en este caso la propuesta va más allá e incorpora la posibilidad de incluir columnas calculadas o metadatos como parte de una estructura física (table).

Este tipo de columnas, definidas en tablas e integradas dentro del diccionario de datos, van asociadas a funciones, dotando de esta forma a una estructura física de propiedades de elementos lógicos (metadatos). Pueden ser definidas como la derivación de un cálculo aplicado a una columna existente de la tabla (de la cual deriva).

Leer más…

Categorías:SQL Etiquetas: , ,

Recursividad en PL/SQL (II) – Implementación alternativa

noviembre 28, 2010 1 comentario

Ref. ”Recursividad en PL/SQL – Un ejemplo

Como bien se identifica en el ejemplo de la referencia, se está tratando con datos existentes en la BBDD que tienen estructura jerárquica. También se identifica una relación unívoca entre un ‘maestro’ / ‘detalle’ (‘Carpeta’ vs ‘Documento’) y operaciones donde si se actúa sobre la entidad ‘maestro’ implica acciones sobre la ‘detalle’ –operación de ‘DELETE’-.

Partiendo de esas dos premisas iniciales, a continuación se expone una propuesta de nuevo modelo de programación a partir del caso del ejemplo de la referencia que, aunque considerándose como un ejemplo muy acertado, permitirá ver otro tipo de soluciones más óptimas

1.- Uso del Modelo Relacional

Por definición, si un registro de ‘Carpeta’ es borrado, carece de sentido la existencia de registros asociados en ‘Documento’. Es más, por integridad referencial no es posible borrar una carpeta que tenga documentos vinculados.

Por lo tanto es recomendable modificar la relación establecida y definida a nivel de ‘CONSTRAINT’ de clave foránea -‘FK’- :

Leer más…

Categorías:SQL Etiquetas: , , , ,

Evitar errores de “tabla mutante” en Oracle Database

noviembre 2, 2010 2 comentarios

Hace poco me preguntaron en un cliente cómo podían evitar la aparición del error:

ORA-04091: table xxx.yyyy is mutating, trigger/function might not see it

Primero os comento un poco qué es una “tabla mutante“. Se considera “mutante” una tabla que está siendo modificada, por ejemplo la tabla sobre la que el propio trigger se ha disparado por causa de un update/insert/delete.

O lo que es lo mismo, dentro de un trigger no podemos hacer referencia a la misma tabla que lo ha disparado, ya que, al estar siendo  modificada, Oracle no puede asegurar una visión “consistente” de sus datos. En caso de hacerlo aparece este error.

Correcto, ¿y qué podemos hacer? Pues hay varias soluciones:

Hasta la versión 10gR2 podíamos:

Cambiar el código y no usar triggers: Podría ser que se pudiese solucionar modificando el diseño del modelo de datos o reprogramando una parte del codigo.

Usar un trigger “AFTER”: En “AFTER” los cambios en la tabla ya estan “consumados” y podemos acceder a una visión “consistente” de ésta.

Usar “PRAGMA AUTONOMOUS_TRANSACTION”: Nuestro trigger se ejecuta en una transacción diferente, y por tanto vemos la tabla en modo “consistente”. No es muy recomendable usar este sistema, ya que por ejemplo:

  • Si queremos realizar varios cambios seguidos dentro de la misma transacción no tendremos acceso a ellos (es una transacción diferente cada vez)
  • O si el trigger falla y se hace rollback tampoco nos enteraremos (no deshará la transacción en que se ha disparado el trigger)

A partir de la versión 11gR1, a las anteriores opciones podemos añadir:

Usar “COMPOUND TRIGGERS”: En la versión 11gR1 ha aparecido un nuevo tipo de trigger llamado “Compound Triggers“. En estos triggers podemos realizar cálculos previos para definir un “estado” que después es accesible durante la ejecución del trigger.

En resumen, que los cálculos que queramos hacer sobre la tabla afectada por el trigger los haremos previamente, guardando los valores necesarios en variables a las que accederemos posteriormente (evitando el acceso directo a la tabla y por tanto el problema). No nos soluciona todos  los casos de tablas mutantes pero sí una parte importante de ellos.

El ejemplo que viene en la documentación es bastante bueno, por lo que os lo referencio directamente: Example 9-4 Compound Trigger that Avoids Mutating-Table Error

PD: No dejéis de estudiar qué otras ventajas ofrecen los “compound triggers”, pues la solución a las tablas mutantes sólo es una consecuencia de sus funcionalidades 😉

Categorías:SQL Etiquetas: , , ,

Recursividad en PL/SQL – Un ejemplo

abril 21, 2010 8 comentarios

El PL/SQL, como la mayoría de lenguajes modernos, nos permite implementar algoritmos recursivos en nuestros procesos. En los modelos relacionales de base de datos, es frecuente encontrarse con estructuras reflexivas. La recursividad nos facilita la programación de tareas que afectan a estos tipos de construcciones. A continuación presentamos un escenario donde aplicando recursividad disminuye la complejidad del proceso.

Hemos diseñado un sistema sencillo que almacena documentos en carpetas. El modelo simplificado de nuestro sistema es el siguiente:

Modelo "Carpetas"

Como se puede observar, una carpeta puede contener otras carpetas, y estas a su vez, otras. El usuario puede guardar una estructura como la que sigue:

Estructura de carpetas

Leer más…

Categorías:SQL Etiquetas: , ,

Leer un archivo de texto mediante una consulta de BBDD

enero 12, 2010 15 comentarios

En Oracle existen las tablas externas que nos permiten vincular archivos de texto a tablas de base de datos. Este sistema es útil en numerosos casos, pero tiene el inconveniente que hay que declarar la estructura del archivo (columnas, separador, nulos…), tarea a menudo laboriosa. A veces sólo nos interesa poder consultar los datos de un archivo de forma ocasional. Vamos a detallar un método que nos permitirá leer mediante una consulta el contenido de un archivo sin necesidad de declarar tablas externas.

La solución propuesta consiste en la creación de una función que lee el archivo y devuelve el contenido de éste en un array. Para conseguir mejores resultados en el caso de archivos grandes, usaremos el método de las “pipelined table functions”, que nos permitirá ir devolviendo las líneas a medida que son leídas desde el archivo.

El primer paso será crear un tipo array de varchar2.

CREATE TYPE Ttb_archivo_texto AS TABLE OF VARCHAR2(4000);

A continuación creamos la función que lee el archivo. La función recibirá por parámetro el directorio de lectura y el nombre del archivo a leer. Devolverá el contenido del archivo en un array del tipo que acabamos de crear.

CREATE FUNCTION LEER_ARCHIVO (P_DIRECTORIO    VARCHAR2,
                              P_ARCHIVO       VARCHAR2
                             ) RETURN Ttb_archivo_texto PIPELINED AS
  vArchivo    utl_file.file_type;
  vLinea      varchar2(4000);
BEGIN
  vArchivo := utl_File.fopen (P_DIRECTORIO, P_ARCHIVO, 'R');

  -- Leemos cada una de las líneas del archivo y la retornamos
  Loop
    Begin
      utl_file.get_line (vArchivo, vLinea);
    exception
      when NO_DATA_FOUND then
        exit;
    end;

    Pipe row (vLinea);
  end loop;

  utl_file.fclose (vArchivo);

  return;
END;
/

Ya podemos usar la función en una consulta. Para ello, vamos a llamarla en la cláusula FROM utilizando la función pl/sql “table”:

Select COLUMN_VALUE
  from table (LEER_ARCHIVO ('OUT_SALDSV', 'test.txt'));

Nota: COLUMN_VALUE  es el nombre que recibe por defecto la columna devuelta por la función de tabla.