Anuncios en tutorial de programación PLSQL

martes, 5 de septiembre de 2006

Tablas externas en PL/SQL

Las tablas externas en PLSQL 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.

32 comentarios:

Arcangelion dijo...

Hola Pepelu, al igual que tú, estoy realizando un blog, pero mi tema es todo lo referente a SQL y Network Solutions, dale una ojeada y dime si estas deacuerdo en un intercambio de links.

http://www.networking-solution.blogspot.com
Att
Arcangelion

Arcangelion dijo...

Hola Pepelu, no hay ningún problema, confirmame los datos:
texto:"Programación PL/SQL"
link: http://plsql-erp.blogspot.com/

Por mi parte sería:
texto: "SQL y Redes desde CERO"
link: http://www.networking-solution.blogspot.com

Saludos
Arcangelion

Janus dijo...

Hola tengo un problema que quiza tu me puedas ayudar, necesito cargar a una tabla externa, pero los datos no estan separados por comas sino que son de ancho fijo, como seria la sintaxis en ese caso para la parte "fields terminated by"?

Gracias

Pepelu dijo...

Hola Janus,

Si sólo necesitas cargar datos desde una tabla externa en realidad no necesitas utilizar esta funcionalidad que también te permite descargar datos a una tabla externa.

Si los campos de tu tabla externa vienen delimitados por un ancho fijo puedes usar SQL*Loader para cargarlos en una tabla Oracle.

No he escrito ningún artículo sobre como usar SQL*Loader pero en este enlace lo explican bastante bien:

http://www.cs.us.es/cursos/bd-2001/practicas/practica5.html

Saludos,
Pepelu.

Manuel dijo...

Buenas!!

Quisiera plantearos una duda, ¿¿una tabla externa se puede bloquear para que mientras que estamos recorriendo un cursor sobre esta tabla en un proceso, no se pueda eliminar o sobreescribir el fichero fisico hasta que no finalize el proceso??

Pepelu dijo...

Hola Manuel,

Yo creo que las tablas externas sólo se pueden proteger utilizando las funcionalidades que te da el sistema operativo para proteger ficheros. De todas formas no estoy seguro al 100 por cien.

Alberto Passarelli dijo...

Una consulta,

se puede usar una external table en el caso de que el archivo txt tiene dos "diseños de registro" diferentes?

Saludos, Alberto.

Manuel dijo...

Entiendo que sí, siempre y cuando la estructura de la tabla te acepte "fisicamente" los registros,

un ejemplo simple es que todos los campos de la tabla sean de tipo number y que los dos tipos de registros del fichero tengan valores de tipo number en todos los campos.

No se si me explico.

Pepelu dijo...

Hola Alberto,

Para utilizar tablas externas se deben definir los campos de la misma, digamos que el "diseño de los registros" viene impuesto por la definición de la tabla que se ha hecho anteriormente.

Si la tabla externa tiene registros como los siguientes:

abc, 123
abcde, 12345

Si al definir la tabla has indicado que el primer campo es un VARCHAR2(3) entonces la carga de la tabla externa fallará ya que el primer campo del segundo registro es de longitud 5. Para que la carga no fallase el primer campo debería definirse al menos como VARCHAR2(5).

Creo que esto explica tu duda y creo que también es lo que intentaba explicar Manuel.

Borja dijo...

Muy bueno el artículo!
Gracias.

carlos_gall dijo...

Buen día, es posible crear tablas externas de archivos con ancho de registro fijo, sin separador de campo?.

Saludos
CarlosG.

Pepelu dijo...

Hola Carlos,

Primero perdona por la demora en responder. Ciertamente se puede hacer lo que pides. Simplemente cambiaría esta parte del comando CREATE TABLE:

(
records delimited by newline
fields terminated by ','
)

Que quedaría de la siguiente manera:

(
records delimited by newline
(
orden position(1:20) NUMBER,
proyecto position(*:+30) CHAR,
actividad position(*:+15) CHAR,
supervisor position(*:+8) CHAR
)
)

Angeles Alanis dijo...

Hola Pepelu,

Tengo un problema sobre tabla externa, al procesar mi archivo de texto el cual está delimitado por pipe (|) en algunos registros el ultimo campo esta vacío y la tabla externa los manda al archivo de descartados, ¿como puedo hacer para que estos sean procesados y no los rechace?. son como 2 millones de registros y no cargan todos y por tal motivo mis cifras no son las que debieran ser.

Gracias

KrLs dijo...

Hola a Todos,

Tengo un problemilla con una tabla externa.

El tema es que el fichero que tengo que utilizar tiene como separador decimal una ',' y no hay manera de que pueda ver los registros en la external table.

Porfavor, me podrian poner un ejemplo?

Saludos,
krls

Pepelu dijo...

Hola Angeles, creo que el problema que tienes es que no utilizas la cláusula MISSING FIELD VALUES ARE NULL.

CREATE TABLE tabla_ext ( ... )
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY dir
ACCESS PARAMETERS (
records delimited by newline
fields terminated by '|'
missing field values are null
)
LOCATION ('nombrefichero')
)
...;

Angeles Alanis dijo...

Muchas gracias Pepelu, voy a probar con esa instruccion y te informo. =)

Pepelu dijo...

Hola KrLs,

Perdona por el retraso en contestar pero tenía que consultar que parámetro de la sesión Oracle tenías que cambiar.

Para definir el separador decimal como una coma y el separador de miles como un punto debes utilizar el siguiente comando SQL:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

