PDF de programación - Manejo de Oracle Large Objects (LOB)

Manejo de Oracle Large Objects (LOB)

Por Francisco Riccio



Introducción

Oracle desde la versión 8i nos provee un tipo de dato llamado LOB, el cual nos permite almacenar largas

estructuras de información estructurada y no estructurada como texto , gráficos, audio y video.

Asimismo la información multimedia puede residir tanto en la misma base de datos como en el sistema

operativo. Este tipo de dato se crea en reemplazo a los tipos de datos antiguos que existían como:

LONG, RAW y LONG RAW debido a todos las restricciones y problemas de mantenimiento que

presentaban.

La capacidad máxima que un LOB puede albergar es de 4 GB.

Los LOBS se categorizan en CLOB (almacenan texto que contiene grandes cantidades de bytes), NCLOB

(es similar al CLOB solo que almacena texto cuyo juego de caracteres está definido por el National

Character Set de la base de datos), BLOB (almacena información multimedia dentro de la base de datos),

BFILE (similar al BLOB solo que la información multimedia está almacenada en el sistema operativo).

La versión Oracle Database 11g ha hecho varias mejoras sobre los LOB presentando una nueva

propuesta llamada Secure Files, el cual entrega mejores tiempos de respuesta en el acceso a los datos,

ahorro en espacio y seguridad.

Implementación

Todo tipo de dato LOB tiene dos partes con que trabajaremos:

a) LOB Value, el cual constituye el valor a almacenar por ejemplo: un texto o contenido multimedia.

b) LOB Locator, es un puntero a la ubicación del valor LOB (LOB Value) que es almacenado en la base de

datos.

Si el texto o información multimedia se guarda dentro de la base de datos, el contenido se almacena en

un segmento separado de la tabla. Este segmento es de tipo LOB y almacena solo el LOB Value mientras

la tabla que se definió con él campo LOB solo lleva el LOB Locator como puntero al segmento LOB.



CLOB

Los CLOB almacenan texto que contienen grandes cantidad de bytes. Reemplaza al tipo de dato LONG.

Existe automáticamente una conversión implícita entre los CLOB y VARCHAR2.

Crearemos una tabla con un campo CLOB:



1





Oracle recomienda que inicialicemos un campo CLOB con un LOB Locator vació y no dejarlo como NULL.

Para realizar esto podemos hacerlo mediante la función EMPTY_CLOB() desde la creación de la tabla o

después, por ejemplo:

ó







Nota: La función EMPTY_CLOB asegura que no habrá ningún valor en el campo mientras el valor NULL

almacena el valor NULO.

Durante la creación de la tabla que alberga campos CLOB, podemos indicar que el LOB Value (contenido)

sea almacenado en otro tablespace que es lo más recomendable, ejemplo:



En el ejemplo definimos que el contenido multimedia será almacenado en el tablespace EXAMPLE, por

lo cual se creará un segmento de tipo LOB en dicho tablespace. Podemos validar está información en la

vista DBA_SEGMENTS, ejemplo:









2

Para insertar un valor sobre un campo CLOB lo hacemos tan similar como si fuera un campo VARCHAR,

ejemplo:



Revisaremos algunas funciones útiles:



a) Si deseamos obtener una parte del contenido de un CLOB usamos la función DBMS_LOB.SUBSTR,

ejemplo:

En este ejemplo obtenemos desde la posición 12 del texto 5 caracteres.





Nota: Esta función también trabaja con los tipos de datos BLOB y BFILE.

b) Si deseamos obtener la posición de un texto usamos la función DBMS_LOB.INSTR, ejemplo:

En este ejemplo conseguimos la posición de la letra "e" en su segunda ocurrencia a partir del primer





3

carácter del texto.



c) Si deseamos agregar más texto a un CLOB usamos la función DBMS_LOB.WRITEAPPEND, ejemplo:

Donde la función DBMS_LOB.WRITEAPPEND pide de parámetro la variable CLOB a modifcar, la cantidad

de caracteres y el texto a añadir.







BLOB

Reemplaza al tipo de dato LONG RAW y almacena el contenido multimedia dentro de la base de datos.

Para trabajar con BLOB y BFILES (más adelante se especifica) se requiere de Objetos Directorios en la

base de datos. Los Objetos Directorios no son objetos que le pertenecen a un esquema, todos los

directorios creados son adueñados por el usuario SYS. Para crear directorios necesitamos el privilegio de

sistema CREATE ANY DIRECTORY. Estos Objetos Directorios serán una referencia a una ubicación de un

directorio del sistema operativo.

Su sintaxis es la siguiente:

SQL> create or replace directory <nombre_directorio> as '<ruta_so>';

Ejemplo:

Donde podemos entregar permisos de lectura y escritura a otros usuarios de la siguiente manera:

SQL> grant read,write on directory <nombre_directorio> to <nombre_usuario>;





4



Crearemos una tabla con un campo BLOB:







Oracle recomienda que inicialicemos un campo BLOB con un LOB Locator vació y no dejarlo como NULL.

La función EMPTY_BLOB nos ayuda en este propósito, por ejemplo:

