2asi sgbd u08 plsql practica14 lobs

2ASI. SGBD. PL/SQL Práctica 14. LOBS Objetos Grandes. Página 1

MANIPULAR LOBs (Large Objects)

1. COMPRENDER LOS LOBS.

Actualmente, las aplicaciones web (y el resto) necesitan además de almacenar textos, números y fechas, manejar información multimedia para hacer amigable al usuario su uso. Por este motivo, ahora los SGBD pueden almacenar imágenes, sonidos, vídeo, etc. Antes de Oracle 8, podías almacenar grandes bloques de texto usando el tipo LONG, y grandes bloques de datos binarios usando LONG RAW. Actualmente, hay 4 tipos de LOB en Oracle:

• CLOB: LOB de caracteres, para almacenar cadenas de caracteres muy largas, como descripciones, páginas web completas, etc.

• NCLOB Un CLOB nacional para caracteres no ingleses.• BLOB Un LOB binario (archivos, etc.).• BFILE para ficheros binarios. Punteros a ficheros que están almacenados en el sistema de ficheros del

sistema operativo que contiene el SGBD (no están almacenados dentro de la BD). Las columnas creadas usando CLOB, NCLOB y BLOB tienen tres ventajas sobre los viejos tipos LONG y LONG RAW:

1. Las columnas LOB pueden almacenar hasta 128 Terabytes de datos. Una columna LONG y LONG RAW, puede almacenar 2 Gigabytes y RAW 4 Kilobytes.

2. En segundo lugar, una tabla puede tener varias columnas LOB, pero solamente una LONG o LONG RAW. 3. En tercer lugar, los datos LOB pueden accederse en orden aleatorio; LONG y LONG RAW solamente en orden

secuencial. Por este motivo, vamos a utilizar únicamente los tipos BLOB y BFILE.Un LOB tiene dos partes:

• LOB locator: Un puntero que especifica la localización del contenido LOB.• LOB content: La información almacenada del LOB.

Según el tamaño del LOB content, los datos se almacenarán en la tabla donde se define o fuera de ella. Si es menor de 4 kilobytes, se deja en la tabla, si es mayor, se almacena fuera. Si se usa el tipo BFILE, solamente el locator se almacena en la BD, el LOB content será externo a la BD (estará en el sistema de ficheros de algún sistema operativo).Crearemos una tabla con todos los tipos posibles de LOBs: CREATE TABLE saltos_agua( nombre VARCHAR2(80) CONSTRAINT cascadas_pk PRIMARY KEY, foto BLOB, indicaciones CLOB, descripcion NCLOB, web BFILE );

COMPRENDER LOS LOCALIZADORESUn localizador de LOB es un puntero a sus datos. Veamos lo que ocurre cuando seleccionamos una columna BLOB en una variable BLOB: DECLARE v_foto BLOB; BEGIN SELECT foto INTO v_foto FROM cascadas WHERE nombre='Dryer Hose';¿Qué hay exactamente en la variable v_foto tras ejecutar la sentencia SELECT? ¿la foto? No. Solo hay un puntero a los datos de la imagen. La figura muestra un esquema.


Esto es diferente a la forma de trabajar de otras bases de datos. Las columnas LOB almacenan localizadores LOB, que apuntan a los datos almacenados. Para trabajar con datos LOB, primero recuperas el LOB locator y luego usas un paquete predefinido llamado DBMS_LOB para recuperar/modificar los datos del LOB. Por ejemplo, para recuperar la los datos de la foto anterior:

1. Usas las sentencia SELECT para recuperar el LOB locator.2. Abres el LOB usando una llamada a DBMS_LOB.OPEN.3. Llamas a DBMS_LOB.GETCHUNKSIZE para obtener el tamaño óptimo de bloque para leer (y escribir) los

valores LOB.4. Haces una llamada a DBMS_LOB.GETLENGTH para obtener el número de bytes de los datos LOB.5. Haces varias llamadas a DBMS_LOB.READ para ir recuperando los datos del LOB.6. Cierras el LOB.

No todos los pasos son necesarios. Aunque el uso de localizadores pueda dar la impresión de mecanismo engorroso, en realidad es una buena aproximación porque evita manipular una gran cantidad de datos cada vez que usas columnas LOB en consultas, cursores, etc. Puedes manipular localizadores con el mismo coste que manipulas un número o una cadena de texto, y luego acceder a los datos que realmente necesites.

LOB NULOS CONTRA LOB VACÍOS.Un LOB vacío (empty LOB) es aquel en el que su localizador no apunta a ningún dato. Un LOB null (columna o variable PL/SQL) es el que no tiene ningún localizador. Ejemplo: DECLARE v_direcciones CLOB; BEGIN IF v_direcciones IS NULL THEN DBMS_OUTPUT.PUT_LINE('Direcciones es NULL'); ELSE DBMS_OUTPUT.PUT_LINE('Direcciones no es NULL'); END IF; END; / Direcciones es NULL

El siguiente código usa una llamada a EMPTY_CLOB en una sentencia INSERT para crear un LOB locator. A continuación, una sentencia SELECT recupera el LOB locator de la base de datos y lo coloca en la variable v_direcciones:

DECLARE v_direcciones CLOB; BEGIN --Borra cualquier fila 'Munising Falls' DELETE FROM cascadas WHERE nombre='Munising Falls'; --Inserta una nueva fila usando EMPTY_CLOB() para crear el LOB locator INSERT INTO cascadas(nombre,indicaciones) VALUES ('Munising Falls', EMPTY_CLOB() ); --Recupera el LOB locator creado antes SELECT direcciones INTO v_direcciones FROM cascadas WHERE nombre='Munising Falls'; IF v_direcciones IS NULL THEN DBMS_OUTPUT.PUT_LINE('Indicaciones es NULL'); ELSE DBMS_OUTPUT.PUT_LINE('indicaciones no es NULL'); END IF; DBMS_OUTPUT.PUT_LINE( 'Peso = ' || DBMS_LOB.GETLENGTH(v_direcciones) ); END;

La salida será:Direcciones no es NULLPeso = 0

EMPTY_CLOB es una función que devuelve un CLOB locator. Esto es importante porque es la forma de comprobar la presencia o ausencia de datos en un LOB (no basta con saber si es NULL, no como en otros tipos de datos):


-- COMPROBACIONES EN DATOS BÁSICOS -- COMPROBACIONES EN LOB

IF dato IS NULL THEN --No hay datoELSE --Hay datoEND IF;

IF dato IS NULLTHEN --No hay locator ni datosELSEIF DBMS_LOB.GETLENGTH(dato) = 0 THEN --hay locator pero no datosELSE --Ahora hay datosEND IF;

3. USAR LOBs EN PL/SQL.

Oracle aporta varios paquetes para ayudar a realizar ciertas tareas. Unos, nos ayudan a trabajar con ficheros, otros a trabajar con páginas web, otros nos ayudan a manejar los atributos LOB. El paquete DBMS_LOB, tiene un enfoque relacional, es decir usa PL/SQL para mover los campos LOB como si de otro dato se tratase. Veamos algunos de sus procedimientos y funciones.

Método Descripción

APPEND(lob_origen, lob_destino) Añade contenido de lob_origen al final de lob_destino

CLOSE(lob) Cierra un LOB abierto antes

COMPARE(lob1, lob2, amount, offset1, offset2) Compara dos LOBs completos o partes de ellos

COPY(dest_lob, src_lob, amount, dest_offset, src_offset)

Copia todo o parte de src_LOB a dest_LOB

CREATETEMPORARY(lob, cache, duration) Crea BLOB o CLOB temporal en temporary tablespace

ERASE(lob, amount, offset) Borra todo o parte de un LOB

FILECLOSE(bfile) Cierra un BFILE

FILECLOSEALL() Cierra todos los BFILEs abiertos antes

FILEEXISTS(bfile) Comprueba si exixte un fichero

FILEGETNAME(bfile, dir_alias, filename) Obtiene el alias deldirectorio y el nombre del fichero

FILEISOPEN(bfile) Comprueba si el fichero está abierto