OMM dijo...

Hola a Todos.
Necesito saber si al crear una tabla externa puedo usar la clausula when o where (necesito leer un archivo plano y solo tomaar informacion de uno de los tipos de registros que vienen en el mismo)

Gracias

Pepelu dijo...

OMM, puedes utilizar todas las cláusulas permitidas en el SELECT.

BRANTIX dijo...

Hola papelu

Tengo un archivo plano que está delimitado solamente por 'newline', es decir no tiene separadores.
Lo que tiene de especial es que tiene 4 tipos de registros asi:

Registro de encabezado de archivo: 56 caracteres.
Registro de encabezado de lote: 20 caracteres.

registros de detalle: 98 caracteres.

Registro de control de lote: 34 caracteres.

Registro de control de archivo: 27 caracteres

se vería algo asi:

registro de encabezado de archivo
registro de encabezado de lote
registro de detalle
registro de detalle
registro de detalle
registro de detalle
registro de control de lote
registro de control de archivo

La pregunta es si hay alguna forma de trabajar este archivo desde una tabla externa realizando una sola definición, ya que son de longitud y tienen información difente.

Muchas Gracias!

Manuel Arco dijo...

Brantix, se me ocurre que si los registros siempre van a tener la misma longitud, ya tienes una manera de diferenciar los registros y por tanto de poder cargar esos registros en tablas externas diferentes, en la clausula where de la Select que alimenta la tabla externa podrías filtrar por longitud.


Espero que te haya entendido bien y te valga

Pepelu dijo...

Brantix, también te recomiendo que eches una ojeada a la funcionalidad de las bases de datos Oracle SQL*Loader.

BRANTIX dijo...

Muchas Gracias por la respuesta Manuel Arco y Papelu.

Tienen algun ejemplo para cargar una tabla externa desde un archivo plano utilizando WHERE para seleccionar alguna linea en específico como en mi caso?

Muchas Gracias de antemano.

Pepelu dijo...

Brantix, Carlos_gall hizo una pregunta similar a la tuya y le puse un ejemplo en un comentario de esta entrada.

Pero si hay registros de encabezado y control lo lógico sería utilizarlos y cargar los datos en una tabla de la base de datos Oracle. Yo utilizaría la librería estándar de PL/SQL UTL_FILE para ller el fichero de datos y poblar la tabla con los registros de detalle.

Te dejo un ejemplo.

Primero hay que crear el directorio donde vas a poner el fichero con los datos:

CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

Y luego el script PL/SQL para leer el fichero podría ser como sigue (obviamente tu necesitarás procesar cada línea del fichero para sacar los distintos datos que incluya cada una de ellas):

DECLARE
  ffile UTL_FILE.FILE_TYPE;
  linea VARCHAR2(100);
BEGIN
  ffile := UTL_FILE.FOPEN('TMP', 'misdatos', 'R');
  LOOP
    UTL_FILE.GET_LINE(ffile, linea);
    dbms_output.put_line(linea);
  END LOOP;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Fichero leído');
END;
/

BRANTIX dijo...

Muchas Gracias Papelu!

Las tablas externas las estoy trabajando como en el ejemplo que le diste a Carlos_gall. La diferencia es que luego de la linea 'records delimited by newline' y antes del parentesis hay que agregar la palabra 'FIELDS', asi:
...
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS
(
tipo_registro position(1:20) CHAR(2),
referencia_usuario position(*:+48) CHAR(48),
valor_recaudo position(*:+14) CHAR(14)
)
.....

Lo intentaré con todas las sugerencias y les contaré cuál de todas fue la mejor solución :D

Manuel Arco dijo...

¿No puedes crear una tabla con solo un campo, que sea todo el registro?

Luego con un cursor podrás tratar de manera diferente cada registro mediante la longitud del registro.

BRANTIX dijo...

Hola a todos! Tiempo sin postear :D

Les cuento que la lectura del archivo que les mencionaba anteriormente la realizamos con una librería de Forms llamada TEXTIO. Como toda la aplicación está hecha en forms se nos facilitó hacerlo de esta manera.

Cabe aclarar que esta librería es propia de Forms y no del motor de base de datos como tal.

Espero les sirva.

Saludos!

LOGIC dijo...

hola a todos
tengo un problema.

Tengo un archivo csv delimitado por ; que dentro de uno de los campos tengo un texto que contiene salto de linea por más que este entre comillas dobles me considera que es una nueva fila.
alguien sabe como se trata esto?

BRANTIX dijo...

Hola Logic.
Pueder publicar la definición de la tabla por favor?

Angeles Alanis dijo...

Hola a todos, tengo una serie de archivos de texto delimitados por pipe "|" y que si no los convierto a unix al leerlos con tablas externas el importe me lo trunca y no me da las cifras decimales, sabrán a que se debe, los archivos son generados bajo windows y mi servidor de oracle esta en unix. gracias!!! =)

Diego Espindola dijo...

Hola, que es mas rapido UTL_FILE o tabla externa? En el caso de tabla externa se puede procesar archivos en donde el nombre fuera fijo y este concatenado con un nombre variable ej. archivo_aaaammdd. Gracias!

José Luis Pérez dijo...

El rendimiento es similar pero el manejo de tablas externas quizás sea más intuitivo. En cuanto a la segunda pregunta no sé que decirte, programando se puede hacer casi todo pero en ese caso yo utilizaría el paquete UTL_FILE.