Tablas externas en Oracle

Las tablas externas es una funcionalidad, que proporciona Oracle, para facilitar la lectura de ficheros formateados, como si fueran tablas. Este tipo de tablas son de sólo lectura y no permite utilizar manipulación de datos (DML). No admite la creación de índices sobre este tipo de tablas.

Las tablas externas se utilizan en cargas de ficheros repetitivos y sin tener que acceder al sistema operativo, para realizar la carga.

¿Cómo se crean las tablas externas?

La sintaxis de creación es como la de una tabla estandar de Oracle «CREATE TABLE«, solo que se añade la clausula «ORGANIZATION EXTERNAL» y otros parámetros, que se definen con «ACCESS PARAMETERS«, para definir donde están los datos y como se acceden a ellos.

Para poder acceder al fichero desde Oracle lo primero que definimos, es un objeto directorio de la siguiente manera:

CREATE OR REPLACE DIRECTORY dir_tablas_externas AS  '/tmp/tablas_externas'; 

Para el ejemplo, necesitamos un fichero en «/tmp/tablas_externas» como el siguiente con el nombre «edades1.txt»:

1,Pepe,"10/01/2000"
2,Luis,"05/02/1999"
3,Tomás,"01/01/1950"

Y otro con el nombre «edades2.txt»:

1,Jesus,"10/01/2000"
2,Carmen,"05/02/1999"
3,Teo,"01/01/1950"

Y ya por último creamos a tabla externa:

CREATE TABLE TE_Edades( 
  clave NUMBER(4),
  nombre CHAR(20),
  nacimiento CHAR(10))
ORGANIZATION EXTERNAL( 
  TYPE ORACLE_LOADER DEFAULT DIRECTORY dir_tablas_externas
	ACCESS PARAMETERS
	(RECORDS DELIMITED BY NEWLINE 
	 SKIP 0    
	 BADFILE '%a_%p.bad' 
	 LOGFILE '%a_%p.log' 
	 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LRTRIM  
	 MISSING FIELD VALUES ARE NULL  
	 REJECT ROWS WITH ALL NULL FIELDS 
    (clave INTEGER EXTERNAL (4),
     nombre CHAR(20),
     nacimiento CHAR(10) DATE_FORMAT DATE MASK "dd/mm/yyyy")) 
   LOCATION ('edades1.txt','edades2.txt'))
REJECT LIMIT 0;  

Ya podemos leer de la tabla TE_Edades:

SELECT * FROM TE_Edades;

Explicación de las sintaxis:

SKIP: indica el número de filas a saltarse, para evitar por ejemplo cabeceras. No se puede utilizar con la clausula de paralelización

LRTRIM: Elimina los espacios en blanco

READSIZE: Indica el buffer de lectura. Se indica en número de bytes.

LOGFILE: Contiene los mensajes generados durante el acceso a los datos.

BADFILE: Registros erroneos. Pueden ser por ejemplo por haber caracteres en un campo númerico.

SEQUENCE: las secuencias de registros desechados por las clausulas WHEN no consumen números de la secuencia.

Se Puede cambiar el fichero con la siguiente sentencia:

ALTER TABLE TE_Edades LOCATION ('edades3.txt'); 

Las tablas de catálogo con finformación sobre tablas externas son:
dba_external_tables, all_external_tables, user_external_tables