FILEOPEN(bfile, open_mode) Abre un fichero

FREETEMPORARY(lob) Libera el BLOB o CLOB temporal

GETCHUNKSIZE(lob) Obtiene la cantidad de espacio usado en LOB chunk para el valor del LOB

GET_STORAGE_LIMIT() Obtener el límite de almacenamiento de un LOB

GETLENGTH(lob) Obtiene la longitud de un LOB

INSTR(lob, pattern, offset, n) Posición de la n-ésima ocurrencia de pattern en LOB

ISOPEN(lob) Comprueba si LOB fue abierto usando input locator

ISTEMPORARY(lob) Comprueba si el locator apunta a un LOB temporal

LOADFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset)

Carga datos de BFILE a un LOB


Método Descripción

LOADBLOBFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset)

Carga un BFILE en un BLOB

LOADCLOBFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset, src_csid, lang_context, warning)

Carga datos BFILE en CLOB

OPEN(lob, open_mode) Abre un LOB (interno, externo o temporal)

READ(lob, amount, offset, buffer) Lee datos en buffer desde LOB

SUBSTR(lob, amount, offset) Lee parte de LOB

TRIM(lob, newlen) Recorta el LOB

WRITE(lob, amount, offset, buffer) Escribe datos del buffer al LOB

WRITEAPPEND(lob, amount, buffer) Añade datos del buffer al final del LOB

EJERCICIO 0. PREPARAR EL ENTORNO DE LA PRÁCTICA.Vamos a crear un script llamado “alumno_iniciar.sql” que prepare el entorno para crear la práctica.

connect SYS AS SYSDBA;set serveroutput onset echo on-- Necesitas privilegios SYSDBA para borrar un usuario.DROP USER multimedia CASCADE;DROP DIRECTORY carpeta;DROP TABLESPACE multimedia INCLUDING CONTENTS;-- Crear un tablespace: un fichero separado.CREATE TABLESPACE multimedia DATAFILE 'multimedia.dbf' size 100M MINIMUM EXTENT 64K DEFAULT STORAGE (INITIAL 64K NEXT 128K) LOGGING;-- Crear el usuario multimediaCREATE USER multimedia IDENTIFIED BY multimediaDEFAULT TABLESPACE multimediaTEMPORARY TABLESPACE temp;GRANT CONNECT, RESOURCE, CREATE LIBRARY TO multimedia;GRANT CREATE ANY DIRECTORY TO multimedia;GRANT EXECUTE ON UTL_FILE TO multimedia;-- Conectarnos como multimediaCONNECT multimedia/multimedia;-- Crear el directorio de carga de datos multimedia. -- Cambia la ruta a la que tu quieras (el directorio debe existir en el SO)CREATE OR REPLACE DIRECTORY carpeta AS 'd:\carpeta';GRANT READ ON DIRECTORY carpeta TO PUBLIC;-- Creamos una secuencia para rellenar claves primarias de la tabla archivosDROP SEQUENCE archivo;CREATE SEQUENCE archivo START WITH 1 INCREMENT BY 1 NOCACHE;-- Creamos la tabla donde almacenaremos los archivosCREATE TABLE tablaArchivos(ID INTEGER CONSTRAINT pk_tablaArchivos PRIMARY KEY,Nombre VARCHAR2(100),ALTA DATE DEFAULT SYSDATE,BIN BLOB);

EJERCICIO 1: INSERTAR EN LA BD UN FICHERO DESDE UN DIRECTORIO DEL SERVIDOR.


Te conectas como usuario multimedia. Los datos binarios nos van a permitir guardar en la base de datos archivos, imágenes, sonidos, etc. Lo que vamos a hacer es cargar un fichero existente en el servidor en un campo BLOB de una tabla. El siguiente bloque de PL/SQL nos va a permitir cargar una imagen, llamada "imagen.gif" en la tabla. Es importante tener claro que el archivo "imagen.gif" debe existir físicamente y también el directorio al que se asocia el objeto CARPETA (d:\carpeta) que hemos creado anteriormente.

Nota: Por experiencia, Oracle guarda los nombres de los objetos en mayúsculas, así que si utilizas el objeto 'carpeta' en vez de 'CARPETA' te dará un mensaje de error como: ORA-22285: no existe el directorio o el archivo para la operación FILEOPEN

DECLARE L_bfile BFILE; -- Usaremos el BFILE para cargar el BLOB L_blob BLOB;BEGIN INSERT INTO tablaArchivos(ID, NOMBRE, BIN, ALTA) VALUES(1,'calamardo.gif', EMPTY_BLOB(), SYSDATE) RETURN BIN INTO l_blob; l_bfile := BFILENAME('CARPETA', 'calamardo.gif'); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.File_Readonly); DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile) ); DBMS_LOB.fileclose(l_bfile); COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Error al importar fichero imagen.gif. ' || SQLERRM ); RAISE;END;

Hay varios aspectos a comentar de este código:• El uso de RETURN en la sentencia INSERT nos permite capturar el LOB locator sin usar otra sentencia SQL.• La función EMPTY_BLOB.Nos permite crear un LOB locator vacío.• La función BFILENAME. Devuelve un objeto BFILE que representa la ruta del fichero "imagen.gif" que

queremos almacenar en la tabla.• El uso del paquete predefinido de ORACLE DBMS_LOB. Utilizamos las siguientes funciones:

o fileopen: Abre el archivo definido por BFILE (l_bfile) en el modo indicado (en nuestro caso solo lectura Dbms_Lob.File_Readonly)

o loadfromfile: Lee un determinado número de bytes (en nuestro caso todos) del fichero definido por BFILE(l_bfile) en un objeto de tipo BLOB (l_blob).

o getlength:Devuelve el tamaño del archivo en bytes.o fileclose:Cierra el archivo

Llama la atención de este código que a pesar de haber insertado el campo BIN vacío con la función EMPTY_BLOB, finalmente queda cargado sin ejecutar ninguna sentencia UPDATE. Esto ocurre porque estamos utilizando RETURN en la sentencia INSERT y guardando una referencia al campo BIN que posteriormente asignamos al leer el archivo con DBMS_LOB.LoadFromFile. El resultado de todo esto, es que la imagen se almacena en la base de datos.

Tu Turno: modifica el bloque PL/SQL anterior y conviértelo en una función almacenada denominada InsertaFichero que acepte de parámetros de entrada:

• El nombre del fichero.• El nombre del objeto directorio donde se encuentra.

La función debe usar la secuencia para insertar en la tabla tablaArchivos y debe devolver la clave de la fila que tiene el blob. Además, almacenará el nombre del fichero en la tabla y tratará las excepciones posibles.

EJERCICIO 2: EXTRAER OBJETOS BLOB DEDE LA BD HACIA UN FICHERO.

Ojo!!


Usamos la tabla archivos, que contiene una columna BLOB del ejercicio anterior. Tenemos una carpeta creada en el sistema de ficheros, en la que tenemos permisos para acceder. Con un usuario DBA, damos permisos de escritura sobre el directorio.

GRANT WRITE ON DIRECTORY carpeta TO PUBLIC;

Ahora, crearemos el procedimiento ExtraerFichero, al que pasamos la clave del fichero y la carpeta donde queremos dejarlo.

