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.
35 comentarios:
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
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
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
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.
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??
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.
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.
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.
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.
Muy bueno el artículo!
Gracias.
Buen día, es posible crear tablas externas de archivos con ancho de registro fijo, sin separador de campo?.
Saludos
CarlosG.
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
)
)
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
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
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')
)
...;
Muchas gracias Pepelu, voy a probar con esa instruccion y te informo. =)
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 = ',.';
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
OMM, puedes utilizar todas las cláusulas permitidas en el SELECT.
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!
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
Brantix, también te recomiendo que eches una ojeada a la funcionalidad de las bases de datos Oracle SQL*Loader.
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.
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;
/
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
¿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.
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!
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?
Hola Logic.
Pueder publicar la definición de la tabla por favor?
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!!! =)
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!
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.
A nadie le salto el error:
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEOPEN
ORA-29400: error de cartucho de datos
estoy probando de todo y no puedo solucionarlo
Hola.
¿A parte de ese error te devuelve otro error KUP -xxxx?
Estos errores los suele devolver cuando no se encuentra el fichero del que se leen los datos o bien no se tienen permisos sobre el a nivel de SO, en el archivo o en los directorios que lo albergan.
Saludos
Un favor. Quisiera poder mediante una sentencia conocer el nombre del archivo que esta usando una tabla externa. Podrian ayudarme?. Lo que busco es cuando realice una carga de información almacenar el nombre del archivo y tener bajo control la informacion que ha se ha subido. Gracias
Publicar un comentario