Tablas externas en PL/SQL

Las tablas externas permiten hacer consultas desde una base de datos Oracle sobre datos almacenados en un fichero de texto como si dicho fichero fuera una tabla de la base de datos. En Oracle 9i, sólo se pueden realizar operaciones de lectura con las tablas externas; en cambio, en Oracle 10g, se puede también escribir datos en una tabla externa que será creada en ese momento, es decir, no se puede utilizar una tabla externa que ya existe para realizar esta operación.

Aunque se pueden hacer consultas sobre las tablas externas, éstas no permiten todas las funcionalidades que permite Oracle sobre tablas normales. Por ejemplo, no es posible realizar algunas operaciones DDL (sentencias de definición de objetos como revoke, grant, etc.) sobre tablas externas aparte de la creación y actualización de la definición de la misma; por lo tanto no es posible crear índices sobre una tabla externa.

Oracle utiliza el SQL*Loader a través del driver ORACLE_LOADER para cargar datos desde un fichero de texto en la base de datos; y, por otro lado, el driver Data Pump (Bombeo de Datos) permite pasar datos desde la base de datos a un fichero de texto utilizando un formato propietario de Oracle, y, obviamente, dicho fichero de texto se puede cargar de nuevo en la misma u otra base de datos. Existen diferentes restricciones y maneras de proceder, pero se puede pensar que las tablas externas son otra opción alternativa al SQL*Loader y al Data Pump.

Por ejemplo, suponiendo que recibimos un informe .csv diariamente. En vez de escribir un script en SQL*Loader para importar los datos todos los días, se puede simplemente crear una tabla externa y escribir una sentencia SQL "insert ... select" para insertar los datos directamente en las tablas de la base de datos. Así que, diariamente podríamos colocar el fichero CSV en el directorio correspondiente, ejecutar la sentencia insert y tendríamos los datos cargados en nuestra base de datos.

Creación de una tabla externa

Puesto que los datos de las tablas externas están en ficheros de texto, estos ficheros deben estar en un lugar al que Oracle tenga acceso. Así pues, el primer paso es crear un directorio y dar acceso de lectura y escritura al usuario del sistema operativo que se encarga de ejecutar los procesos de la base de datos Oracle. Dicho directorio no puede ser un link simbólico, debe ser un directorio real.

$ cd $ORACLE_HOME
$ mkdir texternal
$ mkdir data
$ ls -l $ORACLE_HOME/texternal
total 30
drwxr-x--- 2 oracle dba 5120 Jul 17 2006 data

Después pondremos el fichero de texto en dicho directorio. En el presente ejemplo utilizaremos un fichero CSV (proyectos.csv):

3002508,ESP Pinto,Calidad,RZAPA001
3002509,ESP Pinto,Supervisión,CGAMI001
3002510,ESP Humanes,Calidad,RZAPA001
3002511,ESP Humanes,Supervisión,GDIAZ001
3002512,ESP Humanes,Instalación,HPERE001

El siguiente paso es crear el directorio en Oracle y dar permiso de lectura/escritura sobre dicho directorio al usuario de Oracle que creará la tabla externa. A la hora de crear el directorio hay que estar seguro de que se usa el path completo y que no se utiliza ningún link simbólico. En nuestro ejemplo supondremos que la variable $ORACLE_HOME tiene el valor /u01/app/oracle/ y por lo tanto el nombre del directorio con el path completo sería /u01/app/oracle/texternal/data.

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> create or replace directory texternal_data
2 as '/u01/app/oracle/texternal/data';

Directory created.

SQL> grant read,write on directory texternal_data to userexte;

Grant succeeded.

El último paso es crear la tabla externa. El comando es exactamente el mismo que se utiliza para crear tablas normales, CREATE TABLE, pero incluye un bloque con sentencias específicas que informan a Oracle de como debe interpretar los datos almacenados en el fichero de texto.

SQL> connect userexte
Enter password:
Connected.

SQL> create table text_proyectos
2 (
3 orden number,
4 proyecto varchar2(30),
5 actividad varchar2(15),
6 supervisor varchar(8)
7 )
8 organization external
9 (
10 default directory texternal_data
11 access parameters
12 (
13 records delimited by newline
14 fields terminated by ','
15 )
16 location ('proyectos.csv')
17 );

Table created.

El comando CREATE TABLE no realiza ninguna validación de los datos que hay cargados en el fichero de texto, de hecho el comando va a funcionar incluso si el fichero de texto con los datos no está en el directorio. Con el comando CREATE TABLE sólo hemos creado los metadatos de la tabla externa en el diccionario de datos y le hemos indicado a Oracle como utilizar el driver ORACLE_LOADER para analizar los datos del fichero de texto.

Una vez que colocamos el fichero de texto en el directorio correcto, ya podemos acceder a los datos mediante la ejecución de un comando select:

SQL> select * from text_proyectos;

Orden Proyecto Actividad Supervisor
------- ----------- ----------- ----------
3002508 ESP Pinto Calidad RZAPA001
3002509 ESP Pinto Supervisión CGAMI001
3002510 ESP Humanes Calidad RZAPA001
3002511 ESP Humanes Supervisión GDIAZ001
3002512 ESP Humanes Instalación HPERE001