CREATE OR REPLACE PROCEDURE ExtraerFichero(pFichero IN INTEGER, pDirectorio IN VARCHAR2)IS vblob BLOB; PosInicio NUMBER := 1; bytelen NUMBER := 32000; vlongitud NUMBER; buffer RAW(32000); x NUMBER; vnombre VARCHAR2(100); lv_str_len NUMBER; l_output UTL_FILE.FILE_TYPE;BEGIN -- Averiguar el nombre del fichero SELECT nombre INTO vnombre FROM tablaArchivos WHERE ID=pFichero; -- Crear el fichero de salida (una escritura escribe hasta 32000Bytes) l_output := UTL_FILE.FOPEN( UPPER(pdirectorio), 'Vengo del SGBD_'||vnombre, 'WB', 32000); -- Obtener la longitud del BLOB SELECT dbms_lob.getlength(bin) INTO vlongitud FROM tablaarchivos WHERE ID=pFichero; -- Guardar la longitud del blob x := vlongitud; -- seleccionar el lob locator del blob en una variable blob SELECT bin INTO vblob FROM tablaarchivos WHERE ID = pFichero; IF vlongitud <= 32000 THEN -- Si es más pequeño solo una única escritura utl_file.put_raw(l_output, vblob); utl_file.fflush(l_output); ELSE –- Hay que escribirlo en piezas (chunks) posInicio:= 1; WHILE posInicio < vlongitud LOOP dbms_lob.read(vblob, bytelen, posInicio, buffer); -- En bytelen devuelve bytes leidos utl_file.put_raw(l_output, buffer); utl_file.fflush(l_output); posInicio:= posInicio + bytelen; -- Modificar posición de inicio

x := x – bytelen; -- Comprobar que posición final < 32000 bytes IF x < 32000 THEN bytelen := x; END IF; END LOOP; END IF; utl_file.fclose(l_output); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('ERROR al extraer fichero ' || pFichero || ' en directorio ' || pDirectorio );END ExtraerFichero;

PREGUNTA: Prueba a insertar la imagen 'calamardo.gif' (menor de 32Kb). Luego la extraes ¿Funciona?

.

Prueba a insertar la imagen 'bobesponja.jpg' (mayor de 32Kb). Luego la extraes ¿Funciona? .


3. USANDO LOBS CON INTERMEDIA (opcional).

Intermedia es una especie de librería que permite trabajar con contenido multimedia (imagen, sonido y vídeo). Tiene dos interfaces, una relacional (para mover datos como un todo) y otra orientada a objetos (modelo objeto-relacional) que además da la posibilidad al SGBD de modificar los objetos (cambiarles el formato, editarlos, ampliarlos, cambiar el número de colores, etc.). Tienes ejemplos en $ORACLE_HOME/D\ord para trabajar con diferentes tipos de material (imagen, sonido, vídeo, etc.).

Nota: Oracle Intermedia necesita que el SGBD soporte Java. Una de las limitaciones de Oracle XE es que no soporta Java. Por tanto, para trabajar con Intermedia, debemos usar otra versión. Si te interesa el tema, puedes instalarte la versión Oracle 9i, 10g, 11g en edición personal o enterprise.

EJERCICIO 3.1 (opcional): Crear funciones usando Intermedia, para importar una imagen desde fichero y otra para importar imagen desde una web.

-- Importa imágenes desde el directorio carpeta en el sistema de archivos local (srcType=file).CREATE OR REPLACE PROCEDURE ImportaImagen( FicheroDesde in VARCHAR2 )AS obj ORDSYS.ORDIMAGE; ctx RAW(64):= NULL; Clave imagenes.ID%Type;BEGIN SELECT imagen.nextval INTO clave FROM DUAL; INSERT INTO imagenes VALUES(clave, ORDSYS.ORDImage.init()); SELECT imagen INTO obj FROM imagenes WHERE id = clave FOR UPDATE; obj.setSource( 'file', 'MULTIMEDIA', FicheroDesde ); obj.import(ctx); UPDATE imagenes SET imagen = obj WHERE id = clave; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20000, 'Error al importar imagen desde fichero ' || Ficherodesde);END;

-- Importa imagen desde una página web CREATE OR REPLACE PROCEDURE ImportaImagenWeb( URLDesde IN VARCHAR2 DEFAULT 'localhost', FicheroDesde IN VARCHAR2 )AS obj ORDSYS.ORDIMAGE; ctx RAW(64) := NULL; Clave imagenes.ID%Type;BEGIN -- Importa el fichero Ficherodesde que es alcanzable desde -- la URL URLDesde (srcType=HTTP) SELECT imagen.nextval INTO clave FROM DUAL; INSERT INTO imagenes VALUES( clave, ORDSYS.ORDImage.init( 'http', URLDesde, Ficherodesde) ); SELECT imagen INTO obj FROM imagenes WHERE Id = clave FOR UPDATE; obj.import(ctx); UPDATE imagenes SET imagen = obj WHERE id = clave; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20000, 'Error al importar imagen desde web ' || URLDesde || '/' || Ficherodesde);END;

EJERCICIO 3.2 (opcional). Hacer una aplicación web que permita implementar un álbum de fotos con Intermedia y PLSQL web Toolkit. Es una demostración sacada de Oracle interMedia PL/SQL Web Toolkit. Vamos a realizar una aplicación PL/SQL que muestre como usar PL/SQL Tookit para cargar (upload), recuperar y procesar datos multimedia


almacenados usando el tipo de dato interMedia ORDImage. Los usuarios que accedan a esta aplicación, verán los contenidos de un álbum de fotos, incluyendo versiones en miniatura (thumb-nail) de cada foto que genera la propia aplicación y que permiten enlazar con las fotografías a tamaño real.

PASO 1. Creamos la tabla que contendrá el álbum de fotos y una secuencia para su clave primaria. Crearemos además la tabla de upload de documentos que necesita el web Toolkit. Así que te conectas como usuario multimedia y construyes un script SQL llamado “alumno_creaAlbum.sql”, que contendrá:

-- Crea la tabla FOTOSCREATE TABLE fotos(id NUMBER CONSTRAINT pk_fotos PRIMARY KEY,descripcion VARCHAR2(40) CONSTRAINT pk_nnDesc_fotos NOT NULL,localizador VARCHAR2(40), imagen ORDSYS.ORDIMAGE,thumb ORDSYS.ORDIMAGE );

-- Crea la secuencia FOTOS_SEQUENCECREATE SEQUENCE secuencia_fotos;

-- Crea la tabla document upload que permitirá almacenar las imágenes subidas al-- servidor desde un cliente. El formato de esta tabla lo impone el Web Toolkit.CREATE TABLE fotos_upload(name VARCHAR2(256) UNIQUE NOT NULL,mime_type VARCHAR2(128),doc_size NUMBER,dad_charset VARCHAR2(128),last_updated DATE,content_type VARCHAR2(128),blob_content BLOB );

PASO 2. Creamos la cabecera del paquete que gestionará el álbum de fotos. Escogemos el esquema multimedia para hacerlo.

----------------------------------------------------------------------------- Package name: album-- Descripción:-- El paquete ALBUM es un PL/SQL package que implementa un sencillo -- álbum de fotos usando interMedia ORDImage y PL/SQL Web Toolkit.----------------------------------------------------------------------------- Especificación o cabecera del PackageCREATE OR REPLACE PACKAGE albumAS PROCEDURE ver_album; PROCEDURE ver_entrada( entrada_id IN VARCHAR2 ); PROCEDURE entrega_foto( entrada_id IN VARCHAR2, tipo IN VARCHAR2 ); PROCEDURE ver_formulario_upload; PROCEDURE inserta_nueva_foto( nueva_descripcion IN VARCHAR2, nuevo_localizador IN VARCHAR, nueva_foto IN VARCHAR2 ); FUNCTION dame_formato_preferido( formato IN VARCHAR2 ) RETURN VARCHAR2; PROCEDURE imprime_formulario_upload; PROCEDURE imprime_pagina_cabecera( tag_cabecera IN VARCHAR2 DEFAULT NULL ); PROCEDURE imprime_pagina_pie( muestra_url_devuelta IN BOOLEAN ); PROCEDURE imprime_encabezado( mensaje IN VARCHAR2 ); PROCEDURE imprime_enlace( enlace IN VARCHAR2, mensaje IN VARCHAR2 );END album;/SHOW ERRORS;

PASO 3. Creamos el cuerpo del paquete que gestionará el álbum de fotos. Escogemos el esquema multimedia para hacerlo.

-- Cuerpo del paquete


CREATE OR REPLACE PACKAGE BODY album AS

