Create and Manage LOB Segments

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> SecureFiles and Large Objects Developer’s Guide -> 2 Working with LOBs

2. Hagamos un repaso rápido de los diferentes tipos LOB que tenemos en la BD. Un LOB puede ser interno o externo. Además, un LOB interno puede ser persistente o temporal.

· Internal Persistent LOB: Es una instancia LOB que existe dentro de una fila en una tabla
· Internal Temporary LOB: Una instancia LOB que existe sólo en el contexto de nuestra aplicación
· External LOB: Objetos LOB que se almacenan fuera de la BD a los que se accede a través de un BFILE

También debemos introducir dos conceptos que son el localizador (LOCATOR) y el valor (VALUE) de un LOB. El localizador es una referencia de la ubicación física del LOB. Mientras que su valor es el conjunto de datos que tiene dicho LOB. Esto es útil para cuando utilizamos los procedimientos del paquete DBMS_LOB.

Los tipos de datos que podemos manejar a nivel de tabla son estos:

· BLOB: LOB almacenado en formato Binario
· CLOB: LOB que contiene caracteres dentro del juego de caracteres de la BD
· NCLOB: LOB que almacena caracteres del juego juego nacional de caracteres de la BD
· BFILE: Fichero externo binario de sólo lectura en nuestra aplicación

Los estados que puede tener una columna de tipo LOB (BLOB, CLOB o NCLOB) son los siguientes

· NULL: Existe un registro en la BD pero el campo LOB no tiene localizador
· EMPTY: Existe un localizador en dicho registro pero no tiene valor (datos)
· POPULATED (Poblado): Existe un localizador y un valor en dicho registro

Para poder trabajar con campos LOB existen varios APIs que nos permiten realizar operaciones con ellos. Nosotros vamos a usar el paquete DBMS_LOB en PL/SQL pero también existe un API en OCI y en JAVA para poder manipularlos.

3. Empezamos con un ejercicio sencillo mostrando por pantalla el contenido de un CLOB. Podemos usar el método PUT_LINE del paquete DBMS_OUTPUT para mostrar valores de hasta 32767 bytes. Esta limitación en el pasado era de 255 lineas, pero ahora suele ser suficiente para CLOB «pequeños».

-- Este código carga el contenido de un campo CLOB (ad_finaltext) en la variable "final_ad"
DECLARE
    final_ad VARCHAR(32767);
BEGIN
    SELECT ad_finaltext INTO final_ad FROM print_media
        WHERE product_id = 2056 and ad_id = 12001 ;
    DBMS_OUTPUT.PUT_LINE(final_ad);
END;
/

4. Dentro del paquete DBMS_LOB tenemos métodos para leer y escribir campos LOB. Aprovechando que hemos creado una tabla en el ejercicio anterior, vamos a copiar un campo LOB de la tabla PM.PRINT_MEDIA a PM_PRINT_MEDIA_TEST.

-- Insertamos un registro en la tabla PRINT_MEDIA_TEST con campos LOB de tipo EMPTY
-- Con EMPTY_CLOB y EMPTY_BLOB inicializamos los campos LOB (generamos un localizar) aunque estén vacíos
INSERT INTO PRINT_MEDIA_TEST VALUES (2056, 12001, EMPTY_BLOB(), EMPTY_CLOB());
COMMIT;

-- Copiamos el campo AD_SOURCETEXT entre las tablas PRINT_MEDIA y PRINT_MEDIA_TEST
-- Una técnica habitual para modificar campos LOB es utilizar "FOR UPDATE" sobre el registro que usaremos
DECLARE
  ad_sourcetext_src CLOB;
  ad_sourcetext_dest CLOB;
BEGIN
  SELECT AD_SOURCETEXT INTO ad_sourcetext_src FROM print_media WHERE product_id = 2056 and ad_id = 12001;
  SELECT AD_SOURCETEXT INTO ad_sourcetext_dest
    FROM print_media_test WHERE product_id = 2056 and ad_id = 12001
    FOR UPDATE;
  DBMS_LOB.COPY(
    ad_sourcetext_dest,
    ad_sourcetext_src,
    length(ad_sourcetext_src),
    1,
    1);
  COMMIT;
END;
/

-- Consultamos si hemos escrito correctamente el campo CLOB
SELECT AD_SOURCETEXT FROM PRINT_MEDIA_TEST WHERE PRODUCT_ID=2056 AND AD_ID=12001;

5. Vamos a realizar una prueba escribiendo en la columna AD_COMPOSITE de nuestra tabla de prueba.

-- Insertamos
DECLARE
  ad_composite_dest BLOB;
  contents VARCHAR2(20);
BEGIN
  SELECT AD_COMPOSITE INTO ad_composite_dest
    FROM print_media_test WHERE product_id = 2056 and ad_id = 12001
    FOR UPDATE;
  contents := 'Test!';
  DBMS_LOB.WRITE(
    ad_composite_dest,
    length(contents),
    1,
    utl_raw.cast_to_raw(contents));
  COMMIT;
END;
/
-- Consultamos el contenido
SELECT UTL_RAW.CAST_TO_VARCHAR2(AD_COMPOSITE) FROM PRINT_MEDIA_TEST
  WHERE product_id = 2056 and ad_id = 12001;

6. Limpiamos el entorno.

-- Borramos las tablas de los ejercicios con LOBS
DROP TABLE PRINT_MEDIA_TEST PURGE;
DROP TABLE PRINT_MEDIA_TEST2 PURGE;
DROP TABLE PRINT_MEDIA_TEST3 PURGE;

7. Para vuestra referencia existen muchos procedimientos ya programados en PL/SQL dentro del directorio $ORACLE_HOME/rdbms/demo/lobs/plsql. Hemos visto algún ejemplo similar a los que podéis encontrar ahí (Por ej. lcopy.sql). En la Documentación Oficial también podéis encontrar más información.