5 rows selected.

Oracle utiliza el driver ORACLE_LOADER para procesar el fichero y, de igual forma que la utilidad SQL*Loader, crea un fichero de log en el que se almacena lo que ha ocurrido al procesar el fichero. El fichero de log se habrá creado en el directorio por defecto especificado en la sentencia CREATE TABLE que utilizamos para definir la tabla externa, el nombre del fichero vendrá determinado por el nombre de la tabla seguido por el ID del proceso del sistema operativo que procesó los datos de la tabla externa:

$ ls -l
total 45
-rw-r--r-- 1 oracle dba 1031 Jul 17 2006 TEXT_PROYECTOS_12345.log
-rw------- 1 oracle dba 298 Jul 17 2006 proyectos.csv

Si Oracle detecta algún problema a la hora de procesar el fichero de texto, Oracle generará un error que será mostrado en la línea de comando así como en el fichero de log, además se crearán también un par de ficheros más, el badfile y/o el discardfile. Uno de los errores más típicos es dejar una línea en blanco al final del fichero de texto, Oracle intentará procesar esta última línea y, al no tener ningún dato, se producirá un error.

Se pueden configurar directorios separados para los distintos ficheros (LOG, BAD y DISCARD) así como para el fichero de texto con los datos. En mi opinión, es una buena idea utilizar un directorio para los datos y otro para los ficheros de log. Así pues, igual que tuvimos que crear un directorio para guardar los ficheros de texto con los datos, ahora tenemos que crear un nuevo directorio para almacenar los "logs", tanto en el sistema operativo (por ejemplo, /u01/app/oracle/texternal/log), como en Oracle (en este caso lo llamaremos texternal_log). Ahora utilizando el comando ALTER TABLE podemos cambiar la definición de la tabla externa:

SQL> alter table text_proyectos
1 access parameters
2 (
3 records delimited by newline
4 badfile texternal_log:'text_proyectos.bad'
5 logfile texternal_log:'text_proyectos.log'
6 discardfile texternal_log:'text_proyectos.dsc'
7 fields terminated by ','
8 );

También existe la opción de definir la tabla externa de forma que no se genere ningún fichero de log, bad o discard. En este caso la sentencia select va a fallar cuando se exceda el número máximo de registros rechazados por existir algún tipo de problema con los mismos, es exactamente lo mismo que ocurre con la utilidad SQL*Loader. Este límite se puede cambiar también con el comando ALTER TABLE:

SQL> alter table text_proyectos set reject_limit 250;

Carga de datos en una tabla de la base de datos

Ahora viene la parte interesante de este artículo y es donde voy a mostrar como podemos cargar los datos almacenados en una tabla externa en una tabla real de la base de datos. Además, es importante reseñar que podemos utilizar funciones PL/SQL para transformar los datos de la tabla externa antes de que sean cargados en las tablas de la base de datos.

Por ejemplo, suponiendo que en nuestra base de datos existe una tabla llamada "proyectos" con las columnas proyecto, actividad, nombre_supervisor y de que disponemos de una función PL/SQL denominada get_nombre_supervisor() que permite obtener el nombre de un supervisor en base al campo supervisor de nuestra tabla externa, entonces podemos construir la siguiente sentencia SQL para cargar los datos de nuestra tabla externa en la tabla proyectos de la base de datos:

SQL> insert into proyectos
1 (
2 select proyecto
3 , actividad
4 , get_nombre_supervisor(supervisor) nombre_supervisor
5 from text_proyectos
6 );

5 rows inserted.

Descarga de datos a una tabla externa

Oracle 10g permite crear una tabla externa utilizando datos existentes en la base de datos, estos datos se descargan a un fichero de texto utilizando el driver ORACLE_DATAPUMP. Este fichero de texto es almacenado con un formato propietario de Oracle que puede ser leído por el driver Data Pump. En este caso, es importante indicar en el comando CREATE TABLE, el tipo de driver a utilizar, ORACLE_DATAPUMP, ya que el driver por defecto es ORACLE_LOADER. La sintaxis del comando sería:

SQL> create table text_export_proy
2 organization external
3 (
4 type oracle_datapump
5 default directory texternal_data
6 location ('export_proy.dmp')
7 ) as select * from proyectos;

Evidentemente ahora podemos coger el fichero que acabamos de crear, export_proy.dmp, llevarlo a otro sistema y crear una tabla externa para leer los datos.

SQL> connect userexte@otradb
Enter password:
Connected.

SQL> create table text_proyectos
2 (
4 proyecto varchar2(30),
5 actividad varchar2(15),
6 nombre_supervisor varchar(30)
7 )
8 organization external
9 (
10 type oracle_datapump
10 default directory texternal_data
16 location ('export_proy.dmp')
17 );

Table created.

Conclusión

En este artículo hemos visto como podemos cargar y descargar datos en y hacia una base de datos utilizando tablas externas. Las tablas externas en 9i y 10g permiten mover datos integrando las funcionalidades de Oracle, SQL*Loader y Data Pump, con la mejora añadida de que se pueden utilizar sentencias SQL en el proceso.