----------------------------------------------------------------------------- Procedure: ver_album-- Descripción: Muestra el contenido del álbum con imágenes thumb-nail.---------------------------------------------------------------------------PROCEDURE ver_albumIS CURSOR cursor_album IS SELECT id,descripcion,localizador,thumb FROM fotos ORDER BY descripcion; contador_filas INTEGER := 0; localizador fotos.localizador%TYPE; ampersand VARCHAR2(1) := CHR( 38 );BEGIN -- Cabecera de página común imprime_pagina_cabecera; -- Muestra las imágenes thumb-nail en una tabla htp.print( '<p><table border="1" cellpadding="3" cellspacing="0"' || 'width="100%" summary="Tabla de Imágenes en miniatura">' ); htp.print( '<tr bgcolor="#336699">' ); htp.print( '<th id="descripcion"><font color="#ffffff">Descripción</font></th>' ); htp.print( '<th id="localizador"><font color="#ffffff">Localizador</font></th>' ); htp.print( '<th id="imagen"><font color="#ffffff">Foto</font></th></tr>' ); -- Para cada entrada... FOR entrada IN cursor_album LOOP -- Incrementa el contador de filas contador_filas := contador_filas + 1; htp.print( '<tr>' ); htp.print( '<td headers="descripcion">' || entrada.descripcion || '</td>' ); IF entrada.localizador IS NOT NULL THEN htp.print( '<td headers="localizador">' || entrada.localizador || '</td>' ); ELSE htp.print( '<td headers="localizador">' || ampersand || 'nbsp;</td>' ); END IF; -- Muestra la imagen thumb-nail como enlace a su tamaño real. htp.print( '<td headers="imagen"><a href="ALBUM.VER_ENTRADA' || '?entrada_id=' || entrada.id || '">' ); IF entrada.thumb.contentLength > 0 THEN htp.print( '<img src="ALBUM.ENTREGA_FOTO?tipo=thumb' || ampersand || 'entrada_id=' || entrada.id || '"' || ' height=' || entrada.thumb.height || ' width=' || entrada.thumb.width ||

' alt="' || entrada.descripcion || '"' || ' border=1></a></td>' );

ELSE htp.print( '[Ver imagen]</a></td>' ); END IF; htp.print( '</tr>' ); END LOOP; -- Muestra un mensaje si el album está vacío htp.print('<tr><td scope="col" colspan="3" align="center">' || '<font color="#336699">'); IF contador_filas = 0 THEN htp.print( '<b><i>El álbum de fotos está vacío</i></b>' ); ELSE htp.print( '<b><i>Selecciona la miniatura (thumb-nail) para ver la foto' || ' a tamaño completo</i></b>' ); END IF; htp.print( '</font></td></tr>' ); htp.print( '</table></p>' ); -- Muestra un tag anchor que permita mostrar el formulario de nueva foto imprime_enlace( 'ALBUM.VER_FORMULARIO_UPLOAD', 'Subir una nueva foto' );


-- Pie común imprime_pagina_pie( FALSE );END ver_album;

----------------------------------------------------------------------------- Procedure: ver_entrada-- Descripción: Ver la versión a tamaño real de una imagen.---------------------------------------------------------------------------PROCEDURE ver_entrada( entrada_id IN VARCHAR2 )IS CURSOR cursor_fotos IS SELECT descripcion, localizador, imagen FROM fotos WHERE id = entrada_id; ampersand VARCHAR2( 1 ) := CHR( 38 );BEGIN imprime_pagina_cabecera; FOR foto IN cursor_fotos LOOP htp.print( '<p><table summary="Ver imagen de entrada">' ); htp.print( '<tr><td scope="col"><b>Descripción:</b></td><td scope="col">' || foto.descripcion || '</td></tr>' ); htp.print( '<tr><td scope="col"><b>Localizador:</b></td><td scope="col">' || foto.localizador || '</td></tr>' ); -- Construir una URL para acceder a la imagen en tamaño completo. -- Si el formato de imagen no se reconoce, no genera los atributos HTML -- height y width para la etiqueta img. Indicarlos siempre es mejor -- para el navegador, que puede dibujar la página sin esperar a que se -- descargue toda la imagen. htp.prn( '<tr><td scope= "col" valign="top"><b>Foto:</b></td><td scope="col">' || '<img src="ALBUM.ENTREGA_FOTO?tipo=imagen' || ampersand || 'entrada_id=' || entrada_id || '"' ); htp.prn( ' alt="' || foto.descripcion || '"' ); IF foto.imagen.height > 0 AND foto.imagen.width > 0 THEN htp.prn( ' height="' || foto.imagen.height || '"' || ' width=' || foto.imagen.width || '"' ); END IF; htp.print( ' border="1"></td></tr>' ); htp.print( '</table></p>' ); EXIT; END LOOP; imprime_pagina_pie( TRUE );END ver_entrada;

----------------------------------------------------------------------------- Procedure: entrega_foto-- Descripción: Entregar una imagen thumb-nail o a tamaño completo al browser.---------------------------------------------------------------------------PROCEDURE entrega_foto( entrada_id IN VARCHAR2, tipo IN VARCHAR2 )IS imagen_local ORDSYS.ORDIMAGE;BEGIN IF tipo = 'thumb' THEN SELECT thumb INTO imagen_local FROM fotos WHERE id = entrada_id; ELSE SELECT imagen INTO imagen_local FROM fotos WHERE id = entrada_id; END IF; -- Fijar el tipo MIME y devolver la imagen al browser owa_util.mime_header( imagen_local.mimeType, TRUE ); wpg_docload.download_file( imagen_local.source.localData );END entrega_foto;

----------------------------------------------------------------------------- Procedure: ver_formulario_upload-- Descripción: Muestra el formulario de upload.---------------------------------------------------------------------------PROCEDURE ver_formulario_upload


ISBEGIN imprime_pagina_cabecera; imprime_formulario_upload; imprime_pagina_pie( TRUE );END ver_formulario_upload;

----------------------------------------------------------------------------- Procedure: inserta_nueva_foto-- Descripción: permite subir a la BD un fichero desde el cliente---------------------------------------------------------------------------PROCEDURE inserta_nueva_foto( nueva_descripcion IN VARCHAR2, nuevo_localizador IN VARCHAR, nueva_foto IN VARCHAR2 )IS new_desc VARCHAR2( 128 ) := nueva_descripcion; upload_mime_type VARCHAR2( 128 ); upload_blob BLOB; new_id NUMBER; new_image ORDSYS.ORDIMAGE; new_thumb ORDSYS.ORDIMAGE; pos INTEGER; image_blob BLOB; upload_size INTEGER;BEGIN -- Si no se indica un nombre de fichero, se muestra un error IF nueva_foto IS NULL OR LENGTH( nueva_foto ) = 0 THEN imprime_pagina_cabecera; imprime_encabezado( 'Mensaje de Error' ); htp.print( '<p>Por favor, indica un nombre de fichero.</p>' ); imprime_formulario_upload; imprime_pagina_pie( TRUE ); return; END IF; -- Obtener longitud, tipo MIME y BLOB de la nueva foto desde la tabla upload SELECT doc_size, mime_type, blob_content INTO upload_size, upload_mime_type, upload_blob FROM fotos_upload WHERE name = nueva_foto; -- Comprobar que el fichero es una imagen válida. IF upload_size = 0 THEN imprime_pagina_cabecera; imprime_encabezado( 'Mensaje de Error' ); htp.print( '<p>Por favor, indique un fichero de imagen válido.</p>' ); imprime_formulario_upload; imprime_pagina_pie( TRUE ); return; END IF; -- Si la descripción está en blanco, usaremos el nombre del fichero IF new_desc IS NULL OR LENGTH( new_desc ) = 0 THEN new_desc := nueva_foto; pos := INSTR( new_desc, '/' ); IF pos > 0 THEN new_desc := SUBSTR( new_desc, pos + 1 ); END IF; END IF; -- Insertamos una nueva fila en la tabla, usando la secuencia new_image := ORDSYS.ORDIMAGE.INIT(); new_thumb := ORDSYS.ORDIMAGE.INIT(); INSERT INTO fotos ( id, descripcion, localizador, imagen, thumb ) VALUES(secuencia_fotos.nextval,new_desc, nuevo_localizador, new_image, new_thumb ) RETURN id INTO new_id; SELECT imagen, thumb INTO new_image, new_thumb FROM fotos WHERE id = new_id


FOR UPDATE; -- Cargar la foto desde la tabla documents upload en los objetos imagen DBMS_LOB.COPY( new_image.source.localData, upload_blob, upload_size ); new_image.setLocal(); BEGIN new_image.setProperties(); EXCEPTION WHEN OTHERS THEN new_image.contentLength := upload_size; new_image.mimeType := upload_mime_type; END; -- Algunos formatos son soportados por interMedia pero no pueden ser mostrador -- in-line por un navegador (formato BMP). En este caso convertimos la imagen a un -- GIF o JPEG según el número de colores en la imagen. IF new_image.contentFormat IS NOT NULL AND ( new_image.mimeType = 'image/bmp' OR new_image.mimeType = 'image/x-bmp' ) THEN BEGIN new_image.process('fileFormat=' || dame_Formato_preferido( new_image.contentFormat ) ); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; -- Intentar copiar la imagen a tamaño real y procesarla para crear su thumb-nail. -- Si el formato no se reconoce, no será posible. BEGIN new_image.processCopy( 'maxScale=50,50', new_thumb ); EXCEPTION WHEN OTHERS THEN new_thumb.deleteContent(); new_thumb.contentLength := 0; END; -- Actualizar la imagen a tamaño real y su thumb-nail en nuestra tabla UPDATE fotos SET imagen = new_image, thumb = new_thumb WHERE id = new_id; -- Borrar la fila en la tabla upload de documentos. DELETE FROM fotos_upload WHERE name = nueva_foto; -- Redireccionar al browser para que muestre de nuevo todo el album. imprime_pagina_cabecera('<meta http-equiv="refresh" ' || 'content="2;url=ALBUM.VER_ALBUM">' ); imprime_encabezado( 'Foto satisfactoriamente subida al álbum' ); htp.print( '<p>Por favor, haz clic en el siguiente enlace o ' || 'espera a que el browser refresque la páagina.</p>' ); imprime_pagina_pie( TRUE );END inserta_nueva_foto; --------------------------------------------------------------------------------------- Function: dame_formato_preferido-- Descripción: Devuelve otro formato de fichero si el browser no soporta el actual-------------------------------------------------------------------------------------FUNCTION dame_formato_preferido( formato IN VARCHAR2 ) RETURN VARCHAR2IS num_digits INTEGER; ch CHAR(1);BEGIN -- El formato del contenido de una imagen es de la forma: -- <#bits><format> -- MONOCHROME -- El número de dígitos representa el número de colores num_digits := 0; LOOP ch := SUBSTR( formato, num_digits + 1, 1 ); IF ch >= '0' AND ch <= '9' THEN num_digits := num_digits + 1; ELSE


EXIT; END IF; END LOOP; -- Imagenes con más de 8 bits de colores pueden convertirse a JPEG -- sin pérdida significativa de calidad IF num_digits > 0 THEN IF TO_NUMBER( SUBSTR( formato, 1, num_digits ) ) > 8 THEN RETURN 'JFIF'; END IF; END IF; -- Imagenes con 8 bits o menos de color se convierten a GIF RETURN 'GIFF';END dame_formato_preferido;

----------------------------------------------------------------------------- Procedure: imprime_formulario_upload-- Descripción: Genera el formulario upload---------------------------------------------------------------------------PROCEDURE imprime_formulario_uploadIS nbsp VARCHAR2( 6 ) := CHR( 38 ) || 'nbsp;';BEGIN -- Simplemente genera el formulario imprime_encabezado( 'Subir Nueva Foto' ); htp.print( '<p><form action="ALBUM.INSERTA_NUEVA_FOTO"' ); htp.print( ' method="post" enctype="multipart/form-data">' ); htp.print( '<table border="0" width="100%" cellspacing="0" ' || 'summary="Formulario Upload">' ); htp.print( ' <tr bgcolor="#f7f7e7">' ); htp.print( ' <td scope="col" colspan="2">' || nbsp || '</td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td scope="col" valign="top">' || ' '<label for="descripcion_id"><b>Descripción:</b>' ); htp.print( ' <br><font size="-1">(Opcional)</font></label></td>' ); htp.print( ' <td scope="col" ><input id="descripcion_id" type="text" ' || 'name="nueva_descripcion" length="40">' ); htp.print( ' <br><font size="-1">(ejemplo, Mis vacaciones)</font></td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td scope="col" valign="top"> ' || ' <label for="localizador_id"><b>Localizador:</b>' ); htp.print( ' <br><font size="-1">(Opcional)</font></label></td>' ); htp.print( ' <td scope="col"><input id="localizador_id" type="text" ' || 'name="nuevo_localizador" length="40">' ); htp.print( ' <br><font size="-1">(ejemplo: algo divertido)</font></td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td scope="col" valign="top"><label for="filename_id">' || '<b>Nombre del Fichero:</b></label></td>' ); htp.print( ' <td scope="col" ><input id="filename_id" type="file" ' || ' name="nueva_foto">' ); htp.print( ' <br><font size="-1">(ejemplo: islandia.jpg)</td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td colspan="2">' || nbsp || '</td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td colspan="2"><input type="submit" value="Subir foto" ></td>' ); htp.print( ' </tr><tr bgcolor="#f7f7e7">' ); htp.print( ' <td colspan="2">' || nbsp || '</td></tr>' ); htp.print( '</table>' ); htp.print( '</form></p>' );END imprime_formulario_upload;

----------------------------------------------------------------------------- Procedure: imprime_pagina_cabecera-- Descripción: Saca una cabecera común---------------------------------------------------------------------------


PROCEDURE imprime_pagina_cabecera( tag_cabecera IN VARCHAR2 DEFAULT NULL ) ISBEGIN htp.print( '<html lang="ES">' ); htp.print( '<head>' ); htp.print( '<title>Album de Fotos con InterMedia PL/SQL Web Toolkit</title>' ); IF tag_cabecera IS NOT NULL THEN htp.print( tag_cabecera ); END IF; htp.print( '</head>' ); htp.print( '<body>' ); htp.print( '<table border="0" width="100%">' ); htp.print( '<tr><td bgcolor="#f7f7e7" align="center"><font size="+2">' ); htp.print('<i>Inter</i>Media PL/SQL Web Toolkit Album de Fotos</font></td></tr>' ); htp.print( '</table>' );END imprime_pagina_cabecera;

----------------------------------------------------------------------------- Procedure: imprime_pagina_pie-- Descripción: Genera un pie de página común---------------------------------------------------------------------------PROCEDURE imprime_pagina_pie( muestra_url_devuelta IN BOOLEAN ) ISBEGIN IF muestra_url_devuelta THEN imprime_enlace( 'ALBUM.VER_ALBUM', 'Volver al Album' ); END IF; htp.print( '</body></html>' );END imprime_pagina_pie;

----------------------------------------------------------------------------- Procedure: imprime_encabezado-- Descripción: Genera una línea con formato de titulo---------------------------------------------------------------------------PROCEDURE imprime_encabezado( mensaje IN VARCHAR2 ) ISBEGIN htp.print( '<p><font size=3 color="#336699">' ); htp.print( '<b>' || mensaje || '</b></font><hr size=1></p>' );END imprime_encabezado;

----------------------------------------------------------------------------- Procedure: imprime_enlace-- Descripción: genera un tag anchor (enlace)---------------------------------------------------------------------------PROCEDURE imprime_enlace( enlace IN VARCHAR2, mensaje IN VARCHAR2 ) ISBEGIN htp.print( '<p><table width="100%">' ); htp.print( '<tr bgcolor="#f7f7e7"><td colspan="3" align="center">' ); htp.print( '<a href="' || enlace || '">' || mensaje || '</a>' || '</td></tr></table></p>' );END imprime_enlace;

END Album;/SHOW ERRORS;

PASO 4. Crear un Database Access Descriptor (DAD) para ejecutar la demo. Haz estos pasos:a) Teclea la URL de la configuración del PL/SQL Gateway Configuration en tu navegador. Por ejemplo: http://localhost/pls/admin_/gateway.htm b) Selecciona el enlace "Gateway Database Access Descriptor Settings".c) Selecciona el enlace "Add Default (blank configuration)".d) Teclea la siguiente información dentro de las secciones del formulario "Create DAD Entry":