ó







En la creación de la tabla que contiene campos BLOB podemos indicar que el LOB Value (contenido) sea

almacenado en otro tablespace que es lo recomendable, ejemplo:



Aquí definimos que el contenido multimedia será almacenado en el tablespace EXAMPLE, por lo cual se

creará un segmento de tipo LOB en dicho tablespace. Podemos validar está información en la vista

DBA_SEGMENTS, ejemplo:







5



Para almacenar el contenido multimedia en un campo BLOB, debemos ejecutar un script como el que se

adjunta.

El objetivo es obtener el valor de LOB Locator y asignarle el valor del contenido multimedia.

Se adjunta una muestra de cómo se almacena el contenido en formato binario.

























6

En el siguiente ejemplo copiaremos la información de un BLOB almacenado en la base de datos en un

archivo en el sistema operativo; para realizar esta labor nos vamos a apoyar del paquete UTL_FILE.







BFILES

Los BFILEs almacenan información multimedia pero el contenido es almacenado físicamente en el

sistema operativo, por dicha razón los BFILES solo se pueden acceder en modo lectura es decir no

podemos hacer modificaciones o cambios al contenido.

El campo BFILE solo almacena el LOB Locator hacia una dirección donde se encuentra físicamente el

contenido multimedia en el Sistema Operativo. Es importante que si nuestros sistemas cuentan con

BFILES considerar en nuestra política de backups incluir los directorios de los archivos que son

referenciados en las columnas BFILE de nuestra base de datos.

Debemos tener presente que existe una máxima cantidad de archivos concurrentes que pueden ser

leídos por sesión, la cual está limitada por el parámetro SESSION_MAX_OPEN_FILES (el valor por default

es 10), por lo cual limita también la cantidad de lecturas concurrentes sobre campos BFILE por sesión.

Crearemos una tabla con un campo BFILE:







7

Insertaremos un contenido multimedia en la tabla creada:





Podemos apreciar que para insertar un contenido multimedia en un campo BFILE se utiliza la función

BFILENAME, el cual crea un LOB Locator que es un puntero hacia el archivo oracle.jpg que se encuentra

en el Objeto Directorio DIR_TMP. El directorio DIR_TMP fue creado en la sección BLOB y hace referencia

al directorio /tmp del Sistema Operativo.

El paquete DBMS_LOB tiene una serie de funciones que nos ayudan a operar sobre los archivos, una de

ellas es por ejemplo la función GETLENGTH que nos devuelve el tamaño del archivo leído.

Ejemplo:



La función FILEGETNAME nos devuelve el nombre del directorio y archivo que apunta un BFILE.













8

La función FILEEXISTS nos indica si el archivo que apunta el BFILE existe en el sistema operativo.

Nota: Muchas de estas funciones mostradas son válidas también para los tipos de datos BLOB.





Temporary LOB

Los temporary LOB son variables temporales que solo viven durante el ciclo de vida de una sesión y

almacenan un tipo de dato LOB. Una de sus características es que no generan redo por lo cual los hace

más rápido respecto a los LOB convencionales y no soporta el uso de la función

EMPTY_CLOB/EMPTY_BLOB. Al crearse automáticamente son creados como vacíos.

Para crear un temporary LOB usamos los procedures CREATETEMPORARY y FREETEMPORARY (limpia su

información de la memoria) del paquete DBMS_LOB.





SecureFile LOB



SecureFile LOB es nuevo a partir de Oracle Database 11g, el cual ha sido una reingeniería sobre los tipos

de datos LOB. Su uso da mejor performance, reducción de espacio y seguridad sobre los LOB

convencionales

A partir de Oracle Database 11g el uso de LOB sin SecureFile se le conoce como BasicFile LOB.



9

SecureFile LOB solo pude ser creado cuando el segmento de tipo SecureFile LOB se creará en un

tablespace de tipo ASSM y el parámetro DB_SECUREFILE no tiene el valor de NEVER e IGNORE.

Crearemos una tabla con un campo BLOB en formato SecureFile:





SecureFile LOB tiene ciertas propiedades en ventaja de un LOB almacenado como BasicFile, los cuales

son: Deduplicación, Compresión y Encriptación.

Deduplicación:

Cada valor en formato SecureFile almacena un hash index y si usamos la opción deduplicación validará

que si un valor hash ya se encuentra registrado el valor no es insertado y se creará un puntero al valor

ya ingresado previamente de esta manera mejora el uso del espacio en disco.

Para habilitarlo utilizamos la propiedad DEDUPLICATE, ejemplo:





Compresión:

Sus opciones son:

• COMPRESS HIGH, provee la mejor compresión pero incurre en consumo de CPU.

• COMPRESS MEDIUM, es el valor por default.

• NOCOMPRESS, deshabilita la compresión.

Ejemplo de su implementación:









10

Encriptación:

La encriptación se realiza a nivel de bloque de sistema operativo y se pueden usar los siguientes

algoritmos (3DES168,AES128,AES192(default),AES256).

Ejemplo de su implementación:

Crearemos el wallet, el cual es la llave secreta que permite encriptar y desencriptar un va