Tablas Externas

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.

Para utilizar las tablas externas debe tener un poco de conocimiento del formato de archivo y registro de los ficheros de datos de la plataforma  ORACLE_LOADER  y los archivos de datos en un formato de texto. También debe saber lo suficiente acerca de SQL para poder crear una tabla externa y realizar consultas en contra de ella.

Cuando se crean tablas externas podemos encontrarlos en las siguientes tablas:

dba_external_tables

all_external_tables

user_external_tables

Creación de 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_externo AS  '/tmp/EXDATA';

Dejamos un archivo en la ruta bajo el nombre de text.txt el cual tiene lo siguiente:

10,Roy Damasco,"16/12/2004"

20,Gladys Daviña,"25/02/2005"

30,Leo Sigil,"04/03/2004"

Se crea la tabla Externa bajo el siguiente script:

CREATE TABLE XX_EMPLOYEES_ALL(

  Codigo NUMBER(4),

  Empleado CHAR(50),

  Fecha_inicio  CHAR(10))

ORGANIZATION EXTERNAL(

  TYPE ORACLE_LOADER DEFAULT DIRECTORY dir_externo

  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

    (Codigo INTEGER EXTERNAL (4),

     Empleado CHAR(50),

     Fecha_inicio CHAR(10) DATE_FORMAT DATE MASK "dd/mm/yyyy"))

   LOCATION ('test.txt')

REJECT LIMIT 0;  

Al consultar la tabla, encontraremos los datos ya ingresados. Cada vez que el archivo se actualiza la tabla externa se llena con los nuevos datos.

Elementos:

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.

DEFAULT DIRECTORY- especifica la ubicación predeterminada de los archivos que se leen o se escriben por tablas externas. Se especifica la ubicación de un  directorio.
ACCESS PARAMETERS - describen el origen de datos externos e implementa el tipo de tabla externa que se ha especificado. 
LOCATION - Especifica la ubicación de los datos externos.