Add Database Access Descriptor Database Access Descriptor Name: ALBUM_FOTOS_DAD

http://localhost/pls/admin_/gateway.htm


Schema Name: [lo dejamos en blanco] Database Connectivity Information Oracle User Name: [teclea el nombre de usuario de multimedia] Oracle Password: [teclea password del usuario multimedia] Oracle Connect String: [deja en blanco o teclea un TNS name] Authentication Mode Authentication Mode: Basic Session Cookie Session Cookie Name: [en blanco] Session State Create a Stateful Session? No Connection Pool Parameters Enable Connection Pooling? Yes Default(Home)Page Default (Home) Page: ALBUM.VER_ALBUM Document Access Information Document Table: FOTOS_UPLOAD Document Access Path: [en blanco] Document Access Procedure: [en blanco] Extensions uploaded as Long Raw: [en blanco] Path Aliasing Path Alias: [en blanco] Path Alias Procedure: [en blanco]

Nota: si usas el servidor embebido, debes crear el DAD equivalente (consulta la práctica 12) y la URL para conectarte es http://servidor:8080/album_fotos

5). Ejecutar la aplicación. Para usar la aplicación, ejecuta la URL del album de fotos en un navegador web. Por ejemplo:

<HOST>[:<PUERTO>]/pls/album_fotos_dad (sin pls con servidor embebido)

4. Recuperar BLOBs de ORACLE con Programa Cliente en Java.

Como requisito previo deberíamos conocer el lenguaje de programación Java, pero en fin, es solamente un ejemplo. Para la realización de este programa vamos a usar los siguientes packages: java.io , java.sql, oracle.jdbc.driver. Debido a que necesitamos el paquete de oracle.jdbc.driver vamos a necesitar incluir la librería classes12.jar al ClassPath. El programa que vamos a realizar formará parte del package Mipaquete.Oracle.Blob. Está compuesto de las siguientes clases:

• GestorDeConexiones • RecuperadorBLOB • Main

Las sentencias import que vamos a necesitar son las siguientes:package Mipaquete.Oracle.Blob;import java.io.FileOutputStream;import java.io.InputStream;import java.io.IOException;import java.io.File;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.sql.ResultSet;import java.sql.Blob;import oracle.jdbc.driver.OracleConnection;import oracle.jdbc.driver.OracleDriver;

La primera clase que vamos a comentar es la clase GestorDeConexiones, encargada de establecer y gestionar las conexiones con ORACLE. Esta clase realiza las siguientes tareas:

• El constructor GestorDeConexiones, que recibe el nombre de usuario de la base de datos y su clave de acceso.


• conectar, método privado encargado de realizar la conexión. • closeConnection, cierra la conexion • getConnection, devuelve una referencia a la conexión abierta.

El código de la clase GestorDeConexiones se muestra a continuación. La conexion se realiza a través del driver oci por lo que será necesario que tengamos instalado el cliente de Oracle. Si no disponemos del driver de Oracle podemos usar el driver thin. En todo caso la información de la url de conexión dependerá de nuestra configuración

class GestorDeConexiones {private String user;private String password;private Connection conn = null;private boolean conectado = false; public GestorDeConexiones(String usr, String pwd){ user = usr; password = pwd; } public void closeConnection() throws SQLException{ if (conectado) conn.close(); }

private void conectar() throws SQLException { String url; DriverManager.registerDriver(new OracleDriver()); // url = "jdbc:oracle:oci:@<TNS_NAME>"; // url = "jdbc:oracle:thin:@<server>:<port=1521>:<SID>"; url = "jdbc:oracle:oci:@ORACLEBD"; conn = DriverManager.getConnection(url,user, password); System.out.println("Conexion correcta"); conectado = true; }

public Connection getConnection() throws SQLException { if (!conectado) conectar(); return conn; }}

La clase que realmente realiza el trabajo del programa es RecuperadorBLOB, que ejecuta una consulta a la base de datos y vuelca al sistema de archivos cliente el contenido de nuestro campo BLOB. El metodo RecuperarBLOB es estático por lo que no necesitaremos instanciar la clase, tan solo indicar el id del BLOB en la tabla y la ruta donde queramos guardar el resultado. Todo el trabajo se realiza en el método estático RecuperarBLOB, que ejecuta una consulta a la tabla "archivos" y con el resultado de la consulta guarda los datos en el sistema de archivos. La forma de ejecutar la consulta es la clásica de jdbc, es decir, construir la sql, crear un Statement y ejecutarlo para conseguir el ResultSet. Lo único que varia es almacenaremos el contenido del campo en una variable de tipo Blob (java.sql.Blob), y que con ella obtendremos el InputStream a través del método getBinaryStream(). A partir de ahí usaremos un FileOutputStream para guardar el archivo. Cualquier archivo puede representarse como un array de bytes. Por esos usamos la variable byte buffer[]. El código de la clase RecuperadorBLOB se muesta a continuación.

class RecuperadorBLOB { public static void RecuperarBLOB(Connection cn, String idBLOB, String path) throws SQLException, IOException { FileOutputStream fos = null; Statement st = null; ResultSet rs = null; String sql ="select CO_ARCHIVO, NOMBRE_ARCHIVO, BIN, FX_ALTA " + "from archivos " + "WHERE CO_ARCHIVO = '" + idBLOB + "' "; try{ st = cn.createStatement(); rs = st.executeQuery(sql); if (rs.next()) { String pathname= path + "\\" + rs.getString("NOMBRE_ARCHIVO") ;


File file = new File(pathname); fos = new FileOutputStream(file); Blob bin = rs.getBlob("BIN"); InputStream inStream = bin.getBinaryStream(); int size = (int)bin.length(); byte[] buffer = new byte[size]; int length = -1; while ((length = inStream.read(buffer)) != -1){ fos.write(buffer, 0, length); } } } catch (IOException ioe) { throw new IOException(ioe.getMessage()); } finally { if (fos != null)fos.close(); if (rs != null) rs.close(); rs = null; st = null; } }}

Bien, pues con esto únicamente nos falta la clase Main, el punto de entrada del programa. Esta clase realiza las siguiente tareas: Crea una instancia de GestorDeConexiones, y establece una conexion con nuestro servidor ORACLE, llama al método estático RecuperarBLOB de la clase RecuperadorBLOB, por tres veces (los tres archivos que, en mi caso, tengo guardos en la base de datos y cierra la conexion con la base de datos con el GestorDeConexiones.

public class Main { public static void main(String[] args) { System.out.println("Inicializando programa ..."); Connection conn = null; GestorDeConexiones gc = null; try{ gc = new GestorDeConexiones("app1", "password"); conn = gc.getConnection(); String path = "c:\\javaout"; RecuperadorBLOB.RecuperarBLOB(conn,"000001",path); RecuperadorBLOB.RecuperarBLOB(conn,"000002",path); RecuperadorBLOB.RecuperarBLOB(conn,"000003",path);

} catch (SQLException sqle) { System.out.println("Error de acceso a BD:" + sqle.getMessage()); sqle.printStackTrace(); } catch (IOException ioe){ System.out.println("Error de acceso a disco:" + ioe.getMessage()); ioe.printStackTrace(); } try{ if (gc != null && conn != null) gc.closeConnection(); } catch (SQLException sqle) { System.out.println("Error de acceso a BD:" + sqle.getMessage()); sqle.printStackTrace(); conn = null; gc = null; } System.out.println("Finalizando programa ..."); } }

Como habéis podido ver el código es bastante fácil. El código completo es el que sigue a continuación:


/* * Main.java*/package Mipaquete.Oracle.Blob;import java.io.FileOutputStream;import java.io.InputStream;import java.io.IOException;import java.io.File;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.sql.ResultSet;import java.sql.Blob;import oracle.jdbc.driver.OracleConnection;import oracle.jdbc.driver.OracleDriver;

public class Main { public static void main(String[] args) { System.out.println("Inicializando programa ..."); Connection conn = null; GestorDeConexiones gc = null; try{ gc = new GestorDeConexiones("app1", "password"); conn = gc.getConnection(); String path = "c:\\javaout"; RecuperadorBLOB.RecuperarBLOB(conn,"000001",path); RecuperadorBLOB.RecuperarBLOB(conn,"000002",path); RecuperadorBLOB.RecuperarBLOB(conn,"000003",path); } catch (SQLException sqle) { System.out.println("Error de acceso a BD:" + sqle.getMessage()); sqle.printStackTrace(); } catch (IOException ioe){ System.out.println("Error de acceso a disco:" + ioe.getMessage()); ioe.printStackTrace(); } try{

if (gc != null && conn != null) gc.closeConnection(); } catch (SQLException sqle) { System.out.println("Error de acceso a BD:" + sqle.getMessage()); sqle.printStackTrace(); conn = null; gc = null; } System.out.println("Finalizando programa ..."); } }

class RecuperadorBLOB { public static void RecuperarBLOB (Connection cn, String idBLOB, String path) throws SQLException, IOException { FileOutputStream fos = null; Statement st = null; ResultSet rs = null; String sql ="select CO_ARCHIVO, " + " NOMBRE_ARCHIVO, " + " BIN, " + " FX_ALTA " + "from archivos " + "WHERE CO_ARCHIVO = '" + idBLOB + "' "; try { st = cn.createStatement(); rs = st.executeQuery(sql); if (rs.next())


{ String pathname= path + "\\" + rs.getString("NOMBRE_ARCHIVO") ; File file = new File(pathname); fos = new FileOutputStream(file); Blob bin = rs.getBlob("BIN"); InputStream inStream = bin.getBinaryStream(); int size = (int)bin.length(); byte[] buffer = new byte[size]; int length = -1; while ((length = inStream.read(buffer)) != -1) { fos.write(buffer, 0, length); } } } catch (IOException ioe) { throw new IOException(ioe.getMessage()); } finally { if (fos != null) fos.close(); if (rs != null) rs.close(); rs = null; st = null; } }}

class GestorDeConexiones { private String user; private String password; private Connection conn = null; private boolean conectado = false; public GestorDeConexiones(String usr, String pwd){ user = usr; password = pwd; } public void closeConnection() throws SQLException{ if (conectado) conn.close(); }

private void conectar() throws SQLException { String url; DriverManager.registerDriver(new OracleDriver()); // url = "jdbc:oracle:oci:@<TNS_NAME>"; // url = "jdbc:oracle:thin:@<server>:<port=1521>:<SID>"; url = "jdbc:oracle:oci:@ORACLEBD"; conn = DriverManager.getConnection(url,user, password); System.out.println("Conexion correcta"); conectado = true; } public Connection getConnection() throws SQLException { if (!conectado) conectar(); return conn; } }

5. PL/SQL y Java

Otra de la virtudes de PL/SQL es que permite trabajar conjuntamente con Java. PL/SQL es un excelente lenguaje para la gestión de información pero en ocasiones, podemos necesitar de un lenguaje de programación más potente. Por ejemplo, podríamos necesitar consumir un servicio Web, conectar a otro servidor, trabajar con Sockets, etc. Para


estos casos, podemos unir la potencia de Java con PL/SQL. Para poder trabajar con Java y PL/SQL debemos realizar los siguientes pasos:

• Crear el programa Java y cargarlo en la base de datos. • Crear un programa de recubrimiento1 (Wrapper) con PL/SQL.

Creación de Objetos Java en la base de datos ORACLE.ORACLE incorpora su propia versión de máquina virtual Java y de JRE 2. Esta versión de Java se instala conjuntamente con ORACLE. Para crear objetos Java en la base de datos podemos utilizar la utilidad LoadJava de ORACLE desde la línea de comandos o bien crear objetos JAVA SOURCE en la propia base de datos. La sintaxis para la creación de JAVA SOURCE en ORACLE es la siguiente.

CREATE [OR REPLACE] AND COMPILE JAVA SOURCE NAMED <nombre> ASpublic class <className>{

<instrucciones java>...

};El siguiente ejemplo crea y compila una clase Java OracleJavaClass en el interior de JAVA SOURCE FuentesJava. Un aspecto muy a tener en cuenta es que los métodos de la clase java que queramos invocar desde PL/SQL deben ser estáticos.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED FuentesJava ASpublic class OracleJavaClass { public static String Saluda(String nombre) { return ("Hola desde Java " + nombre); }};

Un mismo JAVA SOURCE puede contener varias clases de Java.CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED FuentesJava ASpublic class OracleJavaClass { public static String Saluda(String nombre){ return ("Hola desde Java" + nombre); }}public class OracleJavaMejorada { public static String SaludoMejorado(String nombre) { return ("Saludo mejorado desde Java para: " + nombre); } };

La otra opción sería guardar nuestro código java en el archivo OracleJavaClass.java, compilarlo y cargarlo en ORACLE con LoadJava. A continuación se muestran ejemplos del uso de la utilidad LoadJava, loadJava -help

loadJava -u usario/password -v -f -r OracleJavaClass.classloadJava -u usario/password -v -f -r OracleJavaClass.java

Ejecución de programas Java con PL/SQL.Una vez que tenemos listo el programa de Java debemos integrarlo con PL/SQL. Esto se realiza a través de subprogramas de recubrimiento llamados Wrappers. No podemos crear un Wrapper en un bloque anónimo. La sintaxis general es la siguiente:

CREATE [OR REPLACE] FUNCTION|PROCEDURE <name> [(<params>,...)][RETURN <tipo>]IS|ASLANGUAGE JAVA NAME '<clase>.<metodo> [return <tipo>]' ;

El siguiente ejemplo muestra el Wrapper para nuestra función Saludo.

1 Un wrapper es algo que envuelve a otra cosa. En este caso, un programa PL/SQL envolverá a otro java. Por fuera creemos que llamamos a PL/SQL, aunque internamente es java lo que se ejecuta.

2 JRE: Java Run-time Environment.


CREATE OR REPLACE FUNCTION Saluda_wrap (nombre VARCHAR2) RETURN VARCHAR2AS LANGUAGE JAVA NAME 'OracleJavaClass.Saluda(java.lang.String) return java.lang.String';

Una vez creado el wrapper, podremos ejecutarlo como cualquier otra función o procedure de PL/SQL. Debemos crear un wrapper por cada función java que queramos ejecutar desde PL/SQL. Cuando ejecutemos el wrapper, es decir, la función "Saluda_wrap", internamente se ejecutará la clase java y se invocará el método estático "OracleJavaClass.Saluda". Un aspecto a tener en cuenta es que es necesario proporcionar el nombre del tipo java completo, es decir, debemos especificar java.lang.String en lugar de únicamente String.

SELECT SALUDA_WRAP('DEVJOKER') FROM DUAL;

La ejecución de este ejemplo en SQL*Plus genera la siguiente salida:SQL> SELECT SALUDA_WRAP('DEVJOKER') FROM DUAL;SALUDA_WRAP('DEVJOKER')----------------------------------------------Hola desde Java DEVJOKER

Una recomendación de diseño sería agrupar todos los Wrapper en un mismo paquete. En el caso de que nuestro programa Java necesitase de packages Java adicionales, deberíamos cargarlos en la base de datos con la utilidad LoadJava.


A ENTREGAR

1º. En el esquema empresa, modifica la tabla empleados para que tenga una columna que le permita almacenar una fotografía (Anota el SQL que necesites para realizar esta modificación).

2º. Modifica la aplicación web de la práctica 12, para que al listar los empleados aparezca una pequeña fotografía (si la tiene de tamaño 30x60). Le edición de un cliente debe permitir subir la foto.

3º. En la Inserción/modificación de los datos de un empleado Añade la posibilidad de que los clientes puedan subir un archivo de su sistema como foto de un empleado.

EJERCICIO VOLUNTARIO:

4º. Usando el siguiente código (adaptándolo) para mostrar y descargar contenidos en la web, crea un nuevo esquema llamado personajes, que tenga una tabla de carga y descarga y que permita añadir datos de varios personajes (nombre, edad, mote, una fotografía y un chiste contado por ellos). Debes hacer una aplicación web que mantenga seguridad a nivel de aplicación (una tabla de usuarios/contraseñas) donde las contraseñas deben estar protegidas. Usando PL/SQL debe permitir:

• Al usuario personajes, realizar el mantenimiento de la tabla, que incluye insertar, modificar y borrar personajes además de subir y bajar contenido (fotos y sonido).

• Al resto de usuarios ver y descargar el contenido.

Oracle tiene un mecanismo para hacer upload y download ficheros directamente desde la BD usando DAD. Primero debes crear el DAD (el ejemplo supone que estás usando Apache, sino es así, debes adaptarlo):

• Accede a http://servidor:puerto/. El puerto lo consultas en el fichero $ORACLE_HOME/Apache/Apache/setupinfo.txt.

• Clic en "Mod_plsql Configuration Menu".• Clic en "Gateway Database Access Descriptor Settings". • Clic en "Add Default (blank configuration)". • Teclea PERSONAJES como nombre del DAD. Esto se usará en la URL de los clientes. • Teclea username (PUBLIC), password (PUBLIC) y connect string si es necesario. • Selecciona "Basic" como authentication mode. • Teclea "documentos" para Document Table. • Teclea "docs" como Document Access Path. • Teclea "document_api.download" para Document Access Procedure. • Clic el botón OK.

Para subir bien, la tabla documentos debe tener la siguiente estructura:

CREATE TABLE documentos ( name VARCHAR2(256) UNIQUE NOT NULL, mime_type VARCHAR2(128), doc_size NUMBER, dad_charset VARCHAR2(128), last_updated DATE, content_type VARCHAR2(128), blob_content BLOB)/

Luego, creamos un package specification en el esquema:

CREATE OR REPLACE PACKAGE document_api ASPROCEDURE upload;PROCEDURE upload (file IN VARCHAR2);


PROCEDURE download;PROCEDURE download (file IN VARCHAR2);END;/SHOW ERRORS

Luego un package body:

CREATE OR REPLACE PACKAGE BODY document_api AS

PROCEDURE upload AS l_real_name VARCHAR2(1000);BEGIN HTP.htmlopen; HTP.headopen; HTP.title('Comprobar Upload'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Comprobar Upload'); HTP.print('<form enctype="multipart/form-data" ' || 'action="document_api.upload" method="post">'); HTP.print(' Fichero a Subir: <input type="file" name="file"><br />'); HTP.print(' <input type="submit" value="Upload">'); HTP.print('</form>'); HTP.bodyclose; HTP.htmlclose;END upload;

PROCEDURE upload (file IN VARCHAR2) AS l_real_name VARCHAR2(1000);BEGIN HTP.htmlopen; HTP.headopen; HTP.title('File Uploaded'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Estado de la subida'); l_real_name := SUBSTR(file, INSTR(file, '/') + 1); BEGIN -- Borrar cualquier documento para permitir la modificación. DELETE FROM documentos WHERE name = l_real_name; -- Modificar el nombre prefijado con el nombre del fichero real. UPDATE documentos SET name = l_real_name WHERE name = file; HTP.print( l_real_name || ' Subido satisfactoriamente.'); EXCEPTION WHEN OTHERS THEN HTP.print('Fallo al subir ' || l_real_name); HTP.print(SQLERRM); END; HTP.br; -- Crea algunos enlaces para demostrar la descarga con una URL HTP.br; HTP.print('Descargas con URL:'); HTP.br; FOR cur_rec IN (SELECT name FROM documentos) LOOP HTP.anchor('docs/' || cur_rec.name, 'docs/' || cur_rec.name); HTP.br; END LOOP; -- Crea enlaces para mostrar descargas directas HTP.br; HTP.print('Descargas Directas:'); HTP.br; FOR cur_rec IN (SELECT name FROM documentos) LOOP


HTP.anchor('document_api.download?file=' || cur_rec.name, 'document_api.download?file=' || cur_rec.name); HTP.br; END LOOP; HTP.bodyclose; HTP.htmlclose;END upload;

PROCEDURE download IS l_filename VARCHAR2(255);BEGIN l_filename := SUBSTR(OWA_UTIL.get_cgi_env('PATH_INFO'), 2); WPG_DOCLOAD.download_file(l_filename);EXCEPTION WHEN OTHERS THEN HTP.htmlopen; HTP.headopen; HTP.title('Descargando Fichero'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Estado de descarga'); HTP.print('Fallo en descarga de ' || l_filename ); HTP.print(SQLERRM); HTP.bodyclose; HTP.htmlclose;END download;

PROCEDURE download (file IN VARCHAR2) AS l_blob_content documents.blob_content%TYPE; l_mime_type documents.mime_type%TYPE;BEGIN SELECT blob_content, mime_type INTO l_blob_content, l_mime_type FROM documentos WHERE name = file; OWA_UTIL.mime_header(l_mime_type, FALSE); HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content)); OWA_UTIL.http_header_close; WPG_DOCLOAD.download_file(l_blob_content);EXCEPTION WHEN OTHERS THEN HTP.htmlopen; HTP.headopen; HTP.title('Descarga de Ficheros'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Estado de la descarga'); HTP.print(SQLERRM); HTP.bodyclose; HTP.htmlclose;END download;

END;/SHOW ERRORS

Puedes subir usando la url http://servidor:puerto/pls/personajes/document_api.upload o ajustando el valor action de un formulario como en el siguiente ejemplo:

<html><head> <title>Comprobar subida</title></head><body> <form enctype="multipart/form-data"


action="http://servidor:7777/pls/DAD/document_api.upload" method="post"> Fichero a subir: <input type="file" name="file"><br> <input type="submit" value="Upload"> </form></body></html>

La segunda sobrecarga del procedure upload realmente no realiza la carga porque esto lo hace automáticamente el DAD. Simplemente te permite realizar procesamiento adicional si fuese necesario. Oracle prefija el nombre dl fichero con un nombre de carpeta prefijado, para reducir la posibilidad de conflictos de nombres. En este ejemplo los ficheros se renombran a sus nombres originales.

Además el código en el procedure upload ofrece dos tipos de links. El primero, usa el "document access path" y el otro usa el "document access procedure" definidos en el DAD. Esto permite acceder a los ficheros usando una URL normal, ocultando el procedimiento que devuelve el fichero. Usando document access path en la URL se provoca que el DAD llame al procedure document access. El nombre del fichero solicitado debe recuperarse de la URL y usar el procedure WPG_DOCLOAD.download_file. El segundo tipo de enlace llama a un procedure alternativo de download directamente. En este caso, el nombre del fichero debe indicarse como parámetro. Aquí tienes un procedure que permite visualizar una imagen y descargarla.

GRANT execute ON muestra_imagen TO public;

CREATE OR REPLACE PROCEDURE muestra_imagen( p_id NUMBER ) AS s_mime_type VARCHAR2(48); n_length NUMBER; s_filename VARCHAR2(400); lob_image BLOB;BEGIN SELECT mime_type, dbms_lob.getlength( blob_content ), filename, blob_content INTO s_mime_type, n_length, s_filename, lob_image FROM documentos WHERE image_id = p_id; -- Fija el tamaño para que el browser sepa cuanto debe descargar. owa_util.mime_header(NVL( s_mime_type, 'application/octet' ), FALSE ); htp.p( 'Content-length: ' || n_length ); -- El filename será usado por el browser si el usuario hace "Guardar como" htp.p( 'Content-Disposition: filename="' || s_filename || '"' ); owa_util.http_header_close; -- Descargar el BLOB wpg_docload.download_file( lob_image );END display_easy_image;

2asi sgbd u08 plsql practica14 lobs